-- ----------------------------
-- 合同表添加合同状态字段 (Oracle)
-- 说明：此脚本用于添加合同状态字段和相关字典数据
-- 请根据实际情况谨慎执行，建议先备份数据
-- ----------------------------

-- ----------------------------
-- 第一步：添加合同状态字段
-- ----------------------------
declare
    v_count number;
begin
    select count(*) into v_count from user_tab_columns where table_name = 'BIZ_CONTRACT' and column_name = 'CONTRACT_STATUS';
    if v_count = 0 then
        execute immediate 'alter table BIZ_CONTRACT add CONTRACT_STATUS varchar2(10) default ''0''';
        execute immediate 'comment on column BIZ_CONTRACT.CONTRACT_STATUS is ''合同状态(0草稿 1生效中 2已完成 3已终止)''';
    end if;
end;
/

-- ----------------------------
-- 第二步：更新现有数据为生效中
-- ----------------------------
update BIZ_CONTRACT set CONTRACT_STATUS = '1' where CONTRACT_STATUS is null or CONTRACT_STATUS = '';
commit;

-- ----------------------------
-- 第三步：添加合同状态字典类型
-- ----------------------------
declare
    v_count number;
begin
    select count(*) into v_count from sys_dict_type where dict_type = 'biz_contract_status';
    if v_count = 0 then
        insert into sys_dict_type (dict_id, tenant_id, dict_name, dict_type, status, del_flag, create_dept, create_time, remark)
        values (110, '000000', '合同状态', 'biz_contract_status', '0', '0', 103, sysdate, '合同状态列表');
    end if;
end;
/

-- ----------------------------
-- 第四步：添加合同状态字典数据
-- ----------------------------
declare
    v_count number;
begin
    select count(*) into v_count from sys_dict_data where dict_value = '0' and dict_type = 'biz_contract_status';
    if v_count = 0 then
        insert into sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, del_flag, create_dept, create_time, remark)
        values (1100, '000000', 1, '草稿', '0', 'biz_contract_status', '', 'info', 'N', '0', '0', 103, sysdate, '草稿');
    end if;
end;
/

declare
    v_count number;
begin
    select count(*) into v_count from sys_dict_data where dict_value = '1' and dict_type = 'biz_contract_status';
    if v_count = 0 then
        insert into sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, del_flag, create_dept, create_time, remark)
        values (1101, '000000', 2, '生效中', '1', 'biz_contract_status', '', 'primary', 'N', '0', '0', 103, sysdate, '生效中');
    end if;
end;
/

declare
    v_count number;
begin
    select count(*) into v_count from sys_dict_data where dict_value = '2' and dict_type = 'biz_contract_status';
    if v_count = 0 then
        insert into sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, del_flag, create_dept, create_time, remark)
        values (1102, '000000', 3, '已完成', '2', 'biz_contract_status', '', 'success', 'N', '0', '0', 103, sysdate, '已完成');
    end if;
end;
/

declare
    v_count number;
begin
    select count(*) into v_count from sys_dict_data where dict_value = '3' and dict_type = 'biz_contract_status';
    if v_count = 0 then
        insert into sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, del_flag, create_dept, create_time, remark)
        values (1103, '000000', 4, '已终止', '3', 'biz_contract_status', '', 'danger', 'N', '0', '0', 103, sysdate, '已终止');
    end if;
end;
/

commit;
