ALTER TABLE public.tb_contract ADD vehicle_count int2 NULL DEFAULT 0;
COMMENT ON COLUMN public.tb_contract.vehicle_count IS '车辆数';
ALTER TABLE public.tb_contract ADD signing_name varchar(64) NULL;
COMMENT ON COLUMN public.tb_contract.signing_name IS '签约方名称（客户档案（客户租赁合同）、维修厂档案（维修合同）、车辆供应商档案（车辆采购合同）、保险档案（保险合同），其填文本）';
ALTER TABLE public.tb_contract ADD status varchar(2) NULL;
COMMENT ON COLUMN public.tb_contract.status IS '合同状态（0未交接，1已交接）';


ALTER TABLE public.tb_contract_vehicle ADD CONSTRAINT tb_contract_vehicle_pk PRIMARY KEY (id);


COMMENT ON COLUMN public.tb_contract_vehicle.sys_org_code IS '所属机构';
ALTER TABLE public.tb_contract_vehicle ADD create_time timestamp NULL;
COMMENT ON COLUMN public.tb_contract_vehicle.create_time IS '创建时间';
ALTER TABLE public.tb_contract_vehicle ADD update_time timestamp NULL;
COMMENT ON COLUMN public.tb_contract_vehicle.update_time IS '更新时间';
ALTER TABLE public.tb_contract_vehicle ADD create_by varchar(32) NULL;
COMMENT ON COLUMN public.tb_contract_vehicle.create_by IS '创建人';
ALTER TABLE public.tb_contract_vehicle ADD update_by varchar(32) NULL;
COMMENT ON COLUMN public.tb_contract_vehicle.update_by IS '更新人';

ALTER TABLE public.tb_vehicle_check_detail ADD vehicle_brand varchar(32) NULL;
COMMENT ON COLUMN public.tb_vehicle_check_detail.vehicle_brand IS '车辆品牌';
ALTER TABLE public.tb_vehicle_check_detail ADD vehicle_model varchar(32) NULL;
COMMENT ON COLUMN public.tb_vehicle_check_detail.vehicle_model IS '车辆型号';
ALTER TABLE public.tb_vehicle_check_detail ADD vehicle_type varchar(32) NULL;
COMMENT ON COLUMN public.tb_vehicle_check_detail.vehicle_type IS '车辆种类';
ALTER TABLE public.tb_vehicle_check_detail ADD plate_number varchar(10) NULL;
COMMENT ON COLUMN public.tb_vehicle_check_detail.plate_number IS '车牌号';

ALTER TABLE public.tb_insurance ALTER COLUMN compulsory_price TYPE numeric(11, 2) USING compulsory_price::numeric;
ALTER TABLE public.tb_insurance ALTER COLUMN commercial_price TYPE numeric(11, 2) USING commercial_price::numeric;
ALTER TABLE public.tb_insurance ALTER COLUMN vehicle_vessel_tax TYPE numeric(11, 2) USING vehicle_vessel_tax::numeric;

ALTER TABLE public.tb_insurance_record ALTER COLUMN insurance_price TYPE numeric(11, 2) USING insurance_price::numeric;

ALTER TABLE public.tb_contract ALTER COLUMN total_price TYPE numeric(11, 2) USING total_price::numeric;
ALTER TABLE public.tb_contract ALTER COLUMN total_price DROP NOT NULL;
ALTER TABLE public.tb_contract ALTER COLUMN total_price_no_tax TYPE numeric(11, 2) USING total_price_no_tax::numeric;
ALTER TABLE public.tb_contract ALTER COLUMN total_price_no_tax DROP NOT NULL;
ALTER TABLE public.tb_contract ALTER COLUMN added_tax_rate TYPE numeric(11, 2) USING added_tax_rate::numeric;
ALTER TABLE public.tb_contract ALTER COLUMN added_tax_rate DROP NOT NULL;
ALTER TABLE public.tb_contract ALTER COLUMN added_tax TYPE numeric(11, 2) USING added_tax::numeric;
ALTER TABLE public.tb_contract ALTER COLUMN added_tax DROP NOT NULL;

update public.tb_contract set status ='0' where contract_type ='1' or contract_type ='4'

ALTER TABLE public.tb_vehicle_check ADD registration_time timestamp NULL;
COMMENT ON COLUMN public.tb_vehicle_check.registration_time IS '初登日期';

ALTER TABLE public.tb_vehicle_check ADD parent_id varchar(32) NULL;
COMMENT ON COLUMN public.tb_vehicle_check.parent_id IS '租赁交车id';

ALTER TABLE public.tb_vehicle_check ADD status varchar(2) NULL DEFAULT 0;
COMMENT ON COLUMN public.tb_vehicle_check.status IS '状态（0待提交，1已提交）';
ALTER TABLE public.tb_vehicle_check ALTER COLUMN check_status DROP NOT NULL;
ALTER TABLE public.tb_vehicle_check ALTER COLUMN vehicle_type DROP NOT NULL;
ALTER TABLE public.tb_vehicle_check ALTER COLUMN check_type DROP NOT NULL;

COMMENT ON COLUMN public.tb_vehicle_check.check_status IS '验收结果(0不合格，1合格)';
ALTER TABLE public.tb_vehicle_check ADD receive_status varchar(2) NULL DEFAULT 0;
COMMENT ON COLUMN public.tb_vehicle_check.receive_status IS '收车状态（0未收车,1已收车）';

