
SET search_path To fw_core;

CREATE DOMAIN fw_flag_bool AS BOOL;
	
CREATE DOMAIN fw_flag_YN AS BPCHAR;
	
CREATE DOMAIN fw_date AS DATE;

CREATE DOMAIN fw_num_dimid AS INTEGER;
	
CREATE DOMAIN fw_num_bigid AS BIGINT;

CREATE DOMAIN fw_num_metric AS 	DECIMAL(12,4);
	
CREATE DOMAIN fw_num_array AS  INTEGER ARRAY;
   
CREATE DOMAIN fw_alphnum AS  TEXT;

CREATE DOMAIN fw_alphanum AS  TEXT;
   
CREATE DOMAIN fw_text_array AS  TEXT ARRAY;   
   
CREATE DOMAIN fw_timestamp AS  TIMESTAMP;  

CREATE DOMAIN fw_time AS  TIME;  

CREATE DOMAIN fw_json AS    JSON;



-- Drop table

DROP TABLE if Exists fw_core.fw_jobctl_jobstep_master;

CREATE TABLE fw_core.fw_jobctl_jobstep_master (
	syspk bigserial NOT NULL,
	client_id fw_core.fw_num_dimid NOT NULL,
	function_id fw_core.fw_num_dimid NOT NULL,
	run_frequency fw_core.fw_alphanum NOT NULL,
	job_scheduling_day fw_core.fw_alphanum NOT NULL,
	sch_day_of_month fw_core.fw_alphanum NOT NULL,
	sch_day_of_week fw_core.fw_alphanum NOT NULL,
	sch_hour_of_day fw_core.fw_time NOT NULL,
	sch_minute_of_hour fw_core.fw_time NOT NULL,
	job_id fw_core.fw_num_dimid NOT NULL,
	step_id fw_core.fw_num_dimid NOT NULL,
	job_step_run_dependency_seuqence fw_core.fw_num_dimid NOT NULL,
	active_flag fw_core.fw_flag_bool NOT NULL DEFAULT true,
	job_name fw_core.fw_alphanum NULL,
	step_name fw_core.fw_alphanum NULL,
	job_script_type fw_core.fw_alphanum NOT NULL,
	job_step_script_name fw_core.fw_alphanum NOT NULL,
	job_fun_param_array fw_core.fw_text_array NULL,
	job_scope fw_core.fw_alphanum NULL,
	job_scope_qualifier_array fw_core.fw_text_array NULL,
	sme_entering fw_core.fw_alphanum NULL,
	extract_field_name fw_core.fw_alphanum NULL,
	job_step_description fw_core.fw_alphanum NULL,
	job_step_objective fw_core.fw_alphanum NULL,
	created_by fw_core.fw_alphanum NOT NULL,
	updated_by fw_core.fw_alphanum NOT NULL,
	create_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	update_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	CONSTRAINT fw_jobctl_jobstep_master_pkey PRIMARY KEY (syspk),
	CONSTRAINT unique_jobctl_jobstep_master UNIQUE (client_id, function_id, run_frequency, job_id, step_id)
);

-- Drop table

DROP TABLE if Exists fw_core.fw_jobctl_jobstep_master;

