// Table Statistic Customer Installation
CREATE TABLE report.crm_statistic_customer_installation (
    id_customer_installation BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    statistic_date      DATE NOT NULL,
    id_unit_instalasi   BIGINT
    unit_type           VARCHAR(255),
    done_status         BIGINT,
    install_status      BIGINT,
    survey_status       BIGINT,
    pending_status      BIGINT,
    failed_status       BIGINT,
    uninstall_status    BIGINT,
    blacklist_status    BIGINT,
    block_status        BIGINT,
    unblock_status      BIGINT,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    deleted_at          TIMESTAMP
);

CREATE INDEX idx_crm_statistic_customer_installation_date
    ON report.crm_statistic_customer_installation (statistic_date);


// Table Statistic Customer Category
CREATE TABLE report.crm_statistic_customer_category (
    id_customer_category BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    statistic_date      DATE NOT NULL,
    unit_type           VARCHAR(255),
    id_unit_instalasi   BIGINT
    home_category        BIGINT,
    corp_category      BIGINT,
    educ_category       BIGINT,
    gov_category      BIGINT,
    res_category       BIGINT,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    deleted_at          TIMESTAMP
);

CREATE INDEX idx_crm_statistic_customer_category_date
    ON report.crm_statistic_customer_category (statistic_date);


// Table Statistic Buku Besar
CREATE TABLE report.finance_ledger (
    id_ledger           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    trx_date            TIMESTAMP,
    trx_id              VARCHAR(255),
    trx_description     TEXT,
    trx_account_id      BIGINT,
    trx_account_name    VARCHAR(255),
    trx_menu            VARCHAR(255),
    trx_menu_id         VARCHAR(255),
    trx_type            VARCHAR(255),
    trx_amount          DOUBLE PRECISION,
    trx_balance         DOUBLE PRECISION,
    accounting_year     INT,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    deleted_at          TIMESTAMP
);


// Table Statisstic Reseller
CREATE TABLE report.reseller_statistic_transaction_monthly (
    id_reseller_statistic_transaction_monthly BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    statistic_date          DATE NOT NULL,
    id_unit_instalasi       BIGINT,
    nama_reseller           VARCHAR(255),
    invoice_all             BIGINT,
    invoice_outstanding     BIGINT,
    invoice_settlement      BIGINT,
    invoice_omzet          DOUBLE PRECISION,
    debit_dibayar           DOUBLE PRECISION,
    debit_piutang           DOUBLE PRECISION,
    kredit_biaya_reseller   DOUBLE PRECISION,
    kredit_ppn               DOUBLE PRECISION,
    kredit_bhp_uso           DOUBLE PRECISION,
    kredit_payment_gateway      DOUBLE PRECISION,
    kredit_adm_system           DOUBLE PRECISION,
    kredit_wa_blast             DOUBLE PRECISION,
    kredit_ter                  DOUBLE PRECISION,
    kredit_payout_fee           DOUBLE PRECISION,
    kredit_withdraw             DOUBLE PRECISION,
    total_last_month_balance    DOUBLE PRECISION,
    total_debit                 DOUBLE PRECISION,
    total_kredit                DOUBLE PRECISION,
    total_current_month_balance  DOUBLE PRECISION,
    created_at                  TIMESTAMP,
    updated_at                  TIMESTAMP,
    deleted_at                  TIMESTAMP
);


CREATE TABLE report.reseller_statistic_transaction_daily (
    id_reseller_statistic_transaction_daily BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    statistic_date          DATE NOT NULL,
    id_unit_instalasi       BIGINT,
    nama_reseller           VARCHAR(255),
    invoice_all             BIGINT,
    invoice_outstanding     BIGINT,
    invoice_settlement      BIGINT,
    invoice_omzet          DOUBLE PRECISION,
    debit_dibayar           DOUBLE PRECISION,
    debit_piutang           DOUBLE PRECISION,
    kredit_biaya_reseller   DOUBLE PRECISION,
    kredit_ppn               DOUBLE PRECISION,
    kredit_bhp_uso           DOUBLE PRECISION,
    kredit_payment_gateway      DOUBLE PRECISION,
    kredit_adm_system           DOUBLE PRECISION,
    kredit_wa_blast             DOUBLE PRECISION,
    kredit_ter                  DOUBLE PRECISION,
    kredit_payout_fee           DOUBLE PRECISION,
    kredit_withdraw             DOUBLE PRECISION,
    total_last_month_balance    DOUBLE PRECISION,
    total_debit                 DOUBLE PRECISION,
    total_kredit                DOUBLE PRECISION,
    total_current_month_balance  DOUBLE PRECISION,
    created_at                  TIMESTAMP,
    updated_at                  TIMESTAMP,
    deleted_at                  TIMESTAMP
);

CREATE TABLE report.reseller_transaction_invoice (
    id_reseller_transaction_invoice BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	id_invoice				BIGINT,
    id_unit_instalasi       BIGINT,
    id_marketing            BIGINT,
    id_technician           BIGINT,
    id_product              BIGINT,
    trx_date                TIMESTAMP,
    reseller_name          VARCHAR(255),
    customer_name           VARCHAR(255),
    marketing_name          VARCHAR(255),
    technician_name         VARCHAR(255),
    product_name           VARCHAR(255),
    invoice_number          VARCHAR(255),
    invoice_date            DATE,
    dpp_value               DOUBLE PRECISION,
    ppn_value               DOUBLE PRECISION,
    bruto_value             DOUBLE PRECISION,
    bhp_uso_value           DOUBLE PRECISION,
    payment_methode         VARCHAR(255),
    adm_payment   	    	DOUBLE PRECISION,
	adm_system_value        DOUBLE PRECISION,
    paid_value              DOUBLE PRECISION,
    created_at                  TIMESTAMP,
    updated_at                  TIMESTAMP,
    deleted_at                  TIMESTAMP
);