DROP TABLE IF EXISTS trans_record;
CREATE TABLE trans_record
(
    id               BIGINT PRIMARY KEY,
    fileId           BIGINT,
    sheetId          BIGINT,
    bankName         VARCHAR,
    personName       VARCHAR,
    personCertNo     VARCHAR,
    personAccount    VARCHAR,
    personCardNo     VARCHAR,
    otherName        VARCHAR,
    otherCertNo      VARCHAR,
    otherAccount     VARCHAR,
    otherCardNo      VARCHAR,
    transType        VARCHAR,
    debitCreditFlag  VARCHAR,
    currency         VARCHAR,
    transAmount      DECIMAL(18, 2),
    transBalance     DECIMAL(18, 2),
    transDateTime    TIMESTAMP,
    transDate        DATE,
    transSerialNo    VARCHAR,
    otherBalance     DECIMAL(18, 2),
    otherAccountBank VARCHAR,
    transSummary     VARCHAR,
    branchName       VARCHAR,
    branchCode       VARCHAR,
    cashFlag         VARCHAR,
    terminalNo       VARCHAR,
    transLocation    VARCHAR,
    merchantName     VARCHAR,
    merchantNo       VARCHAR,
    personIpAddress  VARCHAR,
    personMacAddress VARCHAR,
    transClerkNo     VARCHAR,
    transChannel     VARCHAR,
    remark           VARCHAR,
    personLibId      VARCHAR,
    ext1             VARCHAR
);
-- DuckDB 索引约定：
-- 1. CREATE INDEX 创建的是 ART 索引，适合单列高选择性等值 / IN 查询。
-- 2. 时间、日期、金额等分析过滤主要依赖 DuckDB 自动维护的 zonemap，不额外创建 ART 索引。
-- 3. 为避免拖慢批量导入，仅给“人 / 卡 / 账号 / 手机 / 证件号”这类高频定位列建索引。
CREATE INDEX idx_trans_record_person_name ON trans_record (personName);
CREATE INDEX idx_trans_record_person_card_no ON trans_record (personCardNo);
CREATE INDEX idx_trans_record_other_name ON trans_record (otherName);
CREATE INDEX idx_trans_record_other_card_no ON trans_record (otherCardNo);

DROP TABLE IF EXISTS trans_record_all;
CREATE TABLE trans_record_all
(
    id               BIGINT PRIMARY KEY,
    fileId           BIGINT,
    sheetId          BIGINT,
    bankName         VARCHAR,
    personName       VARCHAR,
    personCertNo     VARCHAR,
    personAccount    VARCHAR,
    personCardNo     VARCHAR,
    otherName        VARCHAR,
    otherCertNo      VARCHAR,
    otherAccount     VARCHAR,
    otherCardNo      VARCHAR,
    transType        VARCHAR,
    debitCreditFlag  VARCHAR,
    currency         VARCHAR,
    transAmount      DECIMAL(18, 2),
    transBalance     DECIMAL(18, 2),
    transDateTime    TIMESTAMP,
    transDate        DATE,
    transSerialNo    VARCHAR,
    otherBalance     DECIMAL(18, 2),
    otherAccountBank VARCHAR,
    transSummary     VARCHAR,
    branchName       VARCHAR,
    branchCode       VARCHAR,
    cashFlag         VARCHAR,
    terminalNo       VARCHAR,
    transLocation    VARCHAR,
    merchantName     VARCHAR,
    merchantNo       VARCHAR,
    personIpAddress  VARCHAR,
    personMacAddress VARCHAR,
    transClerkNo     VARCHAR,
    transChannel     VARCHAR,
    remark           VARCHAR,
    personLibId      VARCHAR,
    ext1             VARCHAR
);

