Untitled

-- create `insurances.bill_payment_renew_resource` table

-- DROP TABLE if exists insurances.bill_payment_renew_resource;
CREATE TABLE insurances.bill_payment_renew_resource (
	file_id serial4 NOT NULL,
	file_name varchar(255) NOT NULL,
	content_type varchar(50) NOT NULL,
	record_amount int4 NULL,
	record_invalid_amount int4 NULL,
	file_type varchar(20) NULL,
	created_by varchar(100) NULL,
	created_date timestamp DEFAULT now() NULL,
	status varchar(20) DEFAULT 'FAIL'::character varying NOT NULL,
	updated_by varchar(100) NULL,
	updated_date timestamp NULL,
	CONSTRAINT bill_payment_renew_resource_pkey PRIMARY KEY (file_id)
);
CREATE INDEX bill_payment_renew_resource_id ON insurances.bill_payment_renew_resource USING btree (file_id);


-- ====================================================================================


-- create `insurances.bill_payment_renew` table

-- DROP TABLE IF EXISTS insurances.bill_payment_renew;
CREATE TABLE insurances.bill_payment_renew (
	rec_indicator bpchar(1) DEFAULT 'D'::bpchar NOT NULL,
	transaction_status bpchar(1) NOT NULL,
	billpay_type varchar(10) NOT NULL,
	data_source varchar(10) NOT NULL,
	as_of_date date NOT NULL,
	customer_name varchar(70) NOT NULL,
	account_type varchar(10) NULL,
	vmi_policy_number varchar(50) NOT NULL,
	cmi_policy_number varchar(50) NULL,
	insurance_class varchar(10) NOT NULL,
	raw_insurance_class varchar(50) NOT NULL,
	coverage_start_date date NOT NULL,
	coverage_end_date date NOT NULL,
	car_brand varchar(50) NULL,
	car_family varchar(50) NULL,
	car_year varchar(50) NULL,
	car_plate varchar(50) NULL,
	car_type_code varchar(50) NULL,
	vin_number varchar(50) NULL,
	engine_number varchar(50) NULL,
	car_registered_year varchar(4) NULL,
	car_cc numeric(30, 2) NULL,
	car_type varchar(100) NULL,
	sum_insured numeric(30, 2) NULL,
	garage_type varchar(10) NOT NULL,
	vmi_premium_exc_vat_duty numeric(30, 2) NOT NULL,
	vat numeric(30, 2) NOT NULL,
	duty numeric(30, 2) NOT NULL,
	deductible numeric(30, 2) NULL,
	own_damage numeric(30, 2) NULL,
	driver_cover_amount numeric(30, 2) NULL,
	passenger_no numeric(2) NULL,
	passenger_cover_amount numeric(30, 2) NULL,
	medical_expenses_person numeric(30, 2) NULL,
	case_assure_expenses_time numeric(30, 2) NULL,
	tpbi_person numeric(30, 2) NULL,
	tpbi_time numeric(30, 2) NULL,
	tppd_time numeric(30, 2) NULL,
	comp_code bpchar(4) NOT NULL,
	ref1 varchar(50) NOT NULL,
	ref2 varchar(50) NOT NULL,
	payment_amount numeric(20, 2) NOT NULL,
	last_payment_date date NOT NULL,
	created_date timestamp DEFAULT now() NULL,
	updated_date timestamp NULL,
	billpay_ibs_transaction_status bpchar(1) NOT NULL,
	CONSTRAINT bill_payment_renew_pkey PRIMARY KEY (data_source, vmi_policy_number)
);


-- ====================================================================================


-- create `insurances.bill_payment_renew_drivers` table

-- DROP TABLE IF EXISTS insurances.bill_payment_renew_drivers;
CREATE TABLE insurances.bill_payment_renew_drivers (
    data_source VARCHAR(10) NOT NULL,
    vmi_policy_number VARCHAR(50) NOT NULL,
    no INT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    birthday DATE,
    license_number VARCHAR(10),
    CONSTRAINT bill_payment_renew_drivers_pkey PRIMARY KEY (data_source, vmi_policy_number, no)
);
ALTER TABLE insurances.bill_payment_renew_drivers ADD CONSTRAINT fk_bill_payment_renew_drivers_on_bill_payment_renew FOREIGN KEY (data_source, vmi_policy_number) REFERENCES insurances.bill_payment_renew(data_source, vmi_policy_number) ON DELETE CASCADE;


-- ====================================================================================


-- create `insurances.stg_bill_payment_renew` table

-- DROP TABLE insurances.stg_bill_payment_renew;
CREATE TABLE insurances.stg_bill_payment_renew (
	id uuid NOT NULL,
	rec_indicator bpchar(255) NULL,
	transaction_status bpchar(255) NULL,
	billpay_type varchar(255) NULL,
	data_source varchar(255) NULL,
	as_of_date varchar(50) NULL,
	customer_name varchar(255) NULL,
	account_type varchar(255) NULL,
	vmi_policy_number varchar(255) NULL,
	cmi_policy_number varchar(255) NULL,
	insurance_class varchar(255) NULL,
	raw_insurance_class varchar(255) NULL,
	coverage_start_date varchar(50) NULL,
	coverage_end_date varchar(50) NULL,
	car_brand varchar(255) NULL,
	car_family varchar(255) NULL,
	car_year varchar(255) NULL,
	car_plate varchar(255) NULL,
	car_type_code varchar(255) NULL,
	vin_number varchar(255) NULL,
	engine_number varchar(255) NULL,
	car_registered_year varchar(255) NULL,
	car_cc varchar(255) NULL,
	car_type varchar(255) NULL,
	sum_insured varchar(50) NULL,
	garage_type varchar(255) NULL,
	vmi_premium_exc_vat_duty varchar(50) NULL,
	vat varchar(50) NULL,
	duty varchar(50) NULL,
	deductible varchar(50) NULL,
	own_damage varchar(50) NULL,
	driver_cover_amount varchar(50) NULL,
	passenger_no varchar(50) NULL,
	passenger_cover_amount varchar(50) NULL,
	medical_expenses_person varchar(50) NULL,
	case_assure_expenses_time varchar(50) NULL,
	tpbi_person varchar(50) NULL,
	tpbi_time varchar(50) NULL,
	tppd_time varchar(50) NULL,
	driver_first_name1 VARCHAR(255) NULL,
    driver_last_name1 VARCHAR(255) NULL,
    birthday1 VARCHAR(50) NULL,
    driver_license_number1 VARCHAR(255) NULL,
    driver_first_name2 VARCHAR(255) NULL,
    driver_last_name2 VARCHAR(255) NULL,
    birthday2 VARCHAR(50) NULL,
    driver_license_number2 VARCHAR(255) null,
	comp_code VARCHAR(255) NULL,
	ref1 varchar(255) NULL,
	ref2 varchar(255) NULL,
	payment_amount varchar(50) NULL,
	last_payment_date varchar(50) NULL,
	created_date timestamp DEFAULT now() NULL,
	updated_date timestamp NULL,
	billpay_ibs_transaction_status VARCHAR(255) NULL,
	file_id int4 NOT NULL,
	remark text NULL,
	CONSTRAINT stg_bill_payment_renew_pkey PRIMARY KEY (id)
);
CREATE INDEX stg_bill_payment_renew_file_id_idx ON insurances.stg_bill_payment_renew USING btree (file_id);