CREATE TABLE fw_core.fw_jobctl_jobstep_master (
	syspk bigserial NOT NULL,
	client_id fw_core.fw_num_dimid NOT NULL,
	function_id fw_core.fw_num_dimid NOT NULL,
	run_frequency fw_core.fw_alphanum NOT NULL,
	job_scheduling_day fw_core.fw_alphanum NOT NULL,
	sch_day_of_month fw_core.fw_alphanum NOT NULL,
	sch_day_of_week fw_core.fw_alphanum NOT NULL,
	sch_hour_of_day fw_core.fw_time NOT NULL,
	sch_minute_of_hour fw_core.fw_time NOT NULL,
	job_id fw_core.fw_num_dimid NOT NULL,
	step_id fw_core.fw_num_dimid NOT NULL,
	job_step_run_dependency_seuqence fw_core.fw_num_dimid NOT NULL,
	active_flag fw_core.fw_flag_bool NOT NULL DEFAULT true,
	job_name fw_core.fw_alphanum NULL,
	step_name fw_core.fw_alphanum NULL,
	job_script_type fw_core.fw_alphanum NOT NULL,
	job_step_script_name fw_core.fw_alphanum NOT NULL,
	job_fun_param_array fw_core.fw_text_array NULL,
	job_scope fw_core.fw_alphanum NULL,
	job_scope_qualifier_array fw_core.fw_text_array NULL,
	sme_entering fw_core.fw_alphanum NULL,
	extract_field_name fw_core.fw_alphanum NULL,
	job_step_description fw_core.fw_alphanum NULL,
	job_step_objective fw_core.fw_alphanum NULL,
	created_by fw_core.fw_alphanum NOT NULL,
	updated_by fw_core.fw_alphanum NOT NULL,
	create_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	update_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	CONSTRAINT fw_jobctl_jobstep_master_pkey PRIMARY KEY (syspk),
	CONSTRAINT unique_jobctl_jobstep_master UNIQUE (client_id, function_id, run_frequency, job_id, step_id)
);

drop table if exists fw_core.fw_jobctl_runschedule;

CREATE table fw_core.fw_jobctl_runschedule (
syspk bigserial NOT NULL,
client_id fw_num_dimid NOT NULL,
function_id fw_num_dimid NOT NULL,
latest_runschedule_flag fw_flag_bool NOT NULL DEFAULT '1',
run_schedule_id bigserial NOT NULL,
run_frequency fw_alphnum NOT NULL,
run_schedule_timestamp fw_timestamp NOT NULL ,
begin_status fw_alphnum NULL,
end_status fw_alphnum NULL DEFAULT NULL,
end_status_note fw_alphnum null DEFAULT NULL,
object_count fw_num_dimid NULL,
start_time fw_timestamp NULL,
end_time fw_timestamp NULL,
created_by fw_alphnum NOT NULL,
updated_by fw_alphnum NOT NULL,
create_timestamp fw_timestamp NOT NULL DEFAULT now(),
update_timestamp fw_timestamp NOT NULL DEFAULT now(),
CONSTRAINT fw_jobctl_runschedule_pkey PRIMARY KEY (syspk),
CONSTRAINT unique_jobctl_runschedule UNIQUE (client_id, function_id, run_schedule_id, run_frequency));

drop table if Exists fw_core.fw_jobctl_runschedule_jobstep ;

CREATE table fw_core.fw_jobctl_runschedule_jobstep (
syspk bigserial NOT NULL,
client_id fw_num_dimid NOT NULL,
function_id fw_num_dimid NOT NULL,
latest_runschedule_flag fw_flag_bool NOT NULL DEFAULT '1',
run_schedule_id fw_num_dimid NULL,
run_frequency fw_alphnum NOT NULL,
run_schedule_timestamp fw_timestamp NOT NULL,
job_id fw_num_dimid NOT NULL,
step_id fw_num_dimid NOT NULL,
job_step_run_dependency_seuqence fw_num_dimid NOT NULL,
job_script_type fw_alphnum NOT NULL,
job_step_script_name fw_alphnum NOT NULL,
job_fun_param_array fw_text_array NULL,
job_scope fw_alphnum NULL,
job_scope_qualifier_array fw_text_array NULL,
job_name fw_alphnum NULL,
step_name fw_alphnum NULL,
begin_status fw_alphnum null default NULL,
end_status fw_alphnum null default NULL,
end_status_note fw_alphnum null default NULL,
start_time fw_timestamp NULL,
end_time fw_timestamp NULL,
created_by fw_alphnum NULL,
updated_by fw_alphnum NULL,
create_timestamp fw_timestamp NOT NULL DEFAULT now(),
update_timestamp fw_timestamp NOT NULL DEFAULT now(),
CONSTRAINT fw_jobctl_runschedule_jobstep_pkey PRIMARY KEY (syspk),
CONSTRAINT unique_jobctl_runschedule_jobstep UNIQUE (client_id, function_id, run_schedule_id, run_frequency, job_id, step_id));

drop table if exists fw_core.fw_jobctl_file_runschedule;