DROP TABLE IF EXISTS call_record;
create table call_record
(
    id                     BIGINT PRIMARY KEY DEFAULT nextval('call_seq'),
    fileId                 BIGINT,
    sheetId                BIGINT,
    personName             VARCHAR,
    personPhone            VARCHAR,
    personPhoneArea        VARCHAR,
    personCertNo           VARCHAR,
    personRegionId         VARCHAR,
    personCellId           VARCHAR,
    personStationName      VARCHAR,
    personStationLongitude VARCHAR,
    personStationLatitude  VARCHAR,
    operatorId             VARCHAR,
    callDateTime           TIMESTAMP,
    callDate               DATE,
    callTime               TIME,
    duration               INTEGER,
    callDirection          VARCHAR,
    callType               VARCHAR,
    otherName              VARCHAR,
    otherPhone             VARCHAR,
    otherPhoneArea         VARCHAR,
    otherCertNo            VARCHAR,
    remark                 VARCHAR,
    personLibId            VARCHAR,
    callLocal              VARCHAR
);
CREATE INDEX idx_call_record_person_name ON call_record (personName);
CREATE INDEX idx_call_record_person_phone ON call_record (personPhone);
CREATE INDEX idx_call_record_other_name ON call_record (otherName);
CREATE INDEX idx_call_record_other_phone ON call_record (otherPhone);


DROP TABLE IF EXISTS call_record_all;
create table call_record_all
(
    id                     BIGINT PRIMARY KEY,
    fileId                 BIGINT,
    sheetId                BIGINT,
    personName             VARCHAR,
    personPhone            VARCHAR,
    personPhoneArea        VARCHAR,
    personCertNo           VARCHAR,
    personRegionId         VARCHAR,
    personCellId           VARCHAR,
    personStationName      VARCHAR,
    personStationLongitude VARCHAR,
    personStationLatitude  VARCHAR,
    operatorId             VARCHAR,
    callDateTime           TIMESTAMP,
    callDate               DATE,
    callTime               TIME,
    duration               INTEGER,
    callDirection          VARCHAR,
    callType               VARCHAR,
    otherName              VARCHAR,
    otherPhone             VARCHAR,
    otherPhoneArea         VARCHAR,
    otherCertNo            VARCHAR,
    remark                 VARCHAR,
    personLibId            VARCHAR,
    callLocal              VARCHAR
);

DROP TABLE IF EXISTS use_open_info_call_record;
create table use_open_info_call_record
(
    id                     BIGINT PRIMARY KEY DEFAULT nextval('use_call_seq'),
    fileId                 BIGINT,
    sheetId                BIGINT,
    personName             VARCHAR,
    personPhone            VARCHAR,
    personPhoneArea        VARCHAR,
    personCertNo           VARCHAR,
    personRegionId         VARCHAR,
    personCellId           VARCHAR,
    personStationName      VARCHAR,
    personStationLongitude VARCHAR,
    personStationLatitude  VARCHAR,
    operatorId             VARCHAR,
    callDateTime           TIMESTAMP,
    callDate               DATE,
    callTime               TIME,
    duration               INTEGER,
    callDirection          VARCHAR,
    callType               VARCHAR,
    otherName              VARCHAR,
    otherPhone             VARCHAR,
    otherPhoneArea         VARCHAR,
    otherCertNo            VARCHAR,
    remark                 VARCHAR,
    personLibId            VARCHAR,
    callLocal              VARCHAR
);

DROP TABLE IF EXISTS account_opening_info;
create table account_opening_info
(
    id                BIGINT PRIMARY KEY,
    fileId            BIGINT,
    sheetId           BIGINT,
    personName        VARCHAR,
    holderCertNo      VARCHAR,
    personCardNo      VARCHAR,
    personAccountNo   VARCHAR,
    openingDate       date,
    balance           DECIMAL(18, 2),
    availableBalance  DECIMAL(18, 2),
    currency          VARCHAR,
    branchCode        VARCHAR,
    branchName        VARCHAR,
    accountStatus     VARCHAR,
    closingDate       date,
    accountType       VARCHAR,
    mailingAddress    VARCHAR,
    phone             VARCHAR,
    remark            VARCHAR,
    bankName          VARCHAR,
    depositTerm       VARCHAR,
    linkedFundAccount VARCHAR,
    closedBranch      VARCHAR,
    lastTransDateTime TIMESTAMP
);
CREATE INDEX idx_account_opening_info_person_name ON account_opening_info (personName);
CREATE INDEX idx_account_opening_info_person_card_no ON account_opening_info (personCardNo);
CREATE INDEX idx_account_opening_info_phone ON account_opening_info (phone);

