CREATE TABLE shipments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    shipment_number VARCHAR(60) NOT NULL UNIQUE,
    buyer_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    shipment_date DATE NOT NULL,
    expected_delivery_date DATE NULL,
    actual_delivery_date DATE NULL,
    shipment_mode ENUM('sea', 'air', 'road', 'rail', 'courier') NOT NULL DEFAULT 'sea',
    incoterm VARCHAR(20) NULL,
    origin_port VARCHAR(120) NULL,
    destination_port VARCHAR(120) NULL,
    vessel_flight_number VARCHAR(100) NULL,
    tracking_number VARCHAR(120) NULL,
    currency_id BIGINT UNSIGNED NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    total_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_cartons DECIMAL(18,4) NOT NULL DEFAULT 0,
    gross_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_value DECIMAL(18,4) NOT NULL DEFAULT 0,
    status ENUM('draft', 'booked', 'packed', 'dispatched', 'in_transit', 'delivered', 'cancelled') NOT NULL DEFAULT 'draft',
    notes VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_shipment_company_date (company_id, shipment_date),
    CONSTRAINT fk_shipment_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_shipment_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_shipment_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_shipment_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_shipment_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE shipment_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipment_id BIGINT UNSIGNED NOT NULL,
    production_order_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NULL,
    style_id BIGINT UNSIGNED NULL,
    description VARCHAR(250) NOT NULL,
    quantity DECIMAL(18,4) NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL DEFAULT 0,
    line_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    carton_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    gross_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    country_of_origin VARCHAR(100) NULL,
    hs_code VARCHAR(40) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_shipment_item_header FOREIGN KEY (shipment_id) REFERENCES shipments (id),
    CONSTRAINT fk_shipment_item_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_shipment_item_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_shipment_item_style FOREIGN KEY (style_id) REFERENCES styles (id)
) ENGINE=InnoDB;

CREATE TABLE containers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shipment_id BIGINT UNSIGNED NOT NULL,
    container_number VARCHAR(60) NOT NULL,
    container_type ENUM('20ft', '40ft', '40hc', '45hc', 'air_uld', 'truck', 'other') NOT NULL DEFAULT '40ft',
    seal_number VARCHAR(80) NULL,
    package_count DECIMAL(18,4) NOT NULL DEFAULT 0,
    gross_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    loading_date DATE NULL,
    status ENUM('planned', 'loading', 'sealed', 'dispatched', 'arrived', 'unloaded') NOT NULL DEFAULT 'planned',
    remarks VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_shipment_container (shipment_id, container_number),
    CONSTRAINT fk_container_shipment FOREIGN KEY (shipment_id) REFERENCES shipments (id)
) ENGINE=InnoDB;

CREATE TABLE packing_lists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    shipment_id BIGINT UNSIGNED NOT NULL,
    packing_list_number VARCHAR(60) NOT NULL UNIQUE,
    packing_date DATE NOT NULL,
    package_count DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_pieces DECIMAL(18,4) NOT NULL DEFAULT 0,
    gross_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_weight DECIMAL(18,4) NOT NULL DEFAULT 0,
    dimensions VARCHAR(150) NULL,
    marks_and_numbers VARCHAR(250) NULL,
    status ENUM('draft', 'issued', 'revised', 'cancelled') NOT NULL DEFAULT 'draft',
    notes VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_packing_list_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_packing_list_shipment FOREIGN KEY (shipment_id) REFERENCES shipments (id),
    CONSTRAINT fk_packing_list_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE commercial_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    shipment_id BIGINT UNSIGNED NOT NULL,
    invoice_number VARCHAR(60) NOT NULL UNIQUE,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    buyer_id BIGINT UNSIGNED NULL,
    currency_id BIGINT UNSIGNED NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    merchandise_value DECIMAL(18,4) NOT NULL DEFAULT 0,
    freight_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    insurance_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    invoice_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    payment_terms VARCHAR(150) NULL,
    bank_details VARCHAR(500) NULL,
    status ENUM('draft', 'issued', 'paid', 'cancelled') NOT NULL DEFAULT 'draft',
    notes VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_commercial_invoice_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_commercial_invoice_shipment FOREIGN KEY (shipment_id) REFERENCES shipments (id),
    CONSTRAINT fk_commercial_invoice_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_commercial_invoice_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_commercial_invoice_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE dashboard_cache (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    cache_key VARCHAR(150) NOT NULL,
    cache_data JSON NOT NULL,
    generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    generated_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_dashboard_cache (company_id, cache_key),
    CONSTRAINT fk_dashboard_cache_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_dashboard_cache_generator FOREIGN KEY (generated_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE report_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    module VARCHAR(80) NOT NULL,
    report_type ENUM('table', 'summary', 'chart', 'document', 'export') NOT NULL DEFAULT 'table',
    description VARCHAR(500) NULL,
    definition_json JSON NOT NULL,
    layout_json JSON NULL,
    is_system BOOLEAN NOT NULL DEFAULT FALSE,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_report_template (company_id, code),
    CONSTRAINT fk_report_template_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_report_template_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE saved_filters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    resource VARCHAR(100) NOT NULL,
    name VARCHAR(150) NOT NULL,
    filter_data JSON NOT NULL,
    sort_data JSON NULL,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_user_saved_filter (user_id, resource, name),
    CONSTRAINT fk_saved_filter_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_saved_filter_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;