CREATE TABLE fw_core.fw_jobctl_file_runschedule (
syspk bigserial NOT NULL,
client_id fw_num_dimid NOT NULL,
function_id fw_num_dimid NOT NULL,
latest_runschedule_flag fw_flag_bool NOT NULL DEFAULT '1',
run_schedule_id fw_num_dimid NULL,
job_id fw_num_dimid NULL,
step_id fw_num_dimid NULL,
file_syspk fw_num_dimid NOT NULL,
file_name fw_alphnum NOT NULL,
file_mnemonic fw_alphnum NOT NULL,
file_landing_date date NOT NULL,
staging_type fw_alphnum NULL,
begin_status fw_alphnum null default NULL,
end_status fw_alphnum null  default NULL,
end_status_note fw_alphnum null  default NULL,
start_time fw_timestamp NULL,
end_time fw_timestamp NULL,
created_by fw_alphnum NULL,
updated_by fw_alphnum NULL,
create_timestamp fw_timestamp NOT NULL DEFAULT now(),
update_timestamp fw_timestamp NOT NULL DEFAULT now(),
CONSTRAINT fw_jobctl_file_runschedule_pkey PRIMARY KEY (syspk),
CONSTRAINT unique_fw_jobctl_file_runschedule UNIQUE (client_id, function_id, run_schedule_id,file_syspk));

-- Drop table

DROP TABLE if exists fw_core.fw_jobctl_file_sheet_runschedule;

CREATE TABLE fw_core.fw_jobctl_file_sheet_runschedule (
	syspk bigserial NOT NULL,
	client_id fw_core.fw_num_dimid NOT NULL,
	function_id fw_core.fw_num_dimid NOT NULL,
	latest_runschedule_flag fw_core.fw_flag_bool NOT NULL DEFAULT true,
	run_schedule_id fw_core.fw_num_dimid NULL,
	job_id fw_core.fw_num_dimid NULL,
	step_id fw_core.fw_num_dimid NULL,
	file_syspk fw_core.fw_num_dimid NOT NULL,
	sheet_id fw_core.fw_num_dimid NOT NULL,
	sheet_name fw_core.fw_alphanum NOT NULL,
	file_mnemonic fw_core.fw_alphanum NOT NULL,
	sheet_format_mnemonic fw_core.fw_alphanum NULL,
	file_sheet_mnemonic fw_core.fw_alphanum NULL,
	staging_type fw_alphnum NULL,
	begin_status fw_core.fw_alphanum NULL DEFAULT NULL,
	end_status fw_core.fw_alphanum NULL DEFAULT NULL,
	end_status_note fw_core.fw_alphanum NULL DEFAULT NULL,
	start_time fw_core.fw_timestamp NULL,
	end_time fw_core.fw_timestamp NULL,
	created_by fw_core.fw_alphanum NULL,
	updated_by fw_core.fw_alphanum NULL,
	create_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	update_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	CONSTRAINT fw_jobctrl_file_sheet_runschedule_pkey PRIMARY KEY (syspk),
	CONSTRAINT unique_fw_jobctrl_file_sheet_runschedule UNIQUE (client_id, function_id, file_syspk, sheet_id)
);
-- Drop table

DROP TABLE if exists fw_core.fw_jobctl_file_sheet_block_run_schedule;