DROP TABLE IF EXISTS call_opening_info;
create table call_opening_info
(
    id                     BIGINT PRIMARY KEY,
    fileId                 BIGINT,
    sheetId                BIGINT,
    phoneType              VARCHAR,
    operatorId             VARCHAR,
    personPhone            VARCHAR,
    personType             VARCHAR,
    personName             VARCHAR,
    personCertNo           VARCHAR,
    personCertAddress      VARCHAR,
    mailingAddress         VARCHAR,
    province               VARCHAR,
    city                   VARCHAR,
    phoneStatus            VARCHAR,
    activationDateTime     TIMESTAMP,
    closingDateTime        TIMESTAMP,
    firstCallDateTime      TIMESTAMP,
    lastSuspensionDateTime TIMESTAMP
);
CREATE INDEX idx_call_opening_info_person_phone ON call_opening_info (personPhone);
CREATE INDEX idx_call_opening_info_person_name ON call_opening_info (personName);

DROP TABLE IF EXISTS express_info;
CREATE TABLE express_info
(
    -- 快递收件信息表主键id
    id                      BIGINT PRIMARY KEY,
    -- 文件id
    fileId                  BIGINT,
    -- sheetId
    sheetId                 BIGINT,
    -- 收件人姓名
    recipientName           VARCHAR,
    -- 收件人现住址地址名称
    recipientCurrentAddress VARCHAR,
    -- 收件人联系电话
    recipientPhone          VARCHAR,
    -- 寄件人姓名
    senderName              VARCHAR,
    -- 寄件人电话
    senderPhone             VARCHAR,
    -- 寄件日期
    sendDate                TIMESTAMP,
    -- 寄件人现住址地址名称
    senderCurrentAddress    VARCHAR,
    -- 运单号
    waybillNumber           VARCHAR,
    -- 始发地名称
    departurePlace          VARCHAR,
    -- 目的地名称
    destinationPlace        VARCHAR,
    -- 收件单位名称
    recipientCompany        VARCHAR,
    -- 快递公司编号
    expressCompanyCode      VARCHAR,
    -- 物品名称
    goodsName               VARCHAR
);
CREATE INDEX idx_express_info_recipient_name ON express_info (recipientName);
CREATE INDEX idx_express_info_recipient_phone ON express_info (recipientPhone);
CREATE INDEX idx_express_info_sender_phone ON express_info (senderPhone);

DROP TABLE IF EXISTS resident_population_info;
CREATE TABLE resident_population_info
(
    -- 主键id
    id              BIGINT PRIMARY KEY,
    -- 文件id
    fileId          BIGINT,
    -- sheetId
    sheetId         BIGINT,
    -- 公民身份号码
    personCertNo    VARCHAR,
    -- 出生地省市县
    birthPlace      VARCHAR,
    -- 出生日期
    birthDate       DATE,
    -- 服务所处
    serviceLocation VARCHAR,
    -- 籍贯国家（地区）
    nativeCountry   VARCHAR,
    -- 出生地国家（地区）
    birthCountry    VARCHAR,
    -- 籍贯省市县（区）
    nativePlace     VARCHAR,
    -- 民族
    nationality     VARCHAR,
    -- 身高
    height          VARCHAR,
    -- 照片1
    photo1          VARCHAR,
    -- 所属派出所名称
    policeStation   VARCHAR,
    -- 所属省市县（区）
    belongArea      VARCHAR,
    -- 性别
    gender          VARCHAR,
    -- 姓名
    name            VARCHAR,
    -- 照片2
    photo2          VARCHAR,
    -- 曾用名
    formerName      VARCHAR,
    -- 住址详址
    address         VARCHAR,
    -- 学历
    education       VARCHAR,
    -- 学位
    degree          VARCHAR
);
CREATE INDEX idx_resident_population_info_name ON resident_population_info (name);

