CREATE TABLE Industry (
    industry_name VARCHAR(50) PRIMARY KEY
);

CREATE TABLE Owner (
    owner_cmnd CHAR(12) PRIMARY KEY,
    owner_name VARCHAR(50) NOT NULL,
    owner_sdt VARCHAR(20) NOT NULL,
    owner_email VARCHAR(50) NOT NULL
);

CREATE TABLE Employee (
    empl_persnbr VARCHAR(10) PRIMARY KEY,
    empl_name VARCHAR(50) NOT NULL,
    title VARCHAR(50) NOT NULL,
    branch VARCHAR(50) NOT NULL,
    area VARCHAR(50) NOT NULL
);

CREATE TABLE Company (
    tax_id CHAR(10) PRIMARY KEY,
    owner_cmnd CHAR(12) REFERENCES Owner(owner_cmnd),
    org_type VARCHAR(10) NOT NULL,
    nganh_trong_tam VARCHAR(30) REFERENCES Industry(industry_name),
    mnsegmtcd VARCHAR(10) NOT NULL,
    post_tel VARCHAR(20),
    ma_pers_nv VARCHAR(10) REFERENCES Employee(empl_persnbr),
    addrname VARCHAR(50) NOT NULL,
    gpkdngaycap DATE,
    tpktdesc VARCHAR(30),
    orgnbr CHAR(13) NOT NULL,
    org_name VARCHAR(50) NOT NULL,
    post_mail VARCHAR(50),
    website VARCHAR(50),
    start_date DATE,
    approach_status VARCHAR(10),
    priority INT NOT NULL,
    TOTAL_BALAMT BIGINT,
    TOTAL_BALANCE BIGINT,
    TONGTAISAN_2021 BIGINT,
    TONGTAISAN_2022 BIGINT,
    TAISAN_NGANHAN_2021 BIGINT,
    TAISAN_NGANHAN_2022 BIGINT,
    PHAITHU_2021 BIGINT,
    PHAITHU_2022 BIGINT,
    TAISAN_DAIHAN_2021 BIGINT,
    TAISAN_DAIHAN_2022 BIGINT,
    NO_DAIHAN_2021 BIGINT,
    NO_DAIHAN_2022 BIGINT,
    NO_NGANHAN_2021 BIGINT,
    NO_NGANHAN_2022 BIGINT,
    VONCHUSOHUU_2021 BIGINT,
    VONCHUSOHUU_2022 BIGINT,
    HANGTONKHO_2021 BIGINT,
    HANGTONKHO_2022 BIGINT,
    VAY_TCTD_KHAC CHAR(1),
    CO_HD_XNK CHAR(1),
    MAT_CAN_DOI_VON_2021 CHAR(1),
    MAT_CAN_DOI_VON_2022 CHAR(1),
    TY_SO_THANH_TOAN_2021 FLOAT,
    TY_SO_THANH_TOAN_2022 FLOAT,
    DOANH_THU_2021 BIGINT,
    DOANH_THU_2022 BIGINT,
    LOI_NHUAN_TRUOC_THUE_2021 BIGINT,
    LOI_NHUAN_TRUOC_THUE_2022 BIGINT,
    LOI_NHUAN_SAU_THUE_2021 BIGINT,
    LOI_NHUAN_SAU_THUE_2022 BIGINT,
    tong_du_no_tctd BIGINT
    );

CREATE TABLE Ledger (
    provider_tax_id CHAR(10) REFERENCES Company(tax_id),
    customer_tax_id CHAR(10) REFERENCES Company(tax_id),
    vat_number INTEGER,
    total_vat_amount BIGINT,
    PRIMARY KEY (provider_tax_id, customer_tax_id)
);

CREATE TABLE Bank (
    bank_name VARCHAR(50) PRIMARY KEY
);

CREATE TABLE CompBankBalance (
    tax_id CHAR(10) REFERENCES Company(tax_id),
    bank_name VARCHAR(50) REFERENCES Bank(bank_name),
    balance BIGINT NOT NULL,
    PRIMARY KEY (tax_id, bank_name)
);

CREATE TABLE CompNews(
    stt INT NOT NULL,
    tax_id CHAR(10) REFERENCES Company(tax_id),
    news VARCHAR(100) NOT NULL,
    news_cls VARCHAR(7) NOT NULL,
    news_date DATE,
    PRIMARY KEY (stt)
);

CREATE TABLE SalesSession(
    session_id CHAR(32) NOT NULL,
    tax_id CHAR(10) REFERENCES Company(tax_id),
    ma_pers_nv VARCHAR(10) REFERENCES Employee(empl_persnbr),
    approach_status VARCHAR(10),
    method VARCHAR(10) NOT NULL,
    product VARCHAR(50),
    session_date DATE,
    session_note VARCHAR(50),
    PRIMARY KEY (session_id)
);


-- Import data from CSV files
COPY Industry FROM '/tmp/industry.csv' DELIMITER ',' CSV HEADER;
COPY Owner FROM '/tmp/owner.csv' DELIMITER ',' CSV HEADER;
COPY Employee FROM '/tmp/employee.csv' DELIMITER ',' CSV HEADER;
COPY Company FROM '/tmp/company.csv' DELIMITER ',' CSV HEADER;
COPY Ledger FROM '/tmp/ledger.csv' DELIMITER ',' CSV HEADER;
COPY Bank FROM '/tmp/bank.csv' DELIMITER ',' CSV HEADER;
COPY CompNews FROM '/tmp/comp_news.csv' DELIMITER ',' CSV HEADER;
COPY SalesSession FROM '/tmp/sales_session.csv' DELIMITER ',' CSV HEADER;
COPY CompBankBalance FROM '/tmp/comp_bank_balance.csv' DELIMITER ',' CSV HEADER;
