-- STOCKFLOW - Hệ thống quản lý kho
-- MySQL 8.x

DROP DATABASE IF EXISTS stockflow;
CREATE DATABASE stockflow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE stockflow;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP VIEW IF EXISTS vw_stock_card;
DROP VIEW IF EXISTS vw_inventory_summary;

DROP TABLE IF EXISTS vat_invoice_details;
DROP TABLE IF EXISTS vat_invoices;
DROP TABLE IF EXISTS inventory_transactions;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS adjustment_details;
DROP TABLE IF EXISTS adjustment_receipts;
DROP TABLE IF EXISTS stock_check_details;
DROP TABLE IF EXISTS stock_checks;
DROP TABLE IF EXISTS transfer_details;
DROP TABLE IF EXISTS transfer_receipts;
DROP TABLE IF EXISTS export_details;
DROP TABLE IF EXISTS export_receipts;
DROP TABLE IF EXISTS import_details;
DROP TABLE IF EXISTS import_receipts;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS warehouses;
DROP TABLE IF EXISTS users;

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;

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;

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;

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,
    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;

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;

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;

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;

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),
    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;

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,
    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;

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;

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,
    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;

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;

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,
    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)
) ENGINE=InnoDB;

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', 'APPROVED', 'ADJUSTED', '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;

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;

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;

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,
    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
    )
) ENGINE=InnoDB;

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;

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,
    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;

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,
    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;

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;

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_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);

INSERT INTO categories (category_code, category_name, description) VALUES
('CAT-COMP', 'Linh kiện máy tính', 'Chuột, bàn phím, RAM, SSD'),
('CAT-NET', 'Thiết bị mạng', 'Thiết bị kết nối và lưu trữ mạng'),
('CAT-ELEC', 'Thiết bị điện', 'Dây dẫn và thiết bị điện dân dụng');

INSERT INTO units (unit_code, unit_name, symbol) VALUES
('PCS', 'Cái', 'cái'), ('BOX', 'Hộp', 'hộp'), ('ROLL', 'Cuộn', 'cuộn');

INSERT INTO warehouses (warehouse_code, warehouse_name, address, description) VALUES
('KHO01', 'Kho chính', '12 Nguyễn Trãi, Quận 1, TP.HCM', 'Kho trung tâm'),
('KHO02', 'Kho phụ', '88 Điện Biên Phủ, Bình Thạnh, TP.HCM', 'Kho dự phòng'),
('KHO03', 'Kho cách ly', '20 Lê Lợi, Quận 1, TP.HCM', 'Hàng trả, hỏng, chờ xử lý');

INSERT INTO users (username, password_hash, full_name, email, role) VALUES
('admin', '$2b$demo$admin', 'Quản trị viên', 'admin@stockflow.local', 'ADMIN'),
('warehouse01', '$2b$demo$warehouse01', 'Nguyễn Văn Kho', 'warehouse01@stockflow.local', 'WAREHOUSE_STAFF'),
('warehouse02', '$2b$demo$warehouse02', 'Trần Thị Kho', 'warehouse02@stockflow.local', 'WAREHOUSE_STAFF');

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', 'Lê Minh', '0281111111', 'sales@saoviet.local', 'TP.HCM'),
('NCC002', 'Nhà phân phối Phú Quý', '0102000002', 'Phạm An', '0282222222', 'contact@phuquy.local', 'Hà Nội'),
('NCC003', 'Thiết bị điện Đông Á', '3703000003', 'Võ Bình', '0283333333', 'hello@donga.local', 'Bình Dương');

INSERT INTO customers (customer_code, customer_name, tax_code, contact_name, phone, email, address) VALUES
('KH001', 'Công ty Minh Long', '0304000001', 'Nguyễn Minh', '0901000001', 'contact@minhlong.local', 'TP.HCM'),
('KH002', 'Công ty An Bình', '0304000002', 'Phạm Bình', '0901000002', 'contact@anbinh.local', 'Đồng Nai'),
('KH003', 'Đại lý Hoàng Gia', '0304000003', 'Lê Hoàng', '0901000003', 'contact@hoanggia.local', 'Bình Dương');

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, 10, 300000, 350000, 'Chuột máy tính'),
('SP002', 'Bàn phím Logitech K120', 1, 1, 8, 200000, 240000, 'Bàn phím máy tính'),
('SP003', 'RAM Kingston 16GB', 1, 1, 10, 800000, 900000, 'Bộ nhớ RAM'),
('SP004', 'SSD Samsung 500GB', 1, 1, 8, 1000000, 1150000, 'Ổ cứng SSD'),
('SP005', 'Bộ phát WiFi TP-Link', 2, 1, 5, 600000, 750000, 'Thiết bị WiFi'),
('SP006', 'Dây điện Cadivi 4.0', 3, 3, 20, 45000, 55000, 'Dây điện cuộn'),
('SP007', 'Router TP-Link AX23', 2, 1, 5, 850000, 1050000, 'Router WiFi'),
('SP008', 'Switch TP-Link 8 cổng', 2, 1, 5, 420000, 520000, 'Thiết bị chuyển mạch'),
('SP009', 'Ổ cắm điện 5 mét', 3, 1, 10, 120000, 150000, 'Ổ cắm điện'),
('SP010', 'Hộp đầu nối điện', 3, 2, 10, 80000, 100000, 'Phụ kiện điện');

