-- Fresh, empty cPanel database only. Select the database before importing.
-- STOCKFLOW - Hệ thống quản lý kho
-- MySQL 8.x
-- Fresh-install baseline incorporating migrations 001-011. This script is


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;



CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE,
    phone VARCHAR(30),
    role ENUM('ADMIN', 'WAREHOUSE_STAFF') NOT NULL DEFAULT 'WAREHOUSE_STAFF',
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(40) NOT NULL,
  resource VARCHAR(80) NOT NULL,
  resource_id VARCHAR(80) NULL,
  method VARCHAR(10) NOT NULL,
  path VARCHAR(255) NOT NULL,
  status_code SMALLINT NOT NULL,
  ip_address VARCHAR(64) NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_logs_created_at (created_at),
  INDEX idx_audit_logs_user_id (user_id),
  INDEX idx_audit_logs_resource (resource),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE warehouses (
    warehouse_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_name VARCHAR(120) NOT NULL,
    address VARCHAR(255),
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_code VARCHAR(30) NOT NULL UNIQUE,
    category_name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE units (
    unit_id INT AUTO_INCREMENT PRIMARY KEY,
    unit_code VARCHAR(20) NOT NULL UNIQUE,
    unit_name VARCHAR(40) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(40) NOT NULL UNIQUE,
    product_name VARCHAR(160) NOT NULL,
    category_id INT NOT NULL,
    unit_id INT NOT NULL,
    minimum_stock DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    import_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    export_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_products_unit FOREIGN KEY (unit_id) REFERENCES units(unit_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_products_prices CHECK (minimum_stock >= 0 AND import_price >= 0 AND export_price >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_code VARCHAR(40) NOT NULL UNIQUE,
    supplier_name VARCHAR(160) NOT NULL,
    tax_code VARCHAR(30),
    contact_name VARCHAR(120),
    phone VARCHAR(30),
    email VARCHAR(120),
    address VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(40) NOT NULL UNIQUE,
    customer_name VARCHAR(160) NOT NULL,
    tax_code VARCHAR(30),
    contact_name VARCHAR(120),
    phone VARCHAR(30),
    email VARCHAR(120),
    address VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE import_receipts (
    import_id INT AUTO_INCREMENT PRIMARY KEY,
    import_code VARCHAR(40) NOT NULL UNIQUE,
    import_type ENUM('PURCHASE', 'FINISHED_GOODS', 'CUSTOMER_RETURN', 'OTHER') NOT NULL DEFAULT 'PURCHASE',
    warehouse_id INT NOT NULL,
    supplier_id INT,
    customer_id INT,
    source_export_id INT,
    created_by INT NOT NULL,
    delivery_person VARCHAR(120),
    invoice_number VARCHAR(60),
    source_name VARCHAR(160),
    import_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_import_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE import_details (
    import_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    import_id INT NOT NULL,
    product_id INT NOT NULL,
    document_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    import_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(16,2) GENERATED ALWAYS AS (actual_quantity * unit_price) STORED,
    note VARCHAR(255),
    CONSTRAINT fk_import_detail_receipt FOREIGN KEY (import_id) REFERENCES import_receipts(import_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_import_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_import_detail_qty CHECK (document_quantity > 0 AND actual_quantity > 0 AND unit_price >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE export_receipts (
    export_id INT AUTO_INCREMENT PRIMARY KEY,
    export_code VARCHAR(40) NOT NULL UNIQUE,
    export_type ENUM('SALE', 'SUPPLIER_RETURN', 'INTERNAL_USE', 'DISPOSAL', 'OTHER') NOT NULL DEFAULT 'SALE',
    warehouse_id INT NOT NULL,
    customer_id INT,
    supplier_id INT,
    source_import_id INT,
    created_by INT NOT NULL,
    receiver_name VARCHAR(120),
    receiver_department VARCHAR(120),
    export_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_export_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_source_import FOREIGN KEY (source_import_id) REFERENCES import_receipts(import_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_export_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE export_details (
    export_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    export_id INT NOT NULL,
    product_id INT NOT NULL,
    requested_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    export_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(16,2) GENERATED ALWAYS AS (actual_quantity * unit_price) STORED,
    note VARCHAR(255),
    CONSTRAINT fk_export_detail_receipt FOREIGN KEY (export_id) REFERENCES export_receipts(export_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_export_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_export_detail_qty CHECK (requested_quantity > 0 AND actual_quantity > 0 AND unit_price >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE import_receipts
    ADD CONSTRAINT fk_import_source_export FOREIGN KEY (source_export_id) REFERENCES export_receipts(export_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE TABLE transfer_receipts (
    transfer_id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_code VARCHAR(40) NOT NULL UNIQUE,
    from_warehouse_id INT NOT NULL,
    to_warehouse_id INT NOT NULL,
    created_by INT NOT NULL,
    carrier_name VARCHAR(120),
    transfer_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_transfer_from_warehouse FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_transfer_to_warehouse FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_transfer_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_transfer_different_warehouses CHECK (from_warehouse_id <> to_warehouse_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE transfer_details (
    transfer_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_id INT NOT NULL,
    product_id INT NOT NULL,
    requested_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255),
    CONSTRAINT fk_transfer_detail_receipt FOREIGN KEY (transfer_id) REFERENCES transfer_receipts(transfer_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_transfer_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_transfer_detail_qty CHECK (requested_quantity > 0 AND actual_quantity > 0 AND unit_price >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE stock_checks (
    check_id INT AUTO_INCREMENT PRIMARY KEY,
    check_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    created_by INT NOT NULL,
    approved_by INT,
    check_date DATE NOT NULL,
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_check_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_check_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_check_approved_by FOREIGN KEY (approved_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE stock_check_details (
    check_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    check_id INT NOT NULL,
    product_id INT NOT NULL,
    system_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    system_damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    system_obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    good_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255),
    UNIQUE KEY uq_stock_check_product (check_id, product_id),
    CONSTRAINT fk_check_detail_check FOREIGN KEY (check_id) REFERENCES stock_checks(check_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_check_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_stock_check_values CHECK (
        system_quantity >= 0 AND actual_quantity >= 0 AND
        system_damaged_quantity >= 0 AND system_obsolete_quantity >= 0 AND
        good_quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND
        system_damaged_quantity + system_obsolete_quantity <= system_quantity AND
        good_quantity + damaged_quantity + obsolete_quantity = actual_quantity
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE adjustment_receipts (
    adjustment_id INT AUTO_INCREMENT PRIMARY KEY,
    adjustment_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    check_id INT,
    created_by INT NOT NULL,
    approved_by INT,
    adjustment_date DATE NOT NULL,
    reason VARCHAR(255) NOT NULL,
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_adjustment_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_adjustment_check FOREIGN KEY (check_id) REFERENCES stock_checks(check_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_adjustment_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_adjustment_approved_by FOREIGN KEY (approved_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE adjustment_details (
    adjustment_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    adjustment_id INT NOT NULL,
    product_id INT NOT NULL,
    system_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    quantity_change DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    damaged_change DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_change DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255),
    UNIQUE KEY uq_adjustment_product (adjustment_id, product_id),
    CONSTRAINT fk_adjustment_detail_receipt FOREIGN KEY (adjustment_id) REFERENCES adjustment_receipts(adjustment_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_adjustment_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_adjustment_values CHECK (
        system_quantity >= 0 AND actual_quantity >= 0 AND
        quantity_change = actual_quantity - system_quantity AND unit_price >= 0
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE inventory (
    inventory_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_inventory_warehouse_product (warehouse_id, product_id),
    CONSTRAINT fk_inventory_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_inventory_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_inventory_values CHECK (
        quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND
        damaged_quantity + obsolete_quantity <= quantity
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE inventory_transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    created_by INT,
    transaction_type ENUM('IMPORT', 'EXPORT', 'TRANSFER_OUT', 'TRANSFER_IN', 'ADJUSTMENT', 'DAMAGE_ADJUSTMENT', 'OBSOLETE_ADJUSTMENT') NOT NULL,
    reference_type VARCHAR(40) NOT NULL,
    reference_id INT NOT NULL,
    quantity_before DECIMAL(14,2) NOT NULL,
    quantity_change DECIMAL(14,2) NOT NULL,
    quantity_after DECIMAL(14,2) NOT NULL,
    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note VARCHAR(255),
    CONSTRAINT fk_transaction_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_transaction_quantities CHECK (quantity_before >= 0 AND quantity_after >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE vat_invoices (
    invoice_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(60) NOT NULL,
    invoice_type ENUM('INPUT', 'OUTPUT') NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    supplier_id INT,
    customer_id INT,
    import_id INT,
    export_id INT,
    seller_name VARCHAR(160),
    seller_tax_code VARCHAR(30),
    buyer_name VARCHAR(160),
    buyer_tax_code VARCHAR(30),
    subtotal DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    status ENUM('DRAFT', 'ISSUED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vat_invoice_type_number (invoice_type, invoice_number),
    CONSTRAINT fk_vat_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vat_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vat_import FOREIGN KEY (import_id) REFERENCES import_receipts(import_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_vat_export FOREIGN KEY (export_id) REFERENCES export_receipts(export_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_vat_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_vat_amounts CHECK (subtotal >= 0 AND vat_rate >= 0 AND vat_rate <= 100 AND vat_amount >= 0 AND total_amount >= 0),
    CONSTRAINT chk_vat_reference CHECK (
        (invoice_type = 'INPUT' AND import_id IS NOT NULL AND export_id IS NULL) OR
        (invoice_type = 'OUTPUT' AND export_id IS NOT NULL AND import_id IS NULL)
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE vat_invoice_details (
    invoice_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    subtotal DECIMAL(16,2) NOT NULL,
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(16,2) NOT NULL,
    CONSTRAINT fk_vat_detail_invoice FOREIGN KEY (invoice_id) REFERENCES vat_invoices(invoice_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_vat_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_vat_detail_values CHECK (quantity > 0 AND unit_price >= 0 AND subtotal >= 0 AND vat_rate >= 0 AND vat_rate <= 100 AND vat_amount >= 0 AND total_amount >= 0)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Debt reporting foundation.  Monetary values stay in DECIMAL columns so
-- opening balances and manual ledger postings can be aggregated exactly in
-- SQL without JavaScript floating-point arithmetic.
CREATE TABLE debt_opening_balances (
    opening_balance_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fiscal_year SMALLINT UNSIGNED NOT NULL,
    effective_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    budget_chapter VARCHAR(20) NULL,
    budget_type VARCHAR(20) NULL,
    budget_section VARCHAR(20) NULL,
    budget_item VARCHAR(20) NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    reference_code VARCHAR(100) NULL,
    note VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    UNIQUE KEY uq_debt_opening_year_partner_category (fiscal_year, partner_key, category),
    KEY idx_debt_opening_partner_date (partner_type, customer_id, supplier_id, effective_date),
    KEY idx_debt_opening_status_date (status, effective_date),
    KEY idx_debt_opening_category (partner_type, category),
    CONSTRAINT fk_debt_opening_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_opening_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_opening_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_opening_year CHECK (fiscal_year BETWEEN 1900 AND 9999),
    CONSTRAINT chk_debt_opening_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_opening_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE debt_ledger_entries (
    debt_entry_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    entry_source ENUM('MANUAL', 'INVOICE', 'PAYMENT', 'ADJUSTMENT') NOT NULL DEFAULT 'MANUAL',
    reference_type VARCHAR(40) NULL,
    reference_id BIGINT UNSIGNED NULL,
    reference_code VARCHAR(100) NULL,
    description VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    KEY idx_debt_ledger_partner_date (partner_type, customer_id, supplier_id, entry_date),
    KEY idx_debt_ledger_status_date (status, entry_date),
    KEY idx_debt_ledger_category (partner_type, category),
    KEY idx_debt_ledger_reference (reference_type, reference_id),
    CONSTRAINT fk_debt_ledger_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_ledger_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_ledger_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_ledger_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_ledger_reference CHECK (reference_id IS NULL OR reference_type IS NOT NULL),
    CONSTRAINT chk_debt_ledger_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX idx_products_name ON products(product_name);
CREATE INDEX idx_suppliers_name ON suppliers(supplier_name);
CREATE INDEX idx_customers_name ON customers(customer_name);
CREATE INDEX idx_import_date ON import_receipts(import_date);
CREATE INDEX idx_export_date ON export_receipts(export_date);
CREATE INDEX idx_import_return_source_status ON import_receipts(source_export_id, import_type, status);
CREATE INDEX idx_export_return_source_status ON export_receipts(source_import_id, export_type, status);
CREATE INDEX idx_import_details_product ON import_details(import_id, product_id);
CREATE INDEX idx_export_details_product ON export_details(export_id, product_id);
CREATE INDEX idx_transfer_date ON transfer_receipts(transfer_date);
CREATE INDEX idx_check_date ON stock_checks(check_date);
CREATE INDEX idx_adjustment_date ON adjustment_receipts(adjustment_date);
CREATE INDEX idx_transaction_date ON inventory_transactions(transaction_date);
CREATE INDEX idx_transaction_reference ON inventory_transactions(reference_type, reference_id);
CREATE INDEX idx_vat_invoice_date ON vat_invoices(invoice_date);
CREATE INDEX idx_vat_invoice_due_date ON vat_invoices(due_date);
CREATE INDEX idx_debt_opening_effective_date ON debt_opening_balances(effective_date);
CREATE INDEX idx_debt_ledger_entry_date ON debt_ledger_entries(entry_date);
CREATE INDEX idx_debt_ledger_payment_code ON debt_ledger_entries(entry_source, status, reference_code, partner_type, customer_id, supplier_id);

CREATE OR REPLACE VIEW vw_inventory_summary AS
SELECT i.inventory_id, w.warehouse_id, w.warehouse_code, w.warehouse_name, p.product_id, p.product_code, p.product_name,
       c.category_name, u.unit_name, u.symbol AS unit_symbol, i.quantity, i.damaged_quantity, i.obsolete_quantity,
       i.quantity - i.damaged_quantity - i.obsolete_quantity AS available_quantity, p.minimum_stock,
       CASE WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity <= 0 THEN 'OUT_OF_STOCK'
            WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity <= p.minimum_stock THEN 'LOW_STOCK'
            ELSE 'IN_STOCK' END AS stock_status
FROM inventory i JOIN warehouses w ON w.warehouse_id=i.warehouse_id JOIN products p ON p.product_id=i.product_id
JOIN categories c ON c.category_id=p.category_id JOIN units u ON u.unit_id=p.unit_id;

CREATE OR REPLACE VIEW vw_stock_card AS
SELECT t.transaction_id, t.warehouse_id, w.warehouse_name, t.product_id, p.product_code, p.product_name,
       t.transaction_date, t.transaction_type, t.reference_type, t.reference_id,
       CASE WHEN t.quantity_change > 0 THEN t.quantity_change ELSE 0 END AS quantity_in,
       CASE WHEN t.quantity_change < 0 THEN ABS(t.quantity_change) ELSE 0 END AS quantity_out,
       t.quantity_before, t.quantity_after, t.note
FROM inventory_transactions t JOIN warehouses w ON w.warehouse_id=t.warehouse_id JOIN products p ON p.product_id=t.product_id;

-- Invoice-level receivable/payable reports.  VAT invoices are the source of
-- the original receivable/payable amount.  Confirmed PAYMENT ledger entries
-- are allocated to an invoice when their reference points at the VAT invoice
-- (`reference_type` + `reference_id`) or when the invoice number is entered in
-- `reference_code`.  Keeping this allocation in a view avoids duplicating
-- payment data and leaves existing debt-ledger behaviour unchanged.
CREATE OR REPLACE VIEW vw_debt_invoice_payments AS
SELECT linked.invoice_id,
       linked.partner_type,
       linked.customer_id,
       linked.supplier_id,
       CAST(COALESCE(SUM(linked.paid_amount), 0.00) AS DECIMAL(20,2)) AS paid_amount
FROM (
    SELECT l.debt_entry_id,
           v.invoice_id,
           l.partner_type,
           l.customer_id,
           l.supplier_id,
           CASE WHEN l.partner_type = 'CUSTOMER' THEN l.credit_amount ELSE l.debit_amount END AS paid_amount
    FROM debt_ledger_entries l
    JOIN vat_invoices v
      ON v.invoice_id = l.reference_id
     AND ((l.partner_type = 'CUSTOMER' AND v.invoice_type = 'OUTPUT' AND v.customer_id = l.customer_id)
       OR (l.partner_type = 'SUPPLIER' AND v.invoice_type = 'INPUT' AND v.supplier_id = l.supplier_id))
    WHERE l.status = 'CONFIRMED'
      AND l.entry_source = 'PAYMENT'
      AND l.reference_id IS NOT NULL
      AND UPPER(TRIM(COALESCE(l.reference_type, ''))) IN ('VAT_INVOICE', 'VAT_INVOICES', 'INVOICE', 'PAYMENT')
    UNION
    SELECT l.debt_entry_id,
           v.invoice_id,
           l.partner_type,
           l.customer_id,
           l.supplier_id,
           CASE WHEN l.partner_type = 'CUSTOMER' THEN l.credit_amount ELSE l.debit_amount END AS paid_amount
    FROM debt_ledger_entries l
    JOIN vat_invoices v
      ON v.invoice_number = l.reference_code
     AND ((l.partner_type = 'CUSTOMER' AND v.invoice_type = 'OUTPUT' AND v.customer_id = l.customer_id)
       OR (l.partner_type = 'SUPPLIER' AND v.invoice_type = 'INPUT' AND v.supplier_id = l.supplier_id))
    WHERE l.status = 'CONFIRMED'
      AND l.entry_source = 'PAYMENT'
      AND l.reference_code IS NOT NULL
      AND l.reference_id IS NULL
      -- A payment entered from the debt screen may carry only the invoice
      -- number in reference_code; the exact partner + number match is enough
      -- to allocate it safely because invoice numbers are unique per type.
) linked
GROUP BY linked.invoice_id, linked.partner_type, linked.customer_id, linked.supplier_id;

CREATE OR REPLACE VIEW vw_debt_receivables_by_invoice AS
SELECT v.invoice_id,
       v.invoice_type,
       v.invoice_date,
       v.invoice_date AS document_date,
       v.invoice_number,
       v.invoice_number AS document_number,
       c.customer_id AS partner_id,
       c.customer_code AS partner_code,
       c.customer_name AS partner_name,
       c.customer_id AS customer_id,
       c.customer_code AS customer_code,
       c.customer_name AS customer_name,
       c.customer_code AS object_code,
       c.customer_name AS object_name,
       COALESCE(NULLIF(er.reason, ''), NULLIF(er.note, ''), CONCAT('Hóa đơn bán hàng ', v.invoice_number)) AS description,
       er.export_id AS source_receipt_id,
       er.export_code AS source_receipt_number,
       CAST(v.total_amount AS DECIMAL(20,2)) AS receivable_amount,
       CAST(v.total_amount AS DECIMAL(20,2)) AS amount,
       CAST(COALESCE(p.paid_amount, 0.00) AS DECIMAL(20,2)) AS paid_amount,
       CAST(COALESCE(p.paid_amount, 0.00) AS DECIMAL(20,2)) AS paid_receivable,
       CAST(GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) AS DECIMAL(20,2)) AS remaining_amount,
       CAST(GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) AS DECIMAL(20,2)) AS remaining_receivable,
       v.due_date,
       v.status AS invoice_status,
       CASE
           WHEN GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) <= 0 THEN 'PAID'
           WHEN v.due_date IS NOT NULL AND v.due_date < CURRENT_DATE THEN 'OVERDUE'
           WHEN COALESCE(p.paid_amount, 0.00) > 0 THEN 'PARTIALLY_PAID'
           ELSE 'UNPAID'
       END AS status,
       CASE
           WHEN GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) <= 0 THEN 'Đã thu đủ'
           WHEN v.due_date IS NOT NULL AND v.due_date < CURRENT_DATE THEN 'Quá hạn'
           WHEN COALESCE(p.paid_amount, 0.00) > 0 THEN 'Thu một phần'
           ELSE 'Chưa thu'
       END AS status_label
FROM vat_invoices v
JOIN customers c ON c.customer_id = v.customer_id
LEFT JOIN export_receipts er ON er.export_id = v.export_id
LEFT JOIN vw_debt_invoice_payments p
       ON p.invoice_id = v.invoice_id AND p.partner_type = 'CUSTOMER' AND p.customer_id = v.customer_id
WHERE v.invoice_type = 'OUTPUT' AND v.status = 'ISSUED';

CREATE OR REPLACE VIEW vw_debt_payables_by_invoice AS
SELECT v.invoice_id,
       v.invoice_type,
       v.invoice_date,
       v.invoice_date AS document_date,
       v.invoice_number,
       v.invoice_number AS document_number,
       s.supplier_id AS partner_id,
       s.supplier_code AS partner_code,
       s.supplier_name AS partner_name,
       s.supplier_id AS supplier_id,
       s.supplier_code AS supplier_code,
       s.supplier_name AS supplier_name,
       s.supplier_code AS object_code,
       s.supplier_name AS object_name,
       COALESCE(NULLIF(ir.reason, ''), NULLIF(ir.note, ''), CONCAT('Hóa đơn mua hàng ', v.invoice_number)) AS description,
       ir.import_id AS source_receipt_id,
       ir.import_code AS source_receipt_number,
       CAST(v.total_amount AS DECIMAL(20,2)) AS payable_amount,
       CAST(v.total_amount AS DECIMAL(20,2)) AS amount,
       CAST(COALESCE(p.paid_amount, 0.00) AS DECIMAL(20,2)) AS paid_amount,
       CAST(COALESCE(p.paid_amount, 0.00) AS DECIMAL(20,2)) AS paid_payable,
       CAST(GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) AS DECIMAL(20,2)) AS remaining_amount,
       CAST(GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) AS DECIMAL(20,2)) AS remaining_payable,
       v.due_date,
       v.status AS invoice_status,
       CASE
           WHEN GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) <= 0 THEN 'PAID'
           WHEN v.due_date IS NOT NULL AND v.due_date < CURRENT_DATE THEN 'OVERDUE'
           WHEN COALESCE(p.paid_amount, 0.00) > 0 THEN 'PARTIALLY_PAID'
           ELSE 'UNPAID'
       END AS status,
       CASE
           WHEN GREATEST(v.total_amount - COALESCE(p.paid_amount, 0.00), 0.00) <= 0 THEN 'Đã trả đủ'
           WHEN v.due_date IS NOT NULL AND v.due_date < CURRENT_DATE THEN 'Quá hạn'
           WHEN COALESCE(p.paid_amount, 0.00) > 0 THEN 'Trả một phần'
           ELSE 'Chưa trả'
       END AS status_label
FROM vat_invoices v
JOIN suppliers s ON s.supplier_id = v.supplier_id
LEFT JOIN import_receipts ir ON ir.import_id = v.import_id
LEFT JOIN vw_debt_invoice_payments p
       ON p.invoice_id = v.invoice_id AND p.partner_type = 'SUPPLIER' AND p.supplier_id = v.supplier_id
WHERE v.invoice_type = 'INPUT' AND v.status = 'ISSUED';

-- Short aliases are kept for integrations and SQL users that use the report
-- names directly.  The canonical definitions above retain the debt prefix.
CREATE OR REPLACE VIEW vw_receivables_by_invoice AS SELECT * FROM vw_debt_receivables_by_invoice;
CREATE OR REPLACE VIEW vw_payables_by_invoice AS SELECT * FROM vw_debt_payables_by_invoice;

SET FOREIGN_KEY_CHECKS = 1;

-- Demonstration data for a fresh, empty cPanel database.
-- StockFlow fresh-install demo data. Run after database/schema.sql.
-- Dates use the Vietnam calendar. Set @seed_date before import to replay a fixed day.
-- IDs below are deterministic for a freshly created schema; this seed is not idempotent.
-- Existing demo login passwords: admin / Admin@123; warehouse01 and warehouse02 / Staff@123.
-- Schema.sql owns every table and view; this file supplies demo records only.
SET NAMES utf8mb4;
SET time_zone = '+07:00';
SET @seed_date = COALESCE(@seed_date, CURRENT_DATE());
START TRANSACTION;

INSERT INTO categories (category_code, category_name, description) VALUES
('CAT-COMP', 'Linh kiện máy tính', 'Thiết bị và phụ kiện máy tính'),
('CAT-NET', 'Thiết bị mạng', 'Kết nối mạng'),
('CAT-ELEC', 'Thiết bị điện', 'Điện dân dụng'),
('CAT-STORE', 'Lưu trữ', 'Ổ đĩa và lưu trữ'),
('CAT-OFFICE', 'Văn phòng', 'Thiết bị văn phòng'),
('CAT-SEC', 'An ninh', 'Camera và giám sát'),
('CAT-CABLE', 'Dây cáp', 'Cáp tín hiệu và nguồn'),
('CAT-TOOLS', 'Công cụ', 'Dụng cụ bảo trì');

INSERT INTO units (unit_code, unit_name, symbol, description) VALUES
('PCS', 'Cái', 'cái', 'Đơn vị chiếc'),
('BOX', 'Hộp', 'hộp', 'Đơn vị hộp'),
('ROLL', 'Cuộn', 'cuộn', 'Đơn vị cuộn'),
('SET', 'Bộ', 'bộ', 'Đơn vị bộ'),
('MTR', 'Mét', 'm', 'Đơn vị chiều dài'),
('PAIR', 'Đôi', 'đôi', 'Đơn vị theo cặp'),
('PACK', 'Gói', 'gói', 'Đơn vị theo gói'),
('CASE', 'Thùng', 'thùng', 'Đơn vị theo thùng');

INSERT INTO warehouses (warehouse_code, warehouse_name, address, description) VALUES
('KHO01', 'Kho trung tâm', '12 Nguyễn Trãi, Quận 1, TP.HCM', 'Kho bán hàng chính'),
('KHO02', 'Kho miền Đông', '88 Điện Biên Phủ, Bình Thạnh, TP.HCM', 'Kho giao hàng khu vực'),
('KHO03', 'Kho cách ly', '20 Lê Lợi, Quận 1, TP.HCM', 'Hàng trả và chờ xử lý'),
('KHO04', 'Kho miền Tây', '15 Cách Mạng Tháng Tám, Cần Thơ', 'Kho giao hàng khu vực'),
('KHO05', 'Kho dự án', '31 Trần Phú, Đà Nẵng', 'Kho dành cho dự án');

INSERT INTO users (username, password_hash, full_name, email, role, status) VALUES
('admin', '$2b$10$Rzp7bvB1osDykg4gdeFB5OmAfYbBmzoypoQRq4p7Ku3GqrHKbnUo2', 'Quản trị viên', 'admin@stockflow.local', 'ADMIN', 'ACTIVE'),
('warehouse01', '$2b$10$jJFkiRdYbQqNePHKbqGVXeJGuh63BSjfm4EQCcO1cXAyHJH4rJD9q', 'Nguyễn Văn Kho', 'warehouse01@stockflow.local', 'WAREHOUSE_STAFF', 'ACTIVE'),
('warehouse02', '$2b$10$VLkq8Ox1qzdbB6sPccCPLe2Wm4jGt4IFt/m5bR64VF5SILXsbAE3q', 'Trần Thị Kho', 'warehouse02@stockflow.local', 'WAREHOUSE_STAFF', 'ACTIVE'),
('manager01', '$2b$10$Rzp7bvB1osDykg4gdeFB5OmAfYbBmzoypoQRq4p7Ku3GqrHKbnUo2', 'Lê Minh Quản', 'manager01@stockflow.local', 'ADMIN', 'ACTIVE'),
('warehouse03', '$2b$10$jJFkiRdYbQqNePHKbqGVXeJGuh63BSjfm4EQCcO1cXAyHJH4rJD9q', 'Phạm Thu Hà', 'warehouse03@stockflow.local', 'WAREHOUSE_STAFF', 'ACTIVE'),
('warehouse_old', '$2b$10$VLkq8Ox1qzdbB6sPccCPLe2Wm4jGt4IFt/m5bR64VF5SILXsbAE3q', 'Nhân viên cũ', 'warehouse_old@stockflow.local', 'WAREHOUSE_STAFF', 'INACTIVE');

INSERT INTO suppliers (supplier_code, supplier_name, tax_code, contact_name, phone, email, address) VALUES
('NCC001', 'Công ty Công nghệ Sao Việt', '0301000001', 'Bộ phận kinh doanh', '0281000001', 'sales1@supplier.local', 'TP.HCM'),
('NCC002', 'Nhà phân phối Phú Quý', '0102000002', 'Bộ phận kinh doanh', '0281000002', 'sales2@supplier.local', 'Hà Nội'),
('NCC003', 'Thiết bị điện Đông Á', '3703000003', 'Bộ phận kinh doanh', '0281000003', 'sales3@supplier.local', 'Bình Dương'),
('NCC004', 'Công ty Mạng Toàn Cầu', '0305000004', 'Bộ phận kinh doanh', '0281000004', 'sales4@supplier.local', 'Đà Nẵng'),
('NCC005', 'Điện tử Đại Nam', '0406000005', 'Bộ phận kinh doanh', '0281000005', 'sales5@supplier.local', 'Cần Thơ'),
('NCC006', 'Công ty Văn phòng Hòa Bình', '1807000006', 'Bộ phận kinh doanh', '0281000006', 'sales6@supplier.local', 'Hải Phòng');

INSERT INTO customers (customer_code, customer_name, tax_code, contact_name, phone, email, address) VALUES
('KH001', 'Công ty Minh Long', '0304000001', 'Bộ phận mua hàng', '0901000001', 'contact1@customer.local', 'TP.HCM'),
('KH002', 'Công ty An Bình', '0304000002', 'Bộ phận mua hàng', '0901000002', 'contact2@customer.local', 'Đồng Nai'),
('KH003', 'Đại lý Hoàng Gia', '0304000003', 'Bộ phận mua hàng', '0901000003', 'contact3@customer.local', 'Bình Dương'),
('KH004', 'Công ty Tân Phát', '0304000004', 'Bộ phận mua hàng', '0901000004', 'contact4@customer.local', 'Long An'),
('KH005', 'Siêu thị Công nghệ Miền Tây', '1804000005', 'Bộ phận mua hàng', '0901000005', 'contact5@customer.local', 'Cần Thơ'),
('KH006', 'Trường Đại học Hải Đăng', '0404000006', 'Bộ phận mua hàng', '0901000006', 'contact6@customer.local', 'Đà Nẵng'),
('KH007', 'Cửa hàng Nhật Minh', '0304000007', 'Bộ phận mua hàng', '0901000007', 'contact7@customer.local', 'TP.HCM');

INSERT INTO products (product_code, product_name, category_id, unit_id, minimum_stock, import_price, export_price, description) VALUES
('SP001', 'Chuột Logitech G102', 1, 1, 12, 300000, 350000, 'Hàng mẫu Chuột Logitech G102'),
('SP002', 'Bàn phím Logitech K120', 1, 1, 10, 200000, 240000, 'Hàng mẫu Bàn phím Logitech K120'),
('SP003', 'RAM Kingston 16GB', 1, 1, 10, 800000, 900000, 'Hàng mẫu RAM Kingston 16GB'),
('SP004', 'SSD Samsung 500GB', 4, 1, 8, 1000000, 1150000, 'Hàng mẫu SSD Samsung 500GB'),
('SP005', 'Bộ phát WiFi TP-Link', 2, 1, 7, 600000, 750000, 'Hàng mẫu Bộ phát WiFi TP-Link'),
('SP006', 'Dây điện Cadivi 4.0', 3, 3, 20, 45000, 55000, 'Hàng mẫu Dây điện Cadivi 4.0'),
('SP007', 'Router TP-Link AX23', 2, 1, 6, 850000, 1050000, 'Hàng mẫu Router TP-Link AX23'),
('SP008', 'Switch TP-Link 8 cổng', 2, 1, 6, 420000, 520000, 'Hàng mẫu Switch TP-Link 8 cổng'),
('SP009', 'Ổ cắm điện 5 mét', 3, 1, 12, 120000, 150000, 'Hàng mẫu Ổ cắm điện 5 mét'),
('SP010', 'Hộp đầu nối điện', 3, 2, 10, 80000, 100000, 'Hàng mẫu Hộp đầu nối điện'),
('SP011', 'Chuột Logitech M331', 1, 1, 10, 280000, 340000, 'Hàng mẫu Chuột Logitech M331'),
('SP012', 'Bàn phím cơ Akko 3087', 1, 1, 5, 1150000, 1390000, 'Hàng mẫu Bàn phím cơ Akko 3087'),
('SP013', 'RAM Corsair 32GB', 1, 1, 6, 1550000, 1850000, 'Hàng mẫu RAM Corsair 32GB'),
('SP014', 'SSD WD Blue 1TB', 4, 1, 5, 1650000, 1990000, 'Hàng mẫu SSD WD Blue 1TB'),
('SP015', 'Ổ cứng HDD Seagate 2TB', 4, 1, 5, 1350000, 1690000, 'Hàng mẫu Ổ cứng HDD Seagate 2TB'),
('SP016', 'USB SanDisk 64GB', 4, 1, 15, 180000, 230000, 'Hàng mẫu USB SanDisk 64GB'),
('SP017', 'Thẻ nhớ Samsung 128GB', 4, 1, 12, 290000, 360000, 'Hàng mẫu Thẻ nhớ Samsung 128GB'),
('SP018', 'Router MikroTik hEX', 2, 1, 5, 1250000, 1590000, 'Hàng mẫu Router MikroTik hEX'),
('SP019', 'Switch Cisco 24 cổng', 2, 1, 4, 2800000, 3400000, 'Hàng mẫu Switch Cisco 24 cổng'),
('SP020', 'Bộ mở rộng WiFi', 2, 1, 8, 390000, 490000, 'Hàng mẫu Bộ mở rộng WiFi'),
('SP021', 'Camera IP Hikvision 2MP', 6, 1, 6, 890000, 1120000, 'Hàng mẫu Camera IP Hikvision 2MP'),
('SP022', 'Đầu ghi hình 8 kênh', 6, 1, 4, 2350000, 2900000, 'Hàng mẫu Đầu ghi hình 8 kênh'),
('SP023', 'Ổ cứng camera 4TB', 6, 1, 4, 2450000, 3000000, 'Hàng mẫu Ổ cứng camera 4TB'),
('SP024', 'Máy in Canon LBP2900', 5, 1, 4, 4100000, 4800000, 'Hàng mẫu Máy in Canon LBP2900'),
('SP025', 'Mực in Canon 303', 5, 2, 8, 950000, 1150000, 'Hàng mẫu Mực in Canon 303'),
('SP026', 'Máy quét Epson V39', 5, 1, 3, 2550000, 3100000, 'Hàng mẫu Máy quét Epson V39'),
('SP027', 'Bộ lưu điện APC 650VA', 3, 1, 5, 1350000, 1650000, 'Hàng mẫu Bộ lưu điện APC 650VA'),
('SP028', 'Dây mạng Cat6', 2, 3, 15, 750000, 950000, 'Hàng mẫu Dây mạng Cat6'),
('SP029', 'Ổ cắm chống sét', 3, 1, 8, 220000, 280000, 'Hàng mẫu Ổ cắm chống sét'),
('SP030', 'Bộ phụ kiện vệ sinh máy tính', 5, 4, 10, 90000, 125000, 'Hàng mẫu Bộ phụ kiện vệ sinh máy tính'),
('SP031', 'Cáp HDMI 2 mét', 1, 1, 12, 125000, 165000, 'Hàng mẫu Cáp HDMI 2 mét'),
('SP032', 'Adapter USB-C đa cổng', 1, 1, 6, 490000, 620000, 'Hàng mẫu Adapter USB-C đa cổng'),
('SP033', 'Bộ chuột bàn phím không dây', 1, 4, 8, 520000, 650000, 'Hàng mẫu Bộ chuột bàn phím không dây'),
('SP034', 'Pin dự phòng 10000mAh', 3, 1, 8, 410000, 510000, 'Hàng mẫu Pin dự phòng 10000mAh'),
('SP035', 'Camera WiFi trong nhà', 6, 1, 6, 730000, 920000, 'Hàng mẫu Camera WiFi trong nhà'),
('SP036', 'Dây điện Cadivi 2.5', 3, 3, 15, 350000, 430000, 'Hàng mẫu Dây điện Cadivi 2.5');

INSERT INTO import_receipts (import_id, import_code, import_type, warehouse_id, supplier_id, customer_id, source_export_id, created_by, delivery_person, invoice_number, source_name, import_date, reason, status) VALUES
(1, 'PN000001', 'PURCHASE', 1, 1, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0001', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -55 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(2, 'PN000002', 'PURCHASE', 1, 2, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0002', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -51 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(3, 'PN000003', 'PURCHASE', 2, 3, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0003', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -47 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(4, 'PN000004', 'PURCHASE', 1, 4, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0004', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -43 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(5, 'PN000005', 'PURCHASE', 4, 5, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0005', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -39 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(6, 'PN000006', 'PURCHASE', 5, 6, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0006', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -34 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(7, 'PN000007', 'PURCHASE', 1, 1, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0007', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -29 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(8, 'PN000008', 'PURCHASE', 2, 2, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0008', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -25 DAY), 'Dữ liệu mẫu: PURCHASE', 'CONFIRMED'),
(9, 'PNTP000001', 'FINISHED_GOODS', 1, NULL, NULL, NULL, 2, 'Nhân viên giao hàng', NULL, 'Bộ phận nội bộ', DATE_ADD(@seed_date, INTERVAL -22 DAY), 'Dữ liệu mẫu: FINISHED_GOODS', 'CONFIRMED'),
(10, 'PNKH000001', 'OTHER', 4, NULL, NULL, NULL, 2, 'Nhân viên giao hàng', NULL, 'Bộ phận nội bộ', DATE_ADD(@seed_date, INTERVAL -19 DAY), 'Dữ liệu mẫu: OTHER', 'CONFIRMED'),
(11, 'PN000009', 'PURCHASE', 5, 4, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0011', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -4 DAY), 'Dữ liệu mẫu: PURCHASE', 'DRAFT'),
(12, 'PN000010', 'PURCHASE', 2, 3, NULL, NULL, 2, 'Nhân viên giao hàng', 'CT-0012', 'Đối tác', DATE_ADD(@seed_date, INTERVAL -14 DAY), 'Dữ liệu mẫu: PURCHASE', 'CANCELLED');

INSERT INTO export_receipts (export_id, export_code, export_type, warehouse_id, customer_id, supplier_id, source_import_id, created_by, receiver_name, export_date, reason, status) VALUES
(1, 'PX000001', 'SALE', 1, 1, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -20 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(2, 'PX000002', 'SALE', 1, 2, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -18 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(3, 'PX000003', 'SALE', 4, 3, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -16 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(4, 'PX000004', 'SALE', 2, 4, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -14 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(5, 'PX000005', 'SALE', 1, 5, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -11 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(6, 'PX000006', 'SALE', 5, 6, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -9 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(7, 'PX000007', 'SALE', 2, 7, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -7 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(8, 'PX000008', 'SALE', 1, 1, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -5 DAY), 'Dữ liệu mẫu: SALE', 'CONFIRMED'),
(9, 'PXTR000001', 'SUPPLIER_RETURN', 1, NULL, 2, 2, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -6 DAY), 'Dữ liệu mẫu: SUPPLIER_RETURN', 'CONFIRMED'),
(10, 'PXNV000001', 'INTERNAL_USE', 5, NULL, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -5 DAY), 'Dữ liệu mẫu: INTERNAL_USE', 'CONFIRMED'),
(11, 'PXHU000001', 'DISPOSAL', 2, NULL, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -3 DAY), 'Dữ liệu mẫu: DISPOSAL', 'CONFIRMED'),
(12, 'PXKH000001', 'OTHER', 1, NULL, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -2 DAY), 'Dữ liệu mẫu: OTHER', 'CONFIRMED'),
(13, 'PX000009', 'SALE', 1, 3, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -1 DAY), 'Dữ liệu mẫu: SALE', 'DRAFT'),
(14, 'PX000010', 'SALE', 2, 5, NULL, NULL, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -12 DAY), 'Dữ liệu mẫu: SALE', 'CANCELLED'),
(15, 'PXTR000002', 'SUPPLIER_RETURN', 1, NULL, 1, 1, 3, 'Người nhận hàng', DATE_ADD(@seed_date, INTERVAL -1 DAY), 'Dữ liệu mẫu: SUPPLIER_RETURN', 'DRAFT');

INSERT INTO export_details (export_id, product_id, requested_quantity, actual_quantity, unit_price, export_price, note) VALUES
(1, 1, 12, 12, 350000, 350000, NULL),
(1, 2, 6, 6, 240000, 240000, NULL),
(1, 11, 5, 5, 340000, 340000, NULL),
(2, 3, 7, 7, 900000, 900000, NULL),
(2, 4, 4, 4, 1150000, 1150000, NULL),
(2, 13, 2, 2, 1850000, 1850000, NULL),
(3, 21, 5, 5, 1120000, 1120000, NULL),
(3, 22, 2, 2, 2900000, 2900000, NULL),
(3, 35, 4, 4, 920000, 920000, NULL),
(4, 6, 10, 10, 55000, 55000, NULL),
(4, 9, 6, 6, 150000, 150000, NULL),
(4, 10, 5, 5, 100000, 100000, NULL),
(5, 5, 6, 6, 750000, 750000, NULL),
(5, 7, 3, 3, 1050000, 1050000, NULL),
(5, 8, 4, 4, 520000, 520000, NULL),
(6, 24, 2, 2, 4800000, 4800000, NULL),
(6, 25, 5, 5, 1150000, 1150000, NULL),
(6, 26, 2, 2, 3100000, 3100000, NULL),
(7, 29, 5, 5, 280000, 280000, NULL),
(7, 33, 3, 3, 650000, 650000, NULL),
(7, 34, 4, 4, 510000, 510000, NULL),
(8, 15, 3, 3, 1690000, 1690000, NULL),
(8, 16, 8, 8, 230000, 230000, NULL),
(8, 17, 5, 5, 360000, 360000, NULL),
(9, 3, 2, 2, 800000, 900000, NULL),
(9, 4, 1, 1, 1000000, 1150000, NULL),
(10, 30, 3, 3, 90000, 125000, NULL),
(11, 9, 2, 2, 120000, 150000, NULL),
(12, 31, 1, 1, 125000, 165000, NULL),
(13, 1, 3, 3, 350000, 350000, NULL),
(13, 2, 2, 2, 240000, 240000, NULL),
(14, 29, 2, 2, 280000, 280000, NULL),
(15, 1, 1, 1, 300000, 350000, NULL);

INSERT INTO import_receipts (import_id, import_code, import_type, warehouse_id, supplier_id, customer_id, source_export_id, created_by, delivery_person, invoice_number, source_name, import_date, reason, status) VALUES
(13, 'PNTR000001', 'CUSTOMER_RETURN', 3, NULL, 1, 1, 2, 'Nhân viên giao hàng', NULL, 'Đối tác', DATE_ADD(@seed_date, INTERVAL -8 DAY), 'Dữ liệu mẫu: CUSTOMER_RETURN', 'CONFIRMED'),
(14, 'PNTR000002', 'CUSTOMER_RETURN', 3, NULL, 3, 3, 2, 'Nhân viên giao hàng', NULL, 'Đối tác', DATE_ADD(@seed_date, INTERVAL -2 DAY), 'Dữ liệu mẫu: CUSTOMER_RETURN', 'DRAFT');

INSERT INTO import_details (import_id, product_id, document_quantity, actual_quantity, unit_price, import_price, note) VALUES
(1, 1, 70, 70, 300000, 300000, NULL),
(1, 2, 45, 45, 200000, 200000, NULL),
(1, 11, 30, 30, 280000, 280000, NULL),
(1, 12, 12, 12, 1150000, 1150000, NULL),
(2, 3, 35, 35, 800000, 800000, NULL),
(2, 4, 25, 25, 1000000, 1000000, NULL),
(2, 13, 18, 18, 1550000, 1550000, NULL),
(2, 14, 16, 16, 1650000, 1650000, NULL),
(3, 6, 75, 75, 45000, 45000, NULL),
(3, 9, 40, 40, 120000, 120000, NULL),
(3, 10, 35, 35, 80000, 80000, NULL),
(3, 27, 20, 20, 1350000, 1350000, NULL),
(4, 5, 30, 30, 600000, 600000, NULL),
(4, 7, 22, 22, 850000, 850000, NULL),
(4, 8, 24, 24, 420000, 420000, NULL),
(4, 18, 18, 18, 1250000, 1250000, NULL),
(4, 19, 10, 10, 2800000, 2800000, NULL),
(4, 20, 20, 20, 390000, 390000, NULL),
(4, 28, 30, 30, 750000, 750000, NULL),
(5, 21, 20, 20, 890000, 890000, NULL),
(5, 22, 12, 12, 2350000, 2350000, NULL),
(5, 23, 12, 12, 2450000, 2450000, NULL),
(5, 35, 20, 20, 730000, 730000, NULL),
(6, 24, 12, 12, 4100000, 4100000, NULL),
(6, 25, 24, 24, 950000, 950000, NULL),
(6, 26, 10, 10, 2550000, 2550000, NULL),
(6, 30, 40, 40, 90000, 90000, NULL),
(7, 15, 20, 20, 1350000, 1350000, NULL),
(7, 16, 55, 55, 180000, 180000, NULL),
(7, 17, 45, 45, 290000, 290000, NULL),
(7, 31, 40, 40, 125000, 125000, NULL),
(7, 32, 20, 20, 490000, 490000, NULL),
(8, 29, 35, 35, 220000, 220000, NULL),
(8, 33, 22, 22, 520000, 520000, NULL),
(8, 34, 25, 25, 410000, 410000, NULL),
(8, 36, 40, 40, 350000, 350000, NULL),
(9, 5, 12, 12, 600000, 600000, NULL),
(9, 33, 8, 8, 520000, 520000, NULL),
(10, 8, 8, 8, 420000, 420000, NULL),
(10, 16, 12, 12, 180000, 180000, NULL),
(11, 7, 10, 10, 850000, 850000, NULL),
(11, 20, 15, 15, 390000, 390000, NULL),
(12, 9, 8, 8, 120000, 120000, NULL),
(13, 1, 2, 2, 300000, 300000, NULL),
(14, 21, 1, 1, 890000, 890000, NULL);

INSERT INTO transfer_receipts (transfer_code, from_warehouse_id, to_warehouse_id, created_by, carrier_name, transfer_date, reason, status) VALUES
('CK000001', 1, 2, 2, 'Nguyễn Văn T', DATE_ADD(@seed_date, INTERVAL -13 DAY), 'Điều phối hàng mẫu', 'CONFIRMED'),
('CK000002', 1, 5, 2, 'Nguyễn Văn T', DATE_ADD(@seed_date, INTERVAL -10 DAY), 'Điều phối hàng mẫu', 'CONFIRMED'),
('CK000003', 2, 4, 2, 'Nguyễn Văn T', DATE_ADD(@seed_date, INTERVAL -6 DAY), 'Điều phối hàng mẫu', 'CONFIRMED'),
('CK000004', 4, 1, 2, 'Nguyễn Văn T', DATE_ADD(@seed_date, INTERVAL -1 DAY), 'Điều phối hàng mẫu', 'DRAFT'),
('CK000005', 5, 2, 2, 'Nguyễn Văn T', DATE_ADD(@seed_date, INTERVAL -4 DAY), 'Điều phối hàng mẫu', 'CANCELLED');

INSERT INTO transfer_details (transfer_id, product_id, requested_quantity, actual_quantity, unit_price) VALUES
(1, 1, 8, 8, 300000),
(1, 2, 5, 5, 200000),
(1, 5, 6, 6, 600000),
(2, 3, 4, 4, 800000),
(2, 16, 10, 10, 180000),
(2, 31, 6, 6, 125000),
(3, 6, 8, 8, 45000),
(3, 29, 5, 5, 220000),
(4, 21, 2, 2, 890000),
(5, 25, 2, 2, 950000);

INSERT INTO stock_checks (check_code, warehouse_id, created_by, approved_by, check_date, note, status) VALUES
('KK000001', 1, 2, 1, DATE_ADD(@seed_date, INTERVAL -4 DAY), 'Kiểm kê KK000001', 'CONFIRMED'),
('KK000002', 2, 2, 1, DATE_ADD(@seed_date, INTERVAL -2 DAY), 'Kiểm kê KK000002', 'CONFIRMED'),
('KK000003', 4, 2, NULL, DATE_ADD(@seed_date, INTERVAL -1 DAY), 'Kiểm kê KK000003', 'DRAFT'),
('KK000004', 5, 2, NULL, DATE_ADD(@seed_date, INTERVAL -7 DAY), 'Kiểm kê KK000004', 'CANCELLED');

INSERT INTO stock_check_details (check_id, product_id, system_quantity, actual_quantity, system_damaged_quantity, system_obsolete_quantity, good_quantity, damaged_quantity, obsolete_quantity, note) VALUES
(1, 1, 50, 49, 0, 0, 48, 1, 0, 'Phân loại hàng theo tình trạng'),
(1, 2, 34, 34, 0, 0, 34, 0, 0, 'Phân loại hàng theo tình trạng'),
(1, 3, 22, 22, 0, 0, 22, 0, 0, 'Phân loại hàng theo tình trạng'),
(2, 6, 57, 57, 0, 0, 55, 2, 0, 'Phân loại hàng theo tình trạng'),
(2, 9, 32, 32, 0, 0, 31, 0, 1, 'Phân loại hàng theo tình trạng'),
(2, 29, 25, 26, 0, 0, 26, 0, 0, 'Phân loại hàng theo tình trạng'),
(3, 21, 15, 15, 0, 0, 15, 0, 0, 'Phân loại hàng theo tình trạng'),
(3, 35, 16, 16, 0, 0, 16, 0, 0, 'Phân loại hàng theo tình trạng'),
(4, 24, 10, 10, 0, 0, 10, 0, 0, 'Phân loại hàng theo tình trạng');

INSERT INTO adjustment_receipts (adjustment_code, warehouse_id, check_id, created_by, approved_by, adjustment_date, reason, status) VALUES
('DC000001', 1, 1, 2, 1, DATE_ADD(@seed_date, INTERVAL -3 DAY), 'Điều chỉnh DC000001', 'CONFIRMED'),
('DC000002', 2, NULL, 2, 1, DATE_ADD(@seed_date, INTERVAL -1 DAY), 'Điều chỉnh DC000002', 'CONFIRMED'),
('DC000003', 5, NULL, 2, NULL, DATE_ADD(@seed_date, INTERVAL 0 DAY), 'Điều chỉnh DC000003', 'DRAFT'),
('DC000004', 4, NULL, 2, NULL, DATE_ADD(@seed_date, INTERVAL -2 DAY), 'Điều chỉnh DC000004', 'CANCELLED');

INSERT INTO adjustment_details (adjustment_id, product_id, system_quantity, actual_quantity, quantity_change, unit_price, damaged_change, obsolete_change, note) VALUES
(1, 1, 49, 49, 0, 300000, 0, 0, 'Điều chỉnh hàng mẫu'),
(1, 2, 34, 34, 0, 200000, 0, 0, 'Điều chỉnh hàng mẫu'),
(2, 5, 6, 9, 3, 600000, 1, 0, 'Điều chỉnh hàng mẫu'),
(2, 9, 32, 31, -1, 120000, 0, 0, 'Điều chỉnh hàng mẫu'),
(3, 25, 19, 20, 1, 950000, 0, 0, 'Điều chỉnh hàng mẫu'),
(4, 21, 15, 14, -1, 890000, 0, 0, 'Điều chỉnh hàng mẫu');

INSERT INTO inventory (warehouse_id, product_id, quantity, damaged_quantity, obsolete_quantity) VALUES
(1, 1, 49, 1, 0),
(1, 2, 34, 0, 0),
(1, 3, 22, 0, 0),
(1, 4, 20, 0, 0),
(1, 5, 30, 0, 0),
(1, 7, 19, 0, 0),
(1, 8, 20, 0, 0),
(1, 11, 25, 0, 0),
(1, 12, 12, 0, 0),
(1, 13, 16, 0, 0),
(1, 14, 16, 0, 0),
(1, 15, 17, 0, 0),
(1, 16, 37, 0, 0),
(1, 17, 40, 0, 0),
(1, 18, 18, 0, 0),
(1, 19, 10, 0, 0),
(1, 20, 20, 0, 0),
(1, 28, 30, 0, 0),
(1, 31, 33, 0, 0),
(1, 32, 20, 0, 0),
(1, 33, 8, 0, 0),
(2, 1, 8, 0, 0),
(2, 2, 5, 0, 0),
(2, 5, 9, 1, 0),
(2, 6, 57, 2, 0),
(2, 9, 31, 0, 1),
(2, 10, 30, 0, 0),
(2, 27, 20, 0, 0),
(2, 29, 26, 0, 0),
(2, 33, 19, 0, 0),
(2, 34, 21, 0, 0),
(2, 36, 40, 0, 0),
(3, 1, 2, 0, 0),
(4, 6, 8, 0, 0),
(4, 8, 8, 0, 0),
(4, 16, 12, 0, 0),
(4, 21, 15, 0, 0),
(4, 22, 10, 0, 0),
(4, 23, 12, 0, 0),
(4, 29, 5, 0, 0),
(4, 35, 16, 0, 0),
(5, 3, 4, 0, 0),
(5, 16, 10, 0, 0),
(5, 24, 10, 0, 0),
(5, 25, 19, 0, 0),
(5, 26, 8, 0, 0),
(5, 30, 37, 0, 0),
(5, 31, 6, 0, 0);

INSERT INTO inventory_transactions (warehouse_id, product_id, created_by, transaction_type, reference_type, reference_id, quantity_before, quantity_change, quantity_after, transaction_date, note) VALUES
(1, 1, 2, 'IMPORT', 'IMPORT_RECEIPT', 1, 0, 70, 70, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -55 DAY), '09:00:00'), 'PN000001'),
(1, 2, 2, 'IMPORT', 'IMPORT_RECEIPT', 1, 0, 45, 45, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -55 DAY), '09:00:00'), 'PN000001'),
(1, 11, 2, 'IMPORT', 'IMPORT_RECEIPT', 1, 0, 30, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -55 DAY), '09:00:00'), 'PN000001'),
(1, 12, 2, 'IMPORT', 'IMPORT_RECEIPT', 1, 0, 12, 12, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -55 DAY), '09:00:00'), 'PN000001'),
(1, 3, 2, 'IMPORT', 'IMPORT_RECEIPT', 2, 0, 35, 35, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -51 DAY), '09:00:00'), 'PN000002'),
(1, 4, 2, 'IMPORT', 'IMPORT_RECEIPT', 2, 0, 25, 25, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -51 DAY), '09:00:00'), 'PN000002'),
(1, 13, 2, 'IMPORT', 'IMPORT_RECEIPT', 2, 0, 18, 18, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -51 DAY), '09:00:00'), 'PN000002'),
(1, 14, 2, 'IMPORT', 'IMPORT_RECEIPT', 2, 0, 16, 16, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -51 DAY), '09:00:00'), 'PN000002'),
(2, 6, 2, 'IMPORT', 'IMPORT_RECEIPT', 3, 0, 75, 75, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -47 DAY), '09:00:00'), 'PN000003'),
(2, 9, 2, 'IMPORT', 'IMPORT_RECEIPT', 3, 0, 40, 40, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -47 DAY), '09:00:00'), 'PN000003'),
(2, 10, 2, 'IMPORT', 'IMPORT_RECEIPT', 3, 0, 35, 35, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -47 DAY), '09:00:00'), 'PN000003'),
(2, 27, 2, 'IMPORT', 'IMPORT_RECEIPT', 3, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -47 DAY), '09:00:00'), 'PN000003'),
(1, 5, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 30, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 7, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 22, 22, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 8, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 24, 24, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 18, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 18, 18, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 19, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 10, 10, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 20, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(1, 28, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 30, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '09:00:00'), 'PN000004'),
(4, 21, 2, 'IMPORT', 'IMPORT_RECEIPT', 5, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -39 DAY), '09:00:00'), 'PN000005'),
(4, 22, 2, 'IMPORT', 'IMPORT_RECEIPT', 5, 0, 12, 12, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -39 DAY), '09:00:00'), 'PN000005'),
(4, 23, 2, 'IMPORT', 'IMPORT_RECEIPT', 5, 0, 12, 12, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -39 DAY), '09:00:00'), 'PN000005'),
(4, 35, 2, 'IMPORT', 'IMPORT_RECEIPT', 5, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -39 DAY), '09:00:00'), 'PN000005'),
(5, 24, 2, 'IMPORT', 'IMPORT_RECEIPT', 6, 0, 12, 12, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -34 DAY), '09:00:00'), 'PN000006'),
(5, 25, 2, 'IMPORT', 'IMPORT_RECEIPT', 6, 0, 24, 24, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -34 DAY), '09:00:00'), 'PN000006'),
(5, 26, 2, 'IMPORT', 'IMPORT_RECEIPT', 6, 0, 10, 10, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -34 DAY), '09:00:00'), 'PN000006'),
(5, 30, 2, 'IMPORT', 'IMPORT_RECEIPT', 6, 0, 40, 40, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -34 DAY), '09:00:00'), 'PN000006'),
(1, 15, 2, 'IMPORT', 'IMPORT_RECEIPT', 7, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -29 DAY), '09:00:00'), 'PN000007'),
(1, 16, 2, 'IMPORT', 'IMPORT_RECEIPT', 7, 0, 55, 55, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -29 DAY), '09:00:00'), 'PN000007'),
(1, 17, 2, 'IMPORT', 'IMPORT_RECEIPT', 7, 0, 45, 45, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -29 DAY), '09:00:00'), 'PN000007'),
(1, 31, 2, 'IMPORT', 'IMPORT_RECEIPT', 7, 0, 40, 40, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -29 DAY), '09:00:00'), 'PN000007'),
(1, 32, 2, 'IMPORT', 'IMPORT_RECEIPT', 7, 0, 20, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -29 DAY), '09:00:00'), 'PN000007'),
(2, 29, 2, 'IMPORT', 'IMPORT_RECEIPT', 8, 0, 35, 35, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -25 DAY), '09:00:00'), 'PN000008'),
(2, 33, 2, 'IMPORT', 'IMPORT_RECEIPT', 8, 0, 22, 22, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -25 DAY), '09:00:00'), 'PN000008'),
(2, 34, 2, 'IMPORT', 'IMPORT_RECEIPT', 8, 0, 25, 25, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -25 DAY), '09:00:00'), 'PN000008'),
(2, 36, 2, 'IMPORT', 'IMPORT_RECEIPT', 8, 0, 40, 40, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -25 DAY), '09:00:00'), 'PN000008'),
(1, 5, 2, 'IMPORT', 'IMPORT_RECEIPT', 9, 30, 12, 42, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -22 DAY), '09:00:00'), 'PNTP000001'),
(1, 33, 2, 'IMPORT', 'IMPORT_RECEIPT', 9, 0, 8, 8, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -22 DAY), '09:00:00'), 'PNTP000001'),
(1, 1, 2, 'EXPORT', 'EXPORT_RECEIPT', 1, 70, -12, 58, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -20 DAY), '09:00:00'), 'PX000001'),
(1, 2, 2, 'EXPORT', 'EXPORT_RECEIPT', 1, 45, -6, 39, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -20 DAY), '09:00:00'), 'PX000001'),
(1, 11, 2, 'EXPORT', 'EXPORT_RECEIPT', 1, 30, -5, 25, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -20 DAY), '09:00:00'), 'PX000001'),
(4, 8, 2, 'IMPORT', 'IMPORT_RECEIPT', 10, 0, 8, 8, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -19 DAY), '09:00:00'), 'PNKH000001'),
(4, 16, 2, 'IMPORT', 'IMPORT_RECEIPT', 10, 0, 12, 12, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -19 DAY), '09:00:00'), 'PNKH000001'),
(1, 3, 2, 'EXPORT', 'EXPORT_RECEIPT', 2, 35, -7, 28, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -18 DAY), '09:00:00'), 'PX000002'),
(1, 4, 2, 'EXPORT', 'EXPORT_RECEIPT', 2, 25, -4, 21, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -18 DAY), '09:00:00'), 'PX000002'),
(1, 13, 2, 'EXPORT', 'EXPORT_RECEIPT', 2, 18, -2, 16, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -18 DAY), '09:00:00'), 'PX000002'),
(4, 21, 2, 'EXPORT', 'EXPORT_RECEIPT', 3, 20, -5, 15, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -16 DAY), '09:00:00'), 'PX000003'),
(4, 22, 2, 'EXPORT', 'EXPORT_RECEIPT', 3, 12, -2, 10, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -16 DAY), '09:00:00'), 'PX000003'),
(4, 35, 2, 'EXPORT', 'EXPORT_RECEIPT', 3, 20, -4, 16, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -16 DAY), '09:00:00'), 'PX000003'),
(2, 6, 2, 'EXPORT', 'EXPORT_RECEIPT', 4, 75, -10, 65, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -14 DAY), '09:00:00'), 'PX000004'),
(2, 9, 2, 'EXPORT', 'EXPORT_RECEIPT', 4, 40, -6, 34, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -14 DAY), '09:00:00'), 'PX000004'),
(2, 10, 2, 'EXPORT', 'EXPORT_RECEIPT', 4, 35, -5, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -14 DAY), '09:00:00'), 'PX000004'),
(1, 1, 2, 'TRANSFER_OUT', 'TRANSFER', 1, 58, -8, 50, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(2, 1, 2, 'TRANSFER_IN', 'TRANSFER', 1, 0, 8, 8, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(1, 2, 2, 'TRANSFER_OUT', 'TRANSFER', 1, 39, -5, 34, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(2, 2, 2, 'TRANSFER_IN', 'TRANSFER', 1, 0, 5, 5, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(1, 5, 2, 'TRANSFER_OUT', 'TRANSFER', 1, 42, -6, 36, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(2, 5, 2, 'TRANSFER_IN', 'TRANSFER', 1, 0, 6, 6, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), 'CK000001'),
(1, 5, 2, 'EXPORT', 'EXPORT_RECEIPT', 5, 36, -6, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -11 DAY), '09:00:00'), 'PX000005'),
(1, 7, 2, 'EXPORT', 'EXPORT_RECEIPT', 5, 22, -3, 19, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -11 DAY), '09:00:00'), 'PX000005'),
(1, 8, 2, 'EXPORT', 'EXPORT_RECEIPT', 5, 24, -4, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -11 DAY), '09:00:00'), 'PX000005'),
(1, 3, 2, 'TRANSFER_OUT', 'TRANSFER', 2, 28, -4, 24, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(5, 3, 2, 'TRANSFER_IN', 'TRANSFER', 2, 0, 4, 4, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(1, 16, 2, 'TRANSFER_OUT', 'TRANSFER', 2, 55, -10, 45, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(5, 16, 2, 'TRANSFER_IN', 'TRANSFER', 2, 0, 10, 10, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(1, 31, 2, 'TRANSFER_OUT', 'TRANSFER', 2, 40, -6, 34, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(5, 31, 2, 'TRANSFER_IN', 'TRANSFER', 2, 0, 6, 6, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), 'CK000002'),
(5, 24, 2, 'EXPORT', 'EXPORT_RECEIPT', 6, 12, -2, 10, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -9 DAY), '09:00:00'), 'PX000006'),
(5, 25, 2, 'EXPORT', 'EXPORT_RECEIPT', 6, 24, -5, 19, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -9 DAY), '09:00:00'), 'PX000006'),
(5, 26, 2, 'EXPORT', 'EXPORT_RECEIPT', 6, 10, -2, 8, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -9 DAY), '09:00:00'), 'PX000006'),
(3, 1, 2, 'IMPORT', 'IMPORT_RECEIPT', 13, 0, 2, 2, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -8 DAY), '09:00:00'), 'PNTR000001'),
(2, 29, 2, 'EXPORT', 'EXPORT_RECEIPT', 7, 35, -5, 30, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -7 DAY), '09:00:00'), 'PX000007'),
(2, 33, 2, 'EXPORT', 'EXPORT_RECEIPT', 7, 22, -3, 19, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -7 DAY), '09:00:00'), 'PX000007'),
(2, 34, 2, 'EXPORT', 'EXPORT_RECEIPT', 7, 25, -4, 21, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -7 DAY), '09:00:00'), 'PX000007'),
(1, 3, 2, 'EXPORT', 'EXPORT_RECEIPT', 9, 24, -2, 22, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'PXTR000001'),
(1, 4, 2, 'EXPORT', 'EXPORT_RECEIPT', 9, 21, -1, 20, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'PXTR000001'),
(2, 6, 2, 'TRANSFER_OUT', 'TRANSFER', 3, 65, -8, 57, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'CK000003'),
(4, 6, 2, 'TRANSFER_IN', 'TRANSFER', 3, 0, 8, 8, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'CK000003'),
(2, 29, 2, 'TRANSFER_OUT', 'TRANSFER', 3, 30, -5, 25, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'CK000003'),
(4, 29, 2, 'TRANSFER_IN', 'TRANSFER', 3, 0, 5, 5, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), 'CK000003'),
(1, 15, 2, 'EXPORT', 'EXPORT_RECEIPT', 8, 20, -3, 17, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -5 DAY), '09:00:00'), 'PX000008'),
(1, 16, 2, 'EXPORT', 'EXPORT_RECEIPT', 8, 45, -8, 37, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -5 DAY), '09:00:00'), 'PX000008'),
(1, 17, 2, 'EXPORT', 'EXPORT_RECEIPT', 8, 45, -5, 40, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -5 DAY), '09:00:00'), 'PX000008'),
(5, 30, 2, 'EXPORT', 'EXPORT_RECEIPT', 10, 40, -3, 37, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -5 DAY), '09:00:00'), 'PXNV000001'),
(1, 1, 2, 'ADJUSTMENT', 'STOCK_CHECK', 1, 50, -1, 49, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -4 DAY), '09:00:00'), 'KK000001'),
(1, 1, 2, 'DAMAGE_ADJUSTMENT', 'STOCK_CHECK', 1, 0, 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -4 DAY), '09:00:00'), 'KK000001'),
(2, 9, 2, 'EXPORT', 'EXPORT_RECEIPT', 11, 34, -2, 32, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -3 DAY), '09:00:00'), 'PXHU000001'),
(1, 31, 2, 'EXPORT', 'EXPORT_RECEIPT', 12, 34, -1, 33, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), 'PXKH000001'),
(2, 6, 2, 'DAMAGE_ADJUSTMENT', 'STOCK_CHECK', 2, 0, 2, 2, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), 'KK000002'),
(2, 9, 2, 'OBSOLETE_ADJUSTMENT', 'STOCK_CHECK', 2, 0, 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), 'KK000002'),
(2, 29, 2, 'ADJUSTMENT', 'STOCK_CHECK', 2, 25, 1, 26, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), 'KK000002'),
(2, 5, 2, 'ADJUSTMENT', 'ADJUSTMENT', 2, 6, 3, 9, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -1 DAY), '09:00:00'), 'DC000002'),
(2, 5, 2, 'DAMAGE_ADJUSTMENT', 'ADJUSTMENT', 2, 0, 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -1 DAY), '09:00:00'), 'DC000002'),
(2, 9, 2, 'ADJUSTMENT', 'ADJUSTMENT', 2, 32, -1, 31, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -1 DAY), '09:00:00'), 'DC000002');

