CREATE TABLE tb_insurance (
	id varchar(32) NOT NULL,
	vehicle_id varchar(32) NULL,
	project_id varchar(32) NULL,
	has_compulsory_insurance varchar(2) NULL DEFAULT 0,
	compulsory_insurance_no varchar(32) NULL,
	compulsory_create_date timestamp NULL,
	compulsory_company_id varchar(32) NULL,
	compulsory_price float4 NOT NULL DEFAULT 0,
	vehicle_vessel_tax float4 NOT NULL DEFAULT 0,
	compulsory_start_time timestamp NULL,
	compulsory_valid_time timestamp NULL,
	compulsory_url text NULL,
	has_commercial_insurance varchar(2) NULL DEFAULT 0,
	commercial_insurance_no varchar(32) NULL,
	commercial_create_date timestamp NULL,
	commercial_company_id varchar(32) NULL,
	commercial_price float4 NOT NULL DEFAULT 0,
	commercial_start_time timestamp NULL,
	commercial_valid_time timestamp NULL,
	commercial_url text NULL,
	sys_org_code varchar(16) NULL,
	create_time timestamp NULL,
	update_time timestamp NULL,
	create_by varchar(32) NULL,
	update_by varchar(32) NULL,
	del_flag int2 NULL DEFAULT 0,
	CONSTRAINT tb_insurance_pk PRIMARY KEY (id)
);
COMMENT ON TABLE tb_insurance IS '保险信息';

-- Column comments

COMMENT ON COLUMN tb_insurance.vehicle_id IS '所属车辆';
COMMENT ON COLUMN tb_insurance.project_id IS '所属项目';
COMMENT ON COLUMN tb_insurance.has_compulsory_insurance IS '是否购买交强险（0,1）';
COMMENT ON COLUMN tb_insurance.compulsory_insurance_no IS '交强险保险单号';
COMMENT ON COLUMN tb_insurance.compulsory_create_date IS '交强险办理日期';
COMMENT ON COLUMN tb_insurance.compulsory_company_id IS '交强险保险公司';
COMMENT ON COLUMN tb_insurance.compulsory_price IS '交强险保险金额';
COMMENT ON COLUMN tb_insurance.vehicle_vessel_tax IS '车船税';
COMMENT ON COLUMN tb_insurance.compulsory_start_time IS '交强险起保日期';
COMMENT ON COLUMN tb_insurance.compulsory_valid_time IS '交强险有效日期';
COMMENT ON COLUMN tb_insurance.compulsory_url IS '交强险附件';
COMMENT ON COLUMN tb_insurance.has_commercial_insurance IS '是否购买商业保险（0,1）';
COMMENT ON COLUMN tb_insurance.commercial_insurance_no IS '商业保险单号';
COMMENT ON COLUMN tb_insurance.commercial_create_date IS '商业保险办理日期';
COMMENT ON COLUMN tb_insurance.commercial_company_id IS '商业保险公司';
COMMENT ON COLUMN tb_insurance.commercial_price IS '商业保险金额';
COMMENT ON COLUMN tb_insurance.commercial_start_time IS '商业保险起保日期';
COMMENT ON COLUMN tb_insurance.commercial_valid_time IS '商业保险有效日期';
COMMENT ON COLUMN tb_insurance.commercial_url IS '商业保险附件';
COMMENT ON COLUMN tb_insurance.sys_org_code IS '所属机构';
COMMENT ON COLUMN tb_insurance.create_time IS '创建时间';
COMMENT ON COLUMN tb_insurance.update_time IS '更新时间';
COMMENT ON COLUMN tb_insurance.create_by IS '创建人';
COMMENT ON COLUMN tb_insurance.update_by IS '更新人';
COMMENT ON COLUMN tb_insurance.del_flag IS '删除标志';

ALTER TABLE tb_insurance ADD vehicle_model varchar(32) NOT NULL;
COMMENT ON COLUMN tb_insurance.vehicle_model IS '车辆型号';
ALTER TABLE tb_insurance ADD vehicle_type varchar(2) NOT NULL;
COMMENT ON COLUMN tb_insurance.vehicle_type IS '车辆种类';

