-- =============================================
-- ILDM_FEE_CUSTOMER_INFO 建表脚本 (Oracle)
-- 自动整理生成，已合并历史patch字段
-- 执行方式: sqlplus developer/dev123@localhost/XEPDB1 @ILDM_FEE_CUSTOMER_INFO.sql
-- =============================================

WHENEVER SQLERROR EXIT SQL.SQLCODE;

BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_ILDM_FEE_CUSTOMER_INFO';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -2289 THEN RAISE; END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ILDM_FEE_CUSTOMER_INFO CASCADE CONSTRAINTS';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;
END;
/

CREATE TABLE ILDM_FEE_CUSTOMER_INFO (
    CUSTOMER_SYSID NUMBER(19) NOT NULL,
    CUSTOMER_REGNO VARCHAR2(64 CHAR),
    CUSTOMER_NO VARCHAR2(64 CHAR),
    CUSTOMER_NAME_C VARCHAR2(200 CHAR),
    CUSTOMER_NAME_EN VARCHAR2(200 CHAR),
    CUSTOMER_SHORT_NAME VARCHAR2(100 CHAR),
    CUSTOMER_SHORT_NAME_EN VARCHAR2(100 CHAR),
    CUSTOMER_TAXNO VARCHAR2(50 CHAR),
    CUSTOMER_ADDRESS VARCHAR2(200 CHAR),
    OFFICEPHONE VARCHAR2(20 CHAR),
    CUSTOMER_BANK VARCHAR2(100 CHAR),
    CUSTOMER_BANKACCOUNT VARCHAR2(50 CHAR),
    CUSTOMER_CHARACTER VARCHAR2(50 CHAR),
    CUSTOMER_REGADDR VARCHAR2(200 CHAR),
    CUSTOMER_REGDATE DATE,
    CUSTOMER_NATURE VARCHAR2(50 CHAR),
    CUSTOMER_ZIP VARCHAR2(20 CHAR),
    CUSTOMER_1_TYPEFLAG VARCHAR2(10 CHAR),
    CUSTOMER_2_TYPEFLAG VARCHAR2(10 CHAR),
    CUSTOMER_3_TYPEFLAG VARCHAR2(10 CHAR),
    CUSTOMER_4_TYPEFLAG VARCHAR2(10 CHAR),
    PLACE_TYPE VARCHAR2(10 CHAR),
    ACTIVEFLAG VARCHAR2(10 CHAR),
    CUSTOMER_SYSID_HT VARCHAR2(64 CHAR),
    DOWN_DATE DATE,
    OPEN_FLAG VARCHAR2(10 CHAR),
    CREATE_BY VARCHAR2(64 CHAR),
    CREATE_TIME DATE DEFAULT SYSDATE,
    UPDATE_BY VARCHAR2(64 CHAR),
    UPDATE_TIME DATE DEFAULT SYSDATE,
    SYSTEM_TYPE VARCHAR2(20 CHAR),
    REMARK VARCHAR2(500 CHAR),
    ORG_ID VARCHAR2(64 CHAR),
    EMAIL_ADDRESS VARCHAR2(100 CHAR),
    OPPOSITE_ID VARCHAR2(64 CHAR),
    OPPOSITE_CHARACTER VARCHAR2(10 CHAR),
    GAT_FLAG VARCHAR2(2 CHAR),
    GROUP_FLAG VARCHAR2(2 CHAR),
    COUNTRY VARCHAR2(20 CHAR),
    COUNTRY_NAME VARCHAR2(50 CHAR),
    CITY VARCHAR2(50 CHAR),
    POSTCODE VARCHAR2(20 CHAR),
    LEGAL_PERSON_ID VARCHAR2(64 CHAR),
    LEGAL_PERSON_NAME VARCHAR2(50 CHAR),
    LEGAL_PERSON_ACCOUNT VARCHAR2(64 CHAR),
    LEGAL_PERSON_CARD_ID VARCHAR2(100 CHAR),
    WEBSITE VARCHAR2(200 CHAR),
    CERTIFICATE_TYPE VARCHAR2(20 CHAR),
    CERTIFICATE_NUM VARCHAR2(100 CHAR),
    TIN_CODE VARCHAR2(50 CHAR),
    ABROAD_CODE VARCHAR2(50 CHAR),
    REGISTERED_CAPITAL NUMBER(20, 2),
    REG_CAPITAL VARCHAR2(100 CHAR),
    BUS_SCOPE CLOB,
    OPPOSITE_CODE VARCHAR2(64 CHAR),
    OPPOSITE_STATUS VARCHAR2(20 CHAR) DEFAULT 'EFFECTUAL',
    IS_SGAT VARCHAR2(2 CHAR) DEFAULT '0',
    IS_JTN VARCHAR2(2 CHAR) DEFAULT '0',
    NATURE_OF_ENTERPRISE_ORG VARCHAR2(10 CHAR),
    NATURE_OF_ENTERPRISE_ORG_LEV VARCHAR2(20 CHAR),
    RELATIVE_TYPE VARCHAR2(30 CHAR),
    IS_DELETED VARCHAR2(2 CHAR) DEFAULT 'N',
    COMMENTS VARCHAR2(500 CHAR),
    SYNC_STATUS VARCHAR2(20 CHAR) DEFAULT 'unsynced' NOT NULL,
    SYNC_TIME DATE,
    SYNC_MESSAGE VARCHAR2(500 CHAR),
    CONSTRAINT PK_ILDM_FEE_CUSTOMER_INFO PRIMARY KEY (CUSTOMER_SYSID),
    LINKED_ORG_ID NUMBER(19),
    LINKED_USER_ACCOUNT VARCHAR2(64)
);

COMMENT ON TABLE ILDM_FEE_CUSTOMER_INFO IS '客户/相对方信息表';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CUSTOMER_SYSID IS '客户系统ID';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CUSTOMER_NAME_C IS '公司全称';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CUSTOMER_TAXNO IS '统一社会信用代码';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CUSTOMER_REGADDR IS '注册地址';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.EMAIL_ADDRESS IS '邮箱地址';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.OPPOSITE_ID IS '法务系统相对方ID（唯一标识）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.OPPOSITE_CHARACTER IS '相对方性质分类';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.GAT_FLAG IS '是否涉港澳台标识';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.GROUP_FLAG IS '是否集团内';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.COUNTRY IS '国家编码';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.COUNTRY_NAME IS '国家名称';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.LEGAL_PERSON_NAME IS '法定代表人或负责人姓名';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CERTIFICATE_TYPE IS '自然人证件类型';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.CERTIFICATE_NUM IS '自然人证件号码';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.TIN_CODE IS 'TIN码（税务识别号）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.REGISTERED_CAPITAL IS '注册资金（单位：元）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.REG_CAPITAL IS '注册资本';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.OPPOSITE_CODE IS '相对方编码';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.OPPOSITE_STATUS IS '状态（EFFECTUAL:有效,INVALID:无效）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.IS_SGAT IS '是否SGAT（1:是,0:否）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.IS_JTN IS '是否JTN（1:是,0:否）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.NATURE_OF_ENTERPRISE_ORG IS '组织性质';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.NATURE_OF_ENTERPRISE_ORG_LEV IS '组织性质子级';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.RELATIVE_TYPE IS '类型';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.IS_DELETED IS '法务系统删除标记';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.COMMENTS IS '备注【法务】';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.LINKED_ORG_ID IS '关联内部组织ID（集团内货代公司对应组织树节点ID）';
COMMENT ON COLUMN ILDM_FEE_CUSTOMER_INFO.LINKED_USER_ACCOUNT IS '关联用户账号/手机号（内部货代公司对应的业务端登录账号）';

CREATE INDEX IDX_CUSTOMER_NO ON ILDM_FEE_CUSTOMER_INFO (CUSTOMER_NO);
CREATE INDEX IDX_OPPOSITE_ID ON ILDM_FEE_CUSTOMER_INFO (OPPOSITE_ID);
CREATE INDEX IDX_CUSTOMER_TAXNO ON ILDM_FEE_CUSTOMER_INFO (CUSTOMER_TAXNO);
CREATE INDEX IDX_SYSTEM_TYPE ON ILDM_FEE_CUSTOMER_INFO (SYSTEM_TYPE);
CREATE INDEX IDX_CREATE_TIME ON ILDM_FEE_CUSTOMER_INFO (CREATE_TIME);
CREATE INDEX IDX_SYNC_STATUS ON ILDM_FEE_CUSTOMER_INFO (SYNC_STATUS);

CREATE SEQUENCE SEQ_ILDM_FEE_CUSTOMER_INFO
    START WITH 4
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

INSERT INTO ILDM_FEE_CUSTOMER_INFO
(CUSTOMER_SYSID, CUSTOMER_NAME_C, CUSTOMER_TAXNO, CUSTOMER_REGADDR, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME,
 EMAIL_ADDRESS, OPPOSITE_ID, OPPOSITE_CHARACTER, GAT_FLAG, GROUP_FLAG, COUNTRY, COUNTRY_NAME,
 LEGAL_PERSON_NAME, REG_CAPITAL, OPPOSITE_CODE, OPPOSITE_STATUS, IS_SGAT, IS_JTN,
 NATURE_OF_ENTERPRISE_ORG, NATURE_OF_ENTERPRISE_ORG_LEV, RELATIVE_TYPE, IS_DELETED,
 COMMENTS, SYNC_STATUS, SYNC_TIME, SYNC_MESSAGE)
VALUES
(1, '宁波港船务货运代理有限公司', '91330201MA2ABCDE9X', '宁波市北仑区明州西路188号', 'local_test',
 TO_DATE('2026-05-20 09:12:33', 'YYYY-MM-DD HH24:MI:SS'),
 'local_test', TO_DATE('2026-05-20 09:15:10', 'YYYY-MM-DD HH24:MI:SS'),
 'zhoujingli@example.com', 'JT-10256', '1', 'N', 'N', NULL, NULL,
 '李四', '5000万元', 'OPP-202605210001', 'EFFECTUAL', '0', '0',
 'QY', 'GNQY', 'supplier', 'N',
 '本地初始化测试数据', 'synced', TO_DATE('2026-05-20 09:15:10', 'YYYY-MM-DD HH24:MI:SS'), '已按数据采集接口规范同步到法务系统');
INSERT INTO ILDM_FEE_CUSTOMER_INFO
(CUSTOMER_SYSID, CUSTOMER_NAME_C, CUSTOMER_TAXNO, CUSTOMER_REGADDR, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME,
 EMAIL_ADDRESS, OPPOSITE_ID, OPPOSITE_CHARACTER, GAT_FLAG, GROUP_FLAG, COUNTRY, COUNTRY_NAME,
 LEGAL_PERSON_NAME, REG_CAPITAL, OPPOSITE_CODE, OPPOSITE_STATUS, IS_SGAT, IS_JTN,
 NATURE_OF_ENTERPRISE_ORG, NATURE_OF_ENTERPRISE_ORG_LEV, RELATIVE_TYPE, IS_DELETED,
 COMMENTS, SYNC_STATUS, SYNC_TIME, SYNC_MESSAGE)
VALUES
(2, '王小明', NULL, NULL, 'local_test',
 TO_DATE('2026-05-20 14:20:05', 'YYYY-MM-DD HH24:MI:SS'),
 'local_test', TO_DATE('2026-05-20 14:20:05', 'YYYY-MM-DD HH24:MI:SS'),
 NULL, NULL, '2', 'N', 'N', NULL, NULL,
 NULL, NULL, 'OPP-202605210002', 'EFFECTUAL', '0', '0',
 'QT', NULL, 'client', 'N',
 '个人类型测试数据', 'unsynced', NULL, '新增后尚未同步');
INSERT INTO ILDM_FEE_CUSTOMER_INFO
(CUSTOMER_SYSID, CUSTOMER_NAME_C, CUSTOMER_TAXNO, CUSTOMER_REGADDR, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME,
 EMAIL_ADDRESS, OPPOSITE_ID, OPPOSITE_CHARACTER, GAT_FLAG, GROUP_FLAG, COUNTRY, COUNTRY_NAME,
 LEGAL_PERSON_NAME, REG_CAPITAL, OPPOSITE_CODE, OPPOSITE_STATUS, IS_SGAT, IS_JTN,
 NATURE_OF_ENTERPRISE_ORG, NATURE_OF_ENTERPRISE_ORG_LEV, RELATIVE_TYPE, IS_DELETED,
 COMMENTS, SYNC_STATUS, SYNC_TIME, SYNC_MESSAGE)
VALUES
(3, 'Ocean Energy Pte. Ltd.', NULL, '88 Harbour Street, London', 'local_test',
 TO_DATE('2026-05-18 11:08:22', 'YYYY-MM-DD HH24:MI:SS'),
 'local_test', TO_DATE('2026-05-18 11:10:40', 'YYYY-MM-DD HH24:MI:SS'),
 'alice.brown@ocean-energy.com', 'JT-10888', '3', 'N', 'N', 'GJ826', '英国',
 NULL, 'USD 2,000,000', 'OPP-202605210003', 'EFFECTUAL', '0', '0',
 'QY', 'WZQY', 'Partner', 'N',
 '境外组织测试数据', 'failed', TO_DATE('2026-05-18 11:10:40', 'YYYY-MM-DD HH24:MI:SS'), '法务系统返回：注册地址长度超限，请修正后重试');

COMMIT;