INSERT INTO vat_invoices (invoice_number, invoice_type, invoice_date, due_date, supplier_id, customer_id, import_id, export_id, seller_name, seller_tax_code, buyer_name, buyer_tax_code, subtotal, vat_rate, vat_amount, total_amount, status, created_by) VALUES
('HDV000001', 'INPUT', DATE_ADD(@seed_date, INTERVAL -53 DAY), DATE_ADD(@seed_date, INTERVAL -7 DAY), 1, NULL, 1, NULL, 'Công ty Công nghệ Sao Việt', '0301000001', 'StockFlow', '0309000001', 52200000, 10, 5220000, 57420000, 'ISSUED', 1),
('HDV000002', 'INPUT', DATE_ADD(@seed_date, INTERVAL -49 DAY), DATE_ADD(@seed_date, INTERVAL 21 DAY), 2, NULL, 2, NULL, 'Nhà phân phối Phú Quý', '0102000002', 'StockFlow', '0309000001', 107300000, 10, 10730000, 118030000, 'ISSUED', 1),
('HDV000003', 'INPUT', DATE_ADD(@seed_date, INTERVAL -45 DAY), DATE_ADD(@seed_date, INTERVAL -5 DAY), 3, NULL, 3, NULL, 'Thiết bị điện Đông Á', '3703000003', 'StockFlow', '0309000001', 37975000, 10, 3797500, 41772500, 'ISSUED', 1),
('HDV000004', 'INPUT', DATE_ADD(@seed_date, INTERVAL -41 DAY), DATE_ADD(@seed_date, INTERVAL 14 DAY), 4, NULL, 4, NULL, 'Công ty Mạng Toàn Cầu', '0305000004', 'StockFlow', '0309000001', 127580000, 10, 12758000, 140338000, 'ISSUED', 1),
('HDV000005', 'INPUT', DATE_ADD(@seed_date, INTERVAL -37 DAY), DATE_ADD(@seed_date, INTERVAL 24 DAY), 5, NULL, 5, NULL, 'Điện tử Đại Nam', '0406000005', 'StockFlow', '0309000001', 90000000, 10, 9000000, 99000000, 'ISSUED', 1),
('HDV000006', 'INPUT', DATE_ADD(@seed_date, INTERVAL -32 DAY), DATE_ADD(@seed_date, INTERVAL 18 DAY), 6, NULL, 6, NULL, 'Công ty Văn phòng Hòa Bình', '1807000006', 'StockFlow', '0309000001', 101100000, 10, 10110000, 111210000, 'ISSUED', 1),
('HDV000007', 'INPUT', DATE_ADD(@seed_date, INTERVAL -27 DAY), DATE_ADD(@seed_date, INTERVAL 25 DAY), 1, NULL, 7, NULL, 'Công ty Công nghệ Sao Việt', '0301000001', 'StockFlow', '0309000001', 64750000, 10, 6475000, 71225000, 'DRAFT', 1),
('HDV000008', 'INPUT', DATE_ADD(@seed_date, INTERVAL -23 DAY), DATE_ADD(@seed_date, INTERVAL 20 DAY), 2, NULL, 8, NULL, 'Nhà phân phối Phú Quý', '0102000002', 'StockFlow', '0309000001', 43390000, 10, 4339000, 47729000, 'CANCELLED', 1),
('HDRA000009', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -19 DAY), DATE_ADD(@seed_date, INTERVAL -2 DAY), NULL, 1, NULL, 1, 'StockFlow', '0309000001', 'Công ty Minh Long', '0304000001', 7340000, 10, 734000, 8074000, 'ISSUED', 1),
('HDRA000010', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -17 DAY), DATE_ADD(@seed_date, INTERVAL 18 DAY), NULL, 2, NULL, 2, 'StockFlow', '0309000001', 'Công ty An Bình', '0304000002', 14600000, 10, 1460000, 16060000, 'ISSUED', 1),
('HDRA000011', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -15 DAY), DATE_ADD(@seed_date, INTERVAL 15 DAY), NULL, 3, NULL, 3, 'StockFlow', '0309000001', 'Đại lý Hoàng Gia', '0304000003', 15080000, 10, 1508000, 16588000, 'ISSUED', 1),
('HDRA000012', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -13 DAY), DATE_ADD(@seed_date, INTERVAL 20 DAY), NULL, 4, NULL, 4, 'StockFlow', '0309000001', 'Công ty Tân Phát', '0304000004', 1950000, 10, 195000, 2145000, 'ISSUED', 1),
('HDRA000013', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -10 DAY), DATE_ADD(@seed_date, INTERVAL 24 DAY), NULL, 5, NULL, 5, 'StockFlow', '0309000001', 'Siêu thị Công nghệ Miền Tây', '1804000005', 9730000, 10, 973000, 10703000, 'ISSUED', 1),
('HDRA000014', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -8 DAY), DATE_ADD(@seed_date, INTERVAL 25 DAY), NULL, 6, NULL, 6, 'StockFlow', '0309000001', 'Trường Đại học Hải Đăng', '0404000006', 21550000, 10, 2155000, 23705000, 'ISSUED', 1),
('HDRA000015', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -6 DAY), DATE_ADD(@seed_date, INTERVAL 17 DAY), NULL, 7, NULL, 7, 'StockFlow', '0309000001', 'Cửa hàng Nhật Minh', '0304000007', 5390000, 10, 539000, 5929000, 'ISSUED', 1),
('HDRA000016', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -4 DAY), DATE_ADD(@seed_date, INTERVAL 30 DAY), NULL, 1, NULL, 8, 'StockFlow', '0309000001', 'Công ty Minh Long', '0304000001', 8710000, 10, 871000, 9581000, 'ISSUED', 1),
('HDRA000017', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -1 DAY), DATE_ADD(@seed_date, INTERVAL 30 DAY), NULL, 3, NULL, 13, 'StockFlow', '0309000001', 'Đại lý Hoàng Gia', '0304000003', 1530000, 10, 153000, 1683000, 'DRAFT', 1),
('HDRA000018', 'OUTPUT', DATE_ADD(@seed_date, INTERVAL -12 DAY), DATE_ADD(@seed_date, INTERVAL 18 DAY), NULL, 5, NULL, 14, 'StockFlow', '0309000001', 'Siêu thị Công nghệ Miền Tây', '1804000005', 560000, 10, 56000, 616000, 'CANCELLED', 1);