INSERT INTO import_receipts (import_code, import_type, warehouse_id, supplier_id, created_by, delivery_person, source_name, import_date, reason, note, status) VALUES
('PN000001', 'PURCHASE', 1, 1, 2, 'Nguyễn Văn Giao', 'Công ty Công nghệ Sao Việt', '2026-09-01', 'Nhập mua theo hợp đồng', 'Đợt nhập tháng 9', 'CONFIRMED'),
('PN000002', 'PURCHASE', 1, 2, 2, 'Phạm Minh', 'Nhà phân phối Phú Quý', '2026-09-02', 'Nhập linh kiện máy tính', NULL, 'CONFIRMED'),
('PNTP000001', 'FINISHED_GOODS', 1, NULL, 2, NULL, 'Bộ phận nội bộ', '2026-09-03', 'Nhập thành phẩm', 'Nguồn nội bộ', 'CONFIRMED'),
('PNTR000001', 'CUSTOMER_RETURN', 3, NULL, 2, NULL, 'Công ty Minh Long', '2026-09-10', 'Khách trả hàng lỗi', 'Chờ kiểm tra chất lượng', 'CONFIRMED');

INSERT INTO import_details (import_id, product_id, document_quantity, actual_quantity, unit_price, note) VALUES
(1, 1, 50, 50, 300000, NULL), (1, 2, 30, 30, 200000, NULL),
(2, 3, 20, 20, 800000, NULL), (3, 5, 15, 15, 600000, 'Thành phẩm nội bộ'),
(4, 1, 2, 2, 300000, 'Hàng trả chờ phân loại');

INSERT INTO export_receipts (export_code, export_type, warehouse_id, customer_id, created_by, receiver_name, export_date, reason, note, status) VALUES
('PX000001', 'SALE', 1, 1, 2, 'Nguyễn Minh', '2026-09-05', 'Xuất bán hàng', NULL, 'CONFIRMED'),
('PX000002', 'SALE', 1, 2, 2, 'Phạm Bình', '2026-09-06', 'Xuất bán hàng', NULL, 'CONFIRMED'),
('PXTR000001', 'SUPPLIER_RETURN', 1, NULL, 2, 'NCC001', '2026-09-07', 'Trả hàng lỗi', 'Trả theo biên bản QC', 'CONFIRMED');

INSERT INTO export_details (export_id, product_id, requested_quantity, actual_quantity, unit_price, note) VALUES
(1, 1, 10, 10, 350000, NULL), (1, 2, 5, 5, 240000, NULL),
(2, 5, 3, 3, 750000, NULL), (3, 3, 2, 2, 900000, 'Trả NCC');

UPDATE import_receipts SET source_export_id = 1 WHERE import_id = 4;

INSERT INTO transfer_receipts (transfer_code, from_warehouse_id, to_warehouse_id, created_by, carrier_name, transfer_date, reason, note, status) VALUES
('CK000001', 1, 2, 2, 'Nguyễn Văn T', '2026-09-08', 'Bổ sung hàng cho kho phụ', NULL, 'CONFIRMED');

INSERT INTO transfer_details (transfer_id, product_id, requested_quantity, actual_quantity, note) VALUES
(1, 1, 5, 5, NULL);

INSERT INTO stock_checks (check_code, warehouse_id, created_by, approved_by, check_date, note, status) VALUES
('KK000001', 1, 2, 1, '2026-09-09', 'Kiểm kê định kỳ tháng 9', 'ADJUSTED');

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, 35, 34, 0, 0, 34, 0, 0, 'Thiếu 1 sản phẩm'),
(1, 2, 25, 25, 0, 0, 25, 0, 0, NULL);

INSERT INTO adjustment_receipts (adjustment_code, warehouse_id, check_id, created_by, approved_by, adjustment_date, reason, note, status) VALUES
('DC000001', 1, 1, 2, 1, '2026-09-09', 'Điều chỉnh theo kết quả kiểm kê', NULL, 'CONFIRMED');

INSERT INTO adjustment_details (adjustment_id, product_id, system_quantity, actual_quantity, quantity_change, damaged_change, obsolete_change, note) VALUES
(1, 1, 35, 34, -1, 0, 0, 'Thiếu 1 sản phẩm'),
(1, 2, 25, 25, 0, 0, 0, 'Không chênh lệch');

