-- ============================================================
-- KopierSFlow ERP — 005_purchasing_imports.sql
-- Importar QUINTO en phpMyAdmin
-- ============================================================

CREATE TABLE vendors (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    vendor_number     VARCHAR(30),
    name              VARCHAR(200)   NOT NULL,
    legal_name        VARCHAR(200),
    type              ENUM('domestic','international') DEFAULT 'domestic',
    country           CHAR(3)        DEFAULT 'USA',
    address           TEXT,
    city              VARCHAR(100),
    state_province    VARCHAR(100),
    zip               VARCHAR(20),
    phone             VARCHAR(30),
    email             VARCHAR(150),
    contact_name      VARCHAR(150),
    payment_terms     TINYINT UNSIGNED DEFAULT 30,
    currency          CHAR(3)         DEFAULT 'USD',
    ein_tax_id        VARCHAR(40),
    is_fda_registered TINYINT(1)     DEFAULT 0,
    fda_registration  VARCHAR(30),
    food_safety_cert  VARCHAR(100),
    is_active         TINYINT(1)     DEFAULT 1,
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchase_orders (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id         BIGINT UNSIGNED NOT NULL,
    po_number          VARCHAR(30)    NOT NULL,
    vendor_id          BIGINT UNSIGNED NOT NULL,
    order_date         DATE           NOT NULL,
    expected_date      DATE,
    status             ENUM('draft','sent','acknowledged','partial','received','cancelled','closed') DEFAULT 'draft',
    warehouse_id       BIGINT UNSIGNED,
    incoterm           VARCHAR(10),
    currency           CHAR(3)  DEFAULT 'USD',
    exchange_rate      DECIMAL(14,6)  DEFAULT 1.0,
    subtotal           DECIMAL(18,4)  DEFAULT 0,
    tax_amount         DECIMAL(18,4)  DEFAULT 0,
    total              DECIMAL(18,4)  DEFAULT 0,
    notes              TEXT,
    is_import          TINYINT(1)     DEFAULT 0,
    import_shipment_id BIGINT UNSIGNED NULL,
    created_by         BIGINT UNSIGNED,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (vendor_id)  REFERENCES vendors(id),
    UNIQUE KEY uq_po_number (company_id, po_number),
    INDEX idx_status (company_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchase_order_lines (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    company_id        BIGINT UNSIGNED NOT NULL,
    product_id        BIGINT UNSIGNED NOT NULL,
    description       VARCHAR(500),
    quantity_ordered  DECIMAL(14,4)  NOT NULL,
    quantity_received DECIMAL(14,4)  DEFAULT 0,
    unit_cost         DECIMAL(14,4)  NOT NULL,
    line_total        DECIMAL(18,4)  NOT NULL,
    unit_of_measure   VARCHAR(20)    DEFAULT 'EA',
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_shipments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    shipment_number   VARCHAR(60)    NOT NULL,
    vendor_id         BIGINT UNSIGNED NOT NULL,
    country_of_origin CHAR(3),
    incoterm          VARCHAR(10),
    transport_mode    ENUM('sea','air','truck','rail') DEFAULT 'sea',
    carrier           VARCHAR(150),
    vessel_flight     VARCHAR(100),
    bill_of_lading    VARCHAR(100),
    airway_bill       VARCHAR(100),
    etd               DATE,
    eta               DATE,
    actual_arrival    DATE,
    port_of_loading   VARCHAR(100),
    port_of_entry     VARCHAR(100),
    status            ENUM('booked','in_transit','arrived','customs','released','delivered') DEFAULT 'booked',
    customs_broker    VARCHAR(150),
    fda_prior_notice_id VARCHAR(100),
    entry_number      VARCHAR(30),
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (vendor_id)  REFERENCES vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE containers (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id         BIGINT UNSIGNED NOT NULL,
    import_shipment_id BIGINT UNSIGNED NOT NULL,
    container_number   VARCHAR(30)    NOT NULL,
    size               ENUM('20ft','40ft','40ft_hc','reefer_20','reefer_40') NOT NULL,
    seal_number        VARCHAR(30),
    set_temp_f         DECIMAL(6,2),
    cargo_weight_lbs   DECIMAL(10,2),
    status             ENUM('booked','loaded','in_transit','discharged','available','returned') DEFAULT 'booked',
    FOREIGN KEY (company_id)         REFERENCES companies(id)         ON DELETE CASCADE,
    FOREIGN KEY (import_shipment_id) REFERENCES import_shipments(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customs_documents (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id         BIGINT UNSIGNED NOT NULL,
    import_shipment_id BIGINT UNSIGNED NOT NULL,
    doc_type           ENUM('commercial_invoice','packing_list','bill_of_lading','airway_bill','certificate_of_origin','phytosanitary','fda_prior_notice','cbp_entry','other') NOT NULL,
    doc_number         VARCHAR(100),
    issued_date        DATE,
    file_path          VARCHAR(500),
    notes              TEXT,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)         REFERENCES companies(id)        ON DELETE CASCADE,
    FOREIGN KEY (import_shipment_id) REFERENCES import_shipments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE landed_costs (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id         BIGINT UNSIGNED NOT NULL,
    import_shipment_id BIGINT UNSIGNED,
    purchase_order_id  BIGINT UNSIGNED,
    lc_number          VARCHAR(30)    NOT NULL,
    status             ENUM('draft','posted') DEFAULT 'draft',
    distribution_method ENUM('by_qty','by_weight','by_volume','by_value') DEFAULT 'by_value',
    notes              TEXT,
    journal_entry_id   BIGINT UNSIGNED,
    created_by         BIGINT UNSIGNED,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE landed_cost_charges (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    landed_cost_id BIGINT UNSIGNED NOT NULL,
    charge_type    ENUM('freight','customs_duty','broker_fee','insurance','port_fee','drayage','inspection','other') NOT NULL,
    vendor_id      BIGINT UNSIGNED,
    amount         DECIMAL(18,4)  NOT NULL,
    currency       CHAR(3)  DEFAULT 'USD',
    exchange_rate  DECIMAL(14,6)  DEFAULT 1.0,
    amount_usd     DECIMAL(18,4)  DEFAULT 0,
    account_id     BIGINT UNSIGNED,
    FOREIGN KEY (landed_cost_id) REFERENCES landed_costs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE landed_cost_lines (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    landed_cost_id  BIGINT UNSIGNED NOT NULL,
    product_id      BIGINT UNSIGNED NOT NULL,
    lot_id          BIGINT UNSIGNED,
    quantity        DECIMAL(14,4)  NOT NULL,
    product_value   DECIMAL(18,4)  NOT NULL,
    allocated_cost  DECIMAL(18,4)  DEFAULT 0,
    FOREIGN KEY (landed_cost_id) REFERENCES landed_costs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE edi_partners (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED NOT NULL,
    partner_name VARCHAR(200)   NOT NULL,
    isa_id       VARCHAR(15),
    gs_id        VARCHAR(15),
    qualifier    VARCHAR(3)  DEFAULT 'ZZ',
    protocol     ENUM('as2','sftp','ftps','van') DEFAULT 'sftp',
    is_active    TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE edi_documents (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    partner_id      BIGINT UNSIGNED NOT NULL,
    transaction_set VARCHAR(10)    NOT NULL  COMMENT '850|855|856|810|997',
    direction       ENUM('inbound','outbound') NOT NULL,
    isa_control     VARCHAR(20),
    status          ENUM('received','processing','processed','error','acknowledged') DEFAULT 'received',
    raw_content     LONGTEXT,
    parsed_data     JSON,
    reference_id    BIGINT UNSIGNED,
    error_message   TEXT,
    received_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at    TIMESTAMP NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (partner_id) REFERENCES edi_partners(id),
    INDEX idx_transaction (company_id, transaction_set, direction, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