INSERT INTO vat_invoice_details (invoice_id, product_id, quantity, unit_price, subtotal, vat_rate, vat_amount, total_amount) VALUES
(1, 1, 70, 300000, 21000000, 10, 2100000, 23100000),
(1, 2, 45, 200000, 9000000, 10, 900000, 9900000),
(1, 11, 30, 280000, 8400000, 10, 840000, 9240000),
(1, 12, 12, 1150000, 13800000, 10, 1380000, 15180000),
(2, 3, 35, 800000, 28000000, 10, 2800000, 30800000),
(2, 4, 25, 1000000, 25000000, 10, 2500000, 27500000),
(2, 13, 18, 1550000, 27900000, 10, 2790000, 30690000),
(2, 14, 16, 1650000, 26400000, 10, 2640000, 29040000),
(3, 6, 75, 45000, 3375000, 10, 337500, 3712500),
(3, 9, 40, 120000, 4800000, 10, 480000, 5280000),
(3, 10, 35, 80000, 2800000, 10, 280000, 3080000),
(3, 27, 20, 1350000, 27000000, 10, 2700000, 29700000),
(4, 5, 30, 600000, 18000000, 10, 1800000, 19800000),
(4, 7, 22, 850000, 18700000, 10, 1870000, 20570000),
(4, 8, 24, 420000, 10080000, 10, 1008000, 11088000),
(4, 18, 18, 1250000, 22500000, 10, 2250000, 24750000),
(4, 19, 10, 2800000, 28000000, 10, 2800000, 30800000),
(4, 20, 20, 390000, 7800000, 10, 780000, 8580000),
(4, 28, 30, 750000, 22500000, 10, 2250000, 24750000),
(5, 21, 20, 890000, 17800000, 10, 1780000, 19580000),
(5, 22, 12, 2350000, 28200000, 10, 2820000, 31020000),
(5, 23, 12, 2450000, 29400000, 10, 2940000, 32340000),
(5, 35, 20, 730000, 14600000, 10, 1460000, 16060000),
(6, 24, 12, 4100000, 49200000, 10, 4920000, 54120000),
(6, 25, 24, 950000, 22800000, 10, 2280000, 25080000),
(6, 26, 10, 2550000, 25500000, 10, 2550000, 28050000),
(6, 30, 40, 90000, 3600000, 10, 360000, 3960000),
(7, 15, 20, 1350000, 27000000, 10, 2700000, 29700000),
(7, 16, 55, 180000, 9900000, 10, 990000, 10890000),
(7, 17, 45, 290000, 13050000, 10, 1305000, 14355000),
(7, 31, 40, 125000, 5000000, 10, 500000, 5500000),
(7, 32, 20, 490000, 9800000, 10, 980000, 10780000),
(8, 29, 35, 220000, 7700000, 10, 770000, 8470000),
(8, 33, 22, 520000, 11440000, 10, 1144000, 12584000),
(8, 34, 25, 410000, 10250000, 10, 1025000, 11275000),
(8, 36, 40, 350000, 14000000, 10, 1400000, 15400000),
(9, 1, 12, 350000, 4200000, 10, 420000, 4620000),
(9, 2, 6, 240000, 1440000, 10, 144000, 1584000),
(9, 11, 5, 340000, 1700000, 10, 170000, 1870000),
(10, 3, 7, 900000, 6300000, 10, 630000, 6930000),
(10, 4, 4, 1150000, 4600000, 10, 460000, 5060000),
(10, 13, 2, 1850000, 3700000, 10, 370000, 4070000),
(11, 21, 5, 1120000, 5600000, 10, 560000, 6160000),
(11, 22, 2, 2900000, 5800000, 10, 580000, 6380000),
(11, 35, 4, 920000, 3680000, 10, 368000, 4048000),
(12, 6, 10, 55000, 550000, 10, 55000, 605000),
(12, 9, 6, 150000, 900000, 10, 90000, 990000),
(12, 10, 5, 100000, 500000, 10, 50000, 550000),
(13, 5, 6, 750000, 4500000, 10, 450000, 4950000),
(13, 7, 3, 1050000, 3150000, 10, 315000, 3465000),
(13, 8, 4, 520000, 2080000, 10, 208000, 2288000),
(14, 24, 2, 4800000, 9600000, 10, 960000, 10560000),
(14, 25, 5, 1150000, 5750000, 10, 575000, 6325000),
(14, 26, 2, 3100000, 6200000, 10, 620000, 6820000),
(15, 29, 5, 280000, 1400000, 10, 140000, 1540000),
(15, 33, 3, 650000, 1950000, 10, 195000, 2145000),
(15, 34, 4, 510000, 2040000, 10, 204000, 2244000),
(16, 15, 3, 1690000, 5070000, 10, 507000, 5577000),
(16, 16, 8, 230000, 1840000, 10, 184000, 2024000),
(16, 17, 5, 360000, 1800000, 10, 180000, 1980000),
(17, 1, 3, 350000, 1050000, 10, 105000, 1155000),
(17, 2, 2, 240000, 480000, 10, 48000, 528000),
(18, 29, 2, 280000, 560000, 10, 56000, 616000);