ALTER TABLE public.tb_insurance ADD contract_id varchar(32) NULL;
COMMENT ON COLUMN public.tb_insurance.contract_id IS '所属合同';
ALTER TABLE public.tb_insurance ADD frame_number varchar(20) NULL;
COMMENT ON COLUMN public.tb_insurance.frame_number IS '车架号';

CREATE TABLE public.tb_insurance_remind (
	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,
	contract_id varchar(32) NULL,
	frame_number varchar(20) NULL,
	has_compulsory_insurance varchar(2) NULL DEFAULT 0,
	compulsory_start_time timestamp NULL,
	compulsory_valid_time timestamp NULL,
	has_commercial_insurance varchar(2) NULL DEFAULT 0,
	insurance_id varchar(32) NULL,
	status varchar(2) NULL DEFAULT 1,
	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_remind_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.tb_insurance_remind IS '保险提醒';

-- Column comments

COMMENT ON COLUMN public.tb_insurance_remind.vehicle_id IS '所属车辆';
COMMENT ON COLUMN public.tb_insurance_remind.project_id IS '所属项目';
COMMENT ON COLUMN public.tb_insurance_remind.vehicle_model IS '车辆型号';
COMMENT ON COLUMN public.tb_insurance_remind.vehicle_type IS '车辆种类';
COMMENT ON COLUMN public.tb_insurance_remind.contract_id IS '所属合同';
COMMENT ON COLUMN public.tb_insurance_remind.frame_number IS '车架号';
COMMENT ON COLUMN public.tb_insurance_remind.has_compulsory_insurance IS '是否购买交强险（0,1）';
COMMENT ON COLUMN public.tb_insurance_remind.compulsory_start_time IS '起保日期';
COMMENT ON COLUMN public.tb_insurance_remind.compulsory_valid_time IS '有效日期';
COMMENT ON COLUMN public.tb_insurance_remind.has_commercial_insurance IS '是否购买商业保险（0,1）';
COMMENT ON COLUMN public.tb_insurance_remind.insurance_id IS '提醒保险id';
COMMENT ON COLUMN public.tb_insurance_remind.status IS '提醒状态（0已过期，1提醒中）';
COMMENT ON COLUMN public.tb_insurance_remind.sys_org_code IS '所属机构';
COMMENT ON COLUMN public.tb_insurance_remind.create_time IS '创建时间';
COMMENT ON COLUMN public.tb_insurance_remind.update_time IS '更新时间';
COMMENT ON COLUMN public.tb_insurance_remind.create_by IS '创建人';
COMMENT ON COLUMN public.tb_insurance_remind.update_by IS '更新人';
COMMENT ON COLUMN public.tb_insurance_remind.del_flag IS '删除标志';

ALTER TABLE public.tb_vehicle ADD source_type varchar(2) NULL DEFAULT 1;
COMMENT ON COLUMN public.tb_vehicle.source_type IS '来源（1车辆档案，2采购交车）';

update tb_contract set signing_name = signing_id where contract_type > '4';
update tb_contract set signing_name = t.name from tb_customer_archives t where tb_contract.contract_type = '1' and t.id = tb_contract.signing_id ;
update tb_contract set signing_name = t.name from tb_insurance_company_archives t where tb_contract.contract_type = '2' and t.id = tb_contract.signing_id ;
update tb_contract set signing_name = t.name from tb_repair_shop_archives t where tb_contract.contract_type = '3' and t.id = tb_contract.signing_id ;
update tb_contract set signing_name = t.name from tb_vehicle_supplier_archives t where tb_contract.contract_type = '4' and t.id = tb_contract.signing_id ;

update tb_vehicle_check_detail set frame_number=v.frame_number , vehicle_brand=v.vehicle_brand ,vehicle_model=v.vehicle_model ,vehicle_type=v.vehicle_type,plate_number=v.plate_number from tb_vehicle v where tb_vehicle_check_detail.plate_number is null and v.id =vehicle_id;

update tb_insurance set compulsory_valid_time = commercial_valid_time where compulsory_valid_time is null;
update tb_insurance set compulsory_start_time  = commercial_start_time  where compulsory_start_time is null;
ALTER TABLE public.tb_insurance_remind ADD compulsory_insurance_no varchar(32) NULL;
COMMENT ON COLUMN public.tb_insurance_remind.compulsory_insurance_no IS '交强险保险单号';
ALTER TABLE public.tb_insurance_remind ADD commercial_insurance_no varchar(32) NULL;
COMMENT ON COLUMN public.tb_insurance_remind.commercial_insurance_no IS '商业保险单号';

ALTER TABLE public.tb_insurance ADD plate_number varchar(10) NULL;
COMMENT ON COLUMN public.tb_insurance.plate_number IS '车牌号';
ALTER TABLE public.tb_insurance_remind ADD plate_number varchar(10) NULL;
COMMENT ON COLUMN public.tb_insurance_remind.plate_number IS '车牌号';

update tb_insurance set plate_number = t.plate_number  from tb_vehicle t where t.id = tb_insurance.vehicle_id and tb_insurance.plate_number is null;
update tb_insurance_remind set plate_number= t.plate_number  from tb_vehicle t where t.id = tb_insurance_remind.vehicle_id and tb_insurance_remind.plate_number is null;

ALTER TABLE public.tb_annual_inspection_info ADD source_type varchar(2) NULL DEFAULT 1;
COMMENT ON COLUMN public.tb_annual_inspection_info.source_type IS '来源（1车辆档案，2采购交车）';