INSERT INTO inventory (warehouse_id, product_id, quantity, damaged_quantity, obsolete_quantity) VALUES
(1, 1, 34, 0, 0), (1, 2, 25, 0, 0), (1, 3, 18, 0, 0),
(1, 5, 12, 0, 0), (2, 1, 5, 0, 0), (3, 1, 2, 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, 50, 50, '2026-09-01 09:00:00', 'Nhập mua PN000001'),
(1, 2, 2, 'IMPORT', 'IMPORT_RECEIPT', 1, 0, 30, 30, '2026-09-01 09:00:00', 'Nhập mua PN000001'),
(1, 3, 2, 'IMPORT', 'IMPORT_RECEIPT', 2, 0, 20, 20, '2026-09-02 09:00:00', 'Nhập mua PN000002'),
(1, 5, 2, 'IMPORT', 'IMPORT_RECEIPT', 3, 0, 15, 15, '2026-09-03 09:00:00', 'Nhập thành phẩm PNTP000001'),
(1, 1, 2, 'EXPORT', 'EXPORT_RECEIPT', 1, 50, -10, 40, '2026-09-05 09:00:00', 'Xuất bán PX000001'),
(1, 2, 2, 'EXPORT', 'EXPORT_RECEIPT', 1, 30, -5, 25, '2026-09-05 09:00:00', 'Xuất bán PX000001'),
(1, 5, 2, 'EXPORT', 'EXPORT_RECEIPT', 2, 15, -3, 12, '2026-09-06 09:00:00', 'Xuất bán PX000002'),
(1, 3, 2, 'EXPORT', 'EXPORT_RECEIPT', 3, 20, -2, 18, '2026-09-07 09:00:00', 'Trả NCC PXTR000001'),
(1, 1, 2, 'TRANSFER_OUT', 'TRANSFER_RECEIPT', 1, 40, -5, 35, '2026-09-08 09:00:00', 'Chuyển kho CK000001'),
(2, 1, 2, 'TRANSFER_IN', 'TRANSFER_RECEIPT', 1, 0, 5, 5, '2026-09-08 09:00:00', 'Nhận chuyển kho CK000001'),
(1, 1, 2, 'ADJUSTMENT', 'ADJUSTMENT_RECEIPT', 1, 35, -1, 34, '2026-09-09 10:00:00', 'Điều chỉnh theo KK000001'),
(3, 1, 2, 'IMPORT', 'IMPORT_RECEIPT', 4, 0, 2, 2, '2026-09-10 09:00:00', 'Khách trả PNTR000001');

INSERT INTO vat_invoices (invoice_number, invoice_type, invoice_date, supplier_id, import_id, seller_name, seller_tax_code, buyer_name, buyer_tax_code, subtotal, vat_rate, vat_amount, total_amount, status, created_by) VALUES
('HDV000001', 'INPUT', '2026-09-01', 1, 1, 'Công ty Công nghệ Sao Việt', '0301000001', 'StockFlow', '0309000001', 21000000, 10, 2100000, 23100000, 'ISSUED', 1),
('HDV000002', 'INPUT', '2026-09-02', 2, 2, 'Nhà phân phối Phú Quý', '0102000002', 'StockFlow', '0309000001', 16000000, 10, 1600000, 17600000, 'ISSUED', 1);

INSERT INTO vat_invoice_details (invoice_id, product_id, quantity, unit_price, subtotal, vat_rate, vat_amount, total_amount) VALUES
(1, 1, 50, 300000, 15000000, 10, 1500000, 16500000), (1, 2, 30, 200000, 6000000, 10, 600000, 6600000),
(2, 3, 20, 800000, 16000000, 10, 1600000, 17600000);

INSERT INTO vat_invoices (invoice_number, invoice_type, invoice_date, customer_id, export_id, seller_name, seller_tax_code, buyer_name, buyer_tax_code, subtotal, vat_rate, vat_amount, total_amount, status, created_by) VALUES
('HDRA000001', 'OUTPUT', '2026-09-05', 1, 1, 'StockFlow', '0309000001', 'Công ty Minh Long', '0304000001', 4700000, 10, 470000, 5170000, 'ISSUED', 1),
('HDRA000002', 'OUTPUT', '2026-09-06', 2, 2, 'StockFlow', '0309000001', 'Công ty An Bình', '0304000002', 2250000, 10, 225000, 2475000, 'ISSUED', 1);

INSERT INTO vat_invoice_details (invoice_id, product_id, quantity, unit_price, subtotal, vat_rate, vat_amount, total_amount) VALUES
(3, 1, 10, 350000, 3500000, 10, 350000, 3850000), (3, 2, 5, 240000, 1200000, 10, 120000, 1320000),
(4, 5, 3, 750000, 2250000, 10, 225000, 2475000);

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;

SET FOREIGN_KEY_CHECKS = 1;