INSERT INTO debt_opening_balances (fiscal_year, effective_date, partner_type, customer_id, supplier_id, category, budget_chapter, budget_type, budget_section, budget_item, debit_amount, credit_amount, reference_code, note, status, created_by, confirmed_by, confirmed_at, cancelled_by, cancelled_at) VALUES
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'CUSTOMER', 1, NULL, 'TRADE_RECEIVABLE', '001', '01', '001', '001', 18000000, 0, 'SD-001', 'Số dư đầu kỳ mẫu', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -69 DAY), '09:00:00'), NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'CUSTOMER', 2, NULL, 'OTHER_RECEIVABLE', '001', '01', '001', '001', 4500000, 0, 'SD-002', 'Số dư đầu kỳ mẫu', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -69 DAY), '09:00:00'), NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'CUSTOMER', 3, NULL, 'RESERVE_CAPITAL', '001', '01', '001', '001', 2600000, 0, 'SD-003', 'Số dư đầu kỳ mẫu', 'DRAFT', 1, NULL, NULL, NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'CUSTOMER', 4, NULL, 'SHORTFALL', '001', '01', '001', '001', 850000, 0, 'SD-004', 'Số dư đầu kỳ mẫu', 'CANCELLED', 1, NULL, NULL, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -68 DAY), '09:00:00')),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'SUPPLIER', NULL, 1, 'TRADE_PAYABLE', '001', '01', '001', '001', 0, 12500000, 'SD-005', 'Số dư đầu kỳ mẫu', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -69 DAY), '09:00:00'), NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'SUPPLIER', NULL, 2, 'EXTERNAL', '001', '01', '001', '001', 0, 7400000, 'SD-006', 'Số dư đầu kỳ mẫu', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -69 DAY), '09:00:00'), NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'SUPPLIER', NULL, 3, 'INTERNAL', '001', '01', '001', '001', 0, 2800000, 'SD-007', 'Số dư đầu kỳ mẫu', 'DRAFT', 1, NULL, NULL, NULL, NULL),
(YEAR(@seed_date), MAKEDATE(YEAR(@seed_date), 1), 'SUPPLIER', NULL, 4, 'OTHER_PAYABLE', '001', '01', '001', '001', 0, 1600000, 'SD-008', 'Số dư đầu kỳ mẫu', 'CANCELLED', 1, NULL, NULL, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -68 DAY), '09:00:00'));

INSERT INTO debt_ledger_entries (entry_date, partner_type, customer_id, supplier_id, category, debit_amount, credit_amount, entry_source, reference_type, reference_id, reference_code, description, status, created_by, confirmed_by, confirmed_at, cancelled_by, cancelled_at) VALUES
(DATE_ADD(@seed_date, INTERVAL -53 DAY), 'SUPPLIER', NULL, 1, 'TRADE_PAYABLE', 0, 57420000, 'INVOICE', 'VAT_INVOICE', 1, 'HDV000001', 'Ghi nhận hóa đơn HDV000001', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -53 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -49 DAY), 'SUPPLIER', NULL, 2, 'TRADE_PAYABLE', 0, 118030000, 'INVOICE', 'VAT_INVOICE', 2, 'HDV000002', 'Ghi nhận hóa đơn HDV000002', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -49 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -45 DAY), 'SUPPLIER', NULL, 3, 'TRADE_PAYABLE', 0, 41772500, 'INVOICE', 'VAT_INVOICE', 3, 'HDV000003', 'Ghi nhận hóa đơn HDV000003', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -45 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -41 DAY), 'SUPPLIER', NULL, 4, 'TRADE_PAYABLE', 0, 140338000, 'INVOICE', 'VAT_INVOICE', 4, 'HDV000004', 'Ghi nhận hóa đơn HDV000004', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -41 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -37 DAY), 'SUPPLIER', NULL, 5, 'TRADE_PAYABLE', 0, 99000000, 'INVOICE', 'VAT_INVOICE', 5, 'HDV000005', 'Ghi nhận hóa đơn HDV000005', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -37 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -32 DAY), 'SUPPLIER', NULL, 6, 'TRADE_PAYABLE', 0, 111210000, 'INVOICE', 'VAT_INVOICE', 6, 'HDV000006', 'Ghi nhận hóa đơn HDV000006', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -32 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -19 DAY), 'CUSTOMER', 1, NULL, 'TRADE_RECEIVABLE', 8074000, 0, 'INVOICE', 'VAT_INVOICE', 9, 'HDRA000009', 'Ghi nhận hóa đơn HDRA000009', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -19 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -17 DAY), 'CUSTOMER', 2, NULL, 'TRADE_RECEIVABLE', 16060000, 0, 'INVOICE', 'VAT_INVOICE', 10, 'HDRA000010', 'Ghi nhận hóa đơn HDRA000010', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -17 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -15 DAY), 'CUSTOMER', 3, NULL, 'TRADE_RECEIVABLE', 16588000, 0, 'INVOICE', 'VAT_INVOICE', 11, 'HDRA000011', 'Ghi nhận hóa đơn HDRA000011', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -15 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -13 DAY), 'CUSTOMER', 4, NULL, 'TRADE_RECEIVABLE', 2145000, 0, 'INVOICE', 'VAT_INVOICE', 12, 'HDRA000012', 'Ghi nhận hóa đơn HDRA000012', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -10 DAY), 'CUSTOMER', 5, NULL, 'TRADE_RECEIVABLE', 10703000, 0, 'INVOICE', 'VAT_INVOICE', 13, 'HDRA000013', 'Ghi nhận hóa đơn HDRA000013', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -8 DAY), 'CUSTOMER', 6, NULL, 'TRADE_RECEIVABLE', 23705000, 0, 'INVOICE', 'VAT_INVOICE', 14, 'HDRA000014', 'Ghi nhận hóa đơn HDRA000014', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -8 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -6 DAY), 'CUSTOMER', 7, NULL, 'TRADE_RECEIVABLE', 5929000, 0, 'INVOICE', 'VAT_INVOICE', 15, 'HDRA000015', 'Ghi nhận hóa đơn HDRA000015', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -6 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -4 DAY), 'CUSTOMER', 1, NULL, 'TRADE_RECEIVABLE', 9581000, 0, 'INVOICE', 'VAT_INVOICE', 16, 'HDRA000016', 'Ghi nhận hóa đơn HDRA000016', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -4 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -20 DAY), 'SUPPLIER', NULL, 2, 'TRADE_PAYABLE', 59015000, 0, 'PAYMENT', 'VAT_INVOICE', 2, 'HDV000002', 'Thanh toán một phần/toàn bộ HDV000002', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -20 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -12 DAY), 'SUPPLIER', NULL, 3, 'TRADE_PAYABLE', 41772500, 0, 'PAYMENT', 'VAT_INVOICE', 3, 'HDV000003', 'Thanh toán một phần/toàn bộ HDV000003', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -12 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -7 DAY), 'SUPPLIER', NULL, 4, 'TRADE_PAYABLE', 46779333, 0, 'PAYMENT', 'VAT_INVOICE', 4, 'HDV000004', 'Thanh toán một phần/toàn bộ HDV000004', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -7 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -10 DAY), 'CUSTOMER', 1, NULL, 'TRADE_RECEIVABLE', 0, 4037000, 'PAYMENT', 'VAT_INVOICE', 9, 'HDRA000009', 'Thanh toán một phần/toàn bộ HDRA000009', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -10 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -5 DAY), 'CUSTOMER', 2, NULL, 'TRADE_RECEIVABLE', 0, 16060000, 'PAYMENT', 'VAT_INVOICE', 10, 'HDRA000010', 'Thanh toán một phần/toàn bộ HDRA000010', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -5 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -2 DAY), 'CUSTOMER', 4, NULL, 'TRADE_RECEIVABLE', 0, 536250, 'PAYMENT', 'VAT_INVOICE', 12, 'HDRA000012', 'Thanh toán một phần/toàn bộ HDRA000012', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -1 DAY), 'CUSTOMER', 6, NULL, 'TRADE_RECEIVABLE', 0, 23705000, 'PAYMENT', 'VAT_INVOICE', 14, 'HDRA000014', 'Thanh toán một phần/toàn bộ HDRA000014', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -1 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -3 DAY), 'CUSTOMER', 3, NULL, 'OTHER_RECEIVABLE', 750000, 0, 'MANUAL', NULL, NULL, 'PT-001', 'Phí vận chuyển phát sinh', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -3 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -2 DAY), 'SUPPLIER', NULL, 5, 'OTHER_PAYABLE', 0, 380000, 'ADJUSTMENT', NULL, NULL, 'DC-CN-001', 'Điều chỉnh công nợ', 'CONFIRMED', 1, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -2 DAY), '09:00:00'), NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -1 DAY), 'CUSTOMER', 4, NULL, 'TRADE_RECEIVABLE', 900000, 0, 'MANUAL', NULL, NULL, 'PT-002', 'Đề nghị ghi nhận công nợ', 'DRAFT', 1, NULL, NULL, NULL, NULL),
(DATE_ADD(@seed_date, INTERVAL -9 DAY), 'SUPPLIER', NULL, 3, 'TRADE_PAYABLE', 0, 210000, 'MANUAL', NULL, NULL, 'PT-003', 'Bút toán đã hủy', 'CANCELLED', 1, NULL, NULL, 1, TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -9 DAY), '09:00:00'));