CREATE TABLE fw_core.fw_jobctl_file_sheet_block_run_schedule (
	syspk bigserial NOT NULL,
	client_id fw_core.fw_num_dimid NOT NULL,
	function_id fw_core.fw_num_dimid NOT NULL,
	latest_runschedule_flag fw_core.fw_flag_bool NOT NULL DEFAULT true,
	run_schedule_id fw_core.fw_num_dimid NULL,
	file_syspk fw_core.fw_num_dimid NULL,
	sheet_id fw_core.fw_num_dimid NULL,
	block_table_load_seq fw_core.fw_num_dimid NULL,
	block_table_name text NULL,
	file_mnemonic fw_core.fw_alphanum NOT NULL,
	sheet_format_mnemonic fw_core.fw_alphanum NULL,
	file_sheet_mnemonic fw_core.fw_alphanum NOT NULL,
	begin_status fw_core.fw_alphanum NULL DEFAULT NULL,
	end_status fw_core.fw_alphanum NULL DEFAULT NULL,
	end_status_note fw_core.fw_alphanum NULL DEFAULT NULL,
	start_time fw_core.fw_timestamp NULL,
	end_time fw_core.fw_timestamp NULL,
	created_by fw_core.fw_alphanum NULL,
	updated_by fw_core.fw_alphanum NULL,
	create_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	update_timestamp fw_core.fw_timestamp NOT NULL DEFAULT now(),
	CONSTRAINT fw_jobctl_file_sheet_block_run_schedule_pkey PRIMARY KEY (syspk),
	CONSTRAINT unique_fw_jobctrl_file_sheet_block_run_schedule UNIQUE (client_id, function_id, file_syspk, sheet_id, block_table_load_seq)
);
-- Drop table

 DROP TABLE if exists staging1.staging_generic_table;

CREATE TABLE staging1.staging_generic_table (
	syspk int8 NULL,
	file_syspk int4 NULL,
	file_name varchar(1024) NULL,
	file_prefix_date date NULL,
	file_mnemonic varchar(1024) NULL,
	sheet_id int4 NULL,
	sheet_name varchar(1024) NULL,
	sheet_format_mnemonic varchar(1024) NULL,
	file_sheet_mnemonic varchar(1024) NULL,
	"row_number" int4 NULL,
	file_creation_date timestamp NULL,
	column1 varchar(1024) NULL,
	column2 varchar(1024) NULL,
	column3 varchar(1024) NULL,
	column4 varchar(1024) NULL,
	column5 varchar(1024) NULL,
	column6 varchar(1024) NULL,
	column7 varchar(1024) NULL,
	column8 varchar(1024) NULL,
	column9 varchar(1024) NULL,
	column10 varchar(1024) NULL,
	column11 varchar(1024) NULL,
	column12 varchar(1024) NULL,
	column13 varchar(1024) NULL,
	column14 varchar(1024) NULL,
	column15 varchar(1024) NULL,
	column16 varchar(1024) NULL,
	column17 varchar(1024) NULL,
	column18 varchar(1024) NULL,
	column19 varchar(1024) NULL,
	column20 varchar(1024) NULL,
	column21 varchar(1024) NULL,
	column22 varchar(1024) NULL,
	column23 varchar(1024) NULL,
	column24 varchar(1024) NULL,
	column25 varchar(1024) NULL,
	column26 varchar(1024) NULL,
	column27 varchar(1024) NULL,
	column28 varchar(1024) NULL,
	column29 varchar(1024) NULL,
	column30 varchar(1024) NULL,
	column31 varchar(1024) NULL,
	column32 varchar(1024) NULL,
	column33 varchar(1024) NULL,
	column34 varchar(1024) NULL,
	column35 varchar(1024) NULL,
	column36 varchar(1024) NULL,
	column37 varchar(1024) NULL,
	column38 varchar(1024) NULL,
	column39 varchar(1024) NULL,
	column40 varchar(1024) NULL,
	column41 varchar(1024) NULL,
	column42 varchar(1024) NULL,
	column43 varchar(1024) NULL,
	column44 varchar(1024) NULL,
	column45 varchar(1024) NULL,
	column46 varchar(1024) NULL,
	column47 varchar(1024) NULL,
	column48 varchar(1024) NULL,
	column49 varchar(1024) NULL,
	column50 varchar(1024) NULL,
	column51 varchar(1024) NULL,
	column52 varchar(1024) NULL,
	column53 varchar(1024) NULL
);

CREATE INDEX i_file_syspk ON staging1.staging_generic_table USING btree (file_syspk);
CREATE INDEX i_sheet_name ON staging1.staging_generic_table USING btree (sheet_name);
CREATE INDEX i_file_mnemonic ON staging1.staging_generic_table USING btree (file_mnemonic);
CREATE INDEX i_file_sheet_mnemonic ON staging1.staging_generic_table USING btree (file_sheet_mnemonic);
CREATE INDEX i_sheet_format_mnemonic ON staging1.staging_generic_table USING btree (sheet_format_mnemonic);