ALTER TABLE tb_insurance ALTER COLUMN vehicle_vessel_tax DROP NOT NULL;
ALTER TABLE tb_insurance ALTER COLUMN compulsory_price DROP NOT NULL;
ALTER TABLE tb_insurance ALTER COLUMN commercial_price DROP NOT NULL;
ALTER TABLE tb_insurance ALTER COLUMN vehicle_model DROP NOT NULL;
ALTER TABLE tb_insurance ALTER COLUMN vehicle_type DROP NOT NULL;
ALTER TABLE tb_insurance ADD remark text NULL;
COMMENT ON COLUMN tb_insurance.remark IS '备注';

CREATE TABLE tb_insurance_record (
	id varchar(32) NOT NULL,
	vehicle_id varchar(32) NULL,
	project_id varchar(32) NULL,
	vehicle_model varchar(32) NULL,
	vehicle_type varchar(2) NULL,
	insurance_date timestamp NULL,
	company_id varchar(32) NULL,
	insurance_no varchar(32) NULL,
	insurance_price float4 NULL DEFAULT 0,
	handle_user varchar(32) NULL,
	handle_status varchar(2) NULL,
	remark text NULL,
	accident_url text NULL,
	compensate_agreement_url text NULL,
	loss_assessment_url text NULL,
	compensate_url text NULL,
	other_url text NULL,
	sys_org_code varchar(16) NULL,
	create_time timestamp NULL,
	update_time timestamp NULL,
	create_by varchar(32) NULL,
	update_by varchar(32) NULL,
	del_flag int2 NULL DEFAULT 0,
	CONSTRAINT tb_insurance_record_pk PRIMARY KEY (id)
);
COMMENT ON TABLE tb_insurance_record IS '出险管理';

-- Column comments

COMMENT ON COLUMN tb_insurance_record.vehicle_id IS '所属车辆';
COMMENT ON COLUMN tb_insurance_record.project_id IS '所属项目';
COMMENT ON COLUMN tb_insurance_record.vehicle_model IS '车辆型号';
COMMENT ON COLUMN tb_insurance_record.vehicle_type IS '车辆种类';
COMMENT ON COLUMN tb_insurance_record.insurance_date IS '出险日期';
COMMENT ON COLUMN tb_insurance_record.company_id IS '保险公司';
COMMENT ON COLUMN tb_insurance_record.insurance_no IS '理赔案号';
COMMENT ON COLUMN tb_insurance_record.insurance_price IS '理赔金额';
COMMENT ON COLUMN tb_insurance_record.handle_user IS '处理人';
COMMENT ON COLUMN tb_insurance_record.handle_status IS '处理结果';
COMMENT ON COLUMN tb_insurance_record.remark IS '备注';
COMMENT ON COLUMN tb_insurance_record.accident_url IS '事故认定书';
COMMENT ON COLUMN tb_insurance_record.compensate_agreement_url IS '赔偿协议书';
COMMENT ON COLUMN tb_insurance_record.loss_assessment_url IS '定损单';
COMMENT ON COLUMN tb_insurance_record.compensate_url IS '赔偿单据';
COMMENT ON COLUMN tb_insurance_record.other_url IS '其他';
COMMENT ON COLUMN tb_insurance_record.sys_org_code IS '所属机构';
COMMENT ON COLUMN tb_insurance_record.create_time IS '创建时间';
COMMENT ON COLUMN tb_insurance_record.update_time IS '更新时间';
COMMENT ON COLUMN tb_insurance_record.create_by IS '创建人';
COMMENT ON COLUMN tb_insurance_record.update_by IS '更新人';
COMMENT ON COLUMN tb_insurance_record.del_flag IS '删除标志';

ALTER TABLE tb_vehicle ADD price float4 NULL DEFAULT 0;
COMMENT ON COLUMN tb_vehicle.price IS '车辆价格';