INSERT INTO audit_logs (user_id, action, resource, resource_id, method, path, status_code, ip_address, metadata, created_at) VALUES
(1, 'LOGIN', 'auth', NULL, 'POST', '/api/auth/login', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -56 DAY), '10:00:00')),
(1, 'CREATE', 'products', '1', 'POST', '/api/products', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -56 DAY), '10:00:00')),
(1, 'CONFIRM', 'import_receipts', '4', 'POST', '/api/imports/4/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -43 DAY), '10:00:00')),
(1, 'CONFIRM', 'export_receipts', '1', 'POST', '/api/exports/1/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -20 DAY), '10:00:00')),
(1, 'CONFIRM', 'transfer_receipts', '1', 'POST', '/api/transfers/1/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -13 DAY), '10:00:00')),
(1, 'CONFIRM', 'stock_checks', '1', 'POST', '/api/stock-checks/1/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -4 DAY), '10:00:00')),
(1, 'CONFIRM', 'adjustment_receipts', '2', 'POST', '/api/adjustments/2/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -1 DAY), '10:00:00')),
(2, 'CREATE', 'vat_invoices', '18', 'POST', '/api/vat-invoices', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -4 DAY), '10:00:00')),
(1, 'CONFIRM', 'debt_ledger_entries', '1', 'POST', '/api/debt/ledger/1/confirm', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL -3 DAY), '10:00:00')),
(2, 'READ', 'inventory', NULL, 'GET', '/api/inventory', 200, '127.0.0.1', JSON_OBJECT('demo', TRUE, 'source', 'seed'), TIMESTAMP(DATE_ADD(@seed_date, INTERVAL 0 DAY), '10:00:00'));

COMMIT;