--1683362389912
DROP TABLE IF EXISTS hotel_stay_info;
CREATE TABLE hotel_stay_info
(
    id             BIGINT PRIMARY KEY,
    fileId         BIGINT,
    sheetId        BIGINT,
    personName     VARCHAR,
    personCertNo   VARCHAR,
    personCertName VARCHAR,
    gender         VARCHAR,
    addressCity    VARCHAR,
    addressDetail  VARCHAR,
    hotelName      VARCHAR,
    hotelAddress   VARCHAR,
    roomNo         VARCHAR,
    inDateTime     TIMESTAMP,
    outDateTime    TIMESTAMP
);
CREATE INDEX idx_hotel_stay_info_person_name ON hotel_stay_info (personName);
CREATE INDEX idx_hotel_stay_info_person_cert_no ON hotel_stay_info (personCertNo);
-- 航班预订信息表
DROP TABLE IF EXISTS flight_ticket_info;
CREATE TABLE flight_ticket_info
(
    id           BIGINT PRIMARY KEY,
    fileId       BIGINT,
    sheetId      BIGINT,
    depDateTime  TIMESTAMP,
    arrDateTime  TIMESTAMP,
    depAirport   VARCHAR,
    arrAirport   VARCHAR,
    personName   VARCHAR,
    surname      VARCHAR,
    personNameEn VARCHAR,
    personCertNo VARCHAR,
    flightNo     VARCHAR,
    carrier      VARCHAR,
    pnr          VARCHAR,
    ticketNo     VARCHAR,
    fare         DECIMAL(18, 2),
    issueDate    DATE,
    agent        VARCHAR,
    flightClass  VARCHAR,
    status       VARCHAR
);
CREATE INDEX idx_flight_ticket_info_person_name ON flight_ticket_info (personName);
CREATE INDEX idx_flight_ticket_info_person_cert_no ON flight_ticket_info (personCertNo);
-- 航班出发信息表
DROP TABLE IF EXISTS flight_departure_info;
CREATE TABLE flight_departure_info
(
    id             BIGINT PRIMARY KEY,
    fileId         BIGINT,
    sheetId        BIGINT,
    flightNo       VARCHAR,
    flightSuffix   VARCHAR,
    carrier        VARCHAR,
    flightDateTime TIMESTAMP,
    depDateTime    TIMESTAMP,
    arrDateTime    TIMESTAMP,
    depCode        VARCHAR,
    arrCode        VARCHAR,
    surname        VARCHAR,
    givenName      VARCHAR,
    middleName     VARCHAR,
    personName     VARCHAR,
    personNameCn   VARCHAR,
    gender         VARCHAR,
    birthDate      DATE,
    birthPlace     VARCHAR,
    personCertNo   VARCHAR,
    seatNo         VARCHAR
);
CREATE INDEX idx_flight_departure_info_person_name ON flight_departure_info (personName);
-- 同航班服务
DROP TABLE IF EXISTS together_flight_info;
CREATE TABLE together_flight_info
(
    id             BIGINT PRIMARY KEY,
    fileId         BIGINT,
    sheetId        BIGINT,
    flightDateTime TIMESTAMP,
    depDateTime    TIMESTAMP,
    flightNo       VARCHAR,
    origin         VARCHAR,
    destination    VARCHAR,
    seatNo         VARCHAR,
    cabinClass     VARCHAR,
    personCertType VARCHAR,
    personCertNo   VARCHAR,
    companionName  VARCHAR
);
CREATE INDEX idx_together_flight_info_person_cert_no ON together_flight_info (personCertNo);
CREATE INDEX idx_together_flight_info_companion_name ON together_flight_info (companionName);
-- 同火车服务
DROP TABLE IF EXISTS together_train_info;
CREATE TABLE together_train_info
(
    id             BIGINT PRIMARY KEY,
    fileId         BIGINT,
    sheetId        BIGINT,
    trainNo        VARCHAR,
    depDateTime    TIMESTAMP,
    origin         VARCHAR,
    destination    VARCHAR,
    carNo          VARCHAR,
    seatNo         VARCHAR,
    personName     VARCHAR,
    companionName  VARCHAR,
    personCertType VARCHAR,
    personCertNo   VARCHAR
);
CREATE INDEX idx_together_train_info_person_name ON together_train_info (personName);
CREATE INDEX idx_together_train_info_person_cert_no ON together_train_info (personCertNo);
CREATE INDEX idx_together_train_info_companion_name ON together_train_info (companionName);

-- 铁路订票信息
DROP TABLE IF EXISTS train_ticket_info;
CREATE TABLE train_ticket_info
(
    id           BIGINT PRIMARY KEY,
    fileId       BIGINT,
    sheetId      BIGINT,
    personName   VARCHAR,
    personCertNo VARCHAR,
    trainNo      VARCHAR,
    origin       VARCHAR,
    destination  VARCHAR,
    depDateTime  TIMESTAMP,
    carNo        VARCHAR,
    seatNo       VARCHAR,
    ticketStatus VARCHAR,
    refundDate   TIMESTAMP,
    changeDate   TIMESTAMP
);
CREATE INDEX idx_train_ticket_info_person_name ON train_ticket_info (personName);

-- 同住服务
DROP TABLE IF EXISTS together_live_info;
CREATE TABLE together_live_info
(
    id             BIGINT PRIMARY KEY,
    fileId         BIGINT,
    sheetId        BIGINT,
    queryName      VARCHAR,
    queryCertNo    VARCHAR,
    queryCertType    VARCHAR,
    queryGender    VARCHAR,
    queryAge       integer,
    queryEthnicity VARCHAR,
    queryOrigin    VARCHAR,
    togetherCertNo VARCHAR,
    hotelName      VARCHAR,
    roomNo         VARCHAR,
    inDateTime     TIMESTAMP,
    outDateTime    TIMESTAMP,
    coType         VARCHAR,
    stayCount      integer,
    remark         VARCHAR
);
CREATE INDEX idx_together_live_info_together_name ON together_live_info (queryName);
CREATE INDEX idx_together_live_info_query_cert_no ON together_live_info (queryCertNo);

-- 汽车票务联网售票记录
DROP TABLE IF EXISTS track_car_ticket_info;
CREATE TABLE track_car_ticket_info
(
    id               BIGINT PRIMARY KEY,
    fileId           BIGINT,
    sheetId          BIGINT,
    busCarType       VARCHAR,
    busNumber        VARCHAR,
    busClassType     VARCHAR,
    personName       VARCHAR,
    personCertType   VARCHAR,
    plateNumber      VARCHAR,
    ticketPrice      VARCHAR,
    ticketType       VARCHAR,
    ticketStatus     VARCHAR,
    depDateTime      TIMESTAMP,
    buyDateTime      TIMESTAMP,
    receiveSmsPhone  VARCHAR,
    destination      VARCHAR,
    pickUpTime       TIMESTAMP,
    origin           VARCHAR,
    refundAmount     VARCHAR,
    refundDateTime   TIMESTAMP,
    salesChannelCode VARCHAR,
    personCertNo     VARCHAR,
    seatNo           VARCHAR
);
CREATE INDEX idx_track_car_ticket_info_person_name ON track_car_ticket_info (personName);
CREATE INDEX idx_track_car_ticket_info_person_cert_no ON track_car_ticket_info (personCertNo);

-- 时序表
DROP TABLE IF EXISTS time_series_info;
CREATE TABLE time_series_info
(
    id             BIGINT PRIMARY KEY,
    tableNameEn    VARCHAR,
    tableNameCn    VARCHAR,
    personName     VARCHAR,
    otherName      VARCHAR,
    seriesDateTime TIMESTAMP,
    ext1           VARCHAR,
    ext2           VARCHAR,
    ext3           VARCHAR,
    ext4           VARCHAR,
    ext5           VARCHAR,
    ext6           VARCHAR,
    ext7           VARCHAR,
    ext8           VARCHAR,
    ext9           VARCHAR
);
CREATE INDEX idx_time_series_info_person_name ON time_series_info (personName);
CREATE INDEX idx_time_series_info_other_name ON time_series_info (otherName);