-- Migration: Create stock_audit_sessions and stock_audit_items tables
-- Date: 2024-07-15
-- Supports the AdminStockAuditController for physical inventory counting.

CREATE TABLE IF NOT EXISTS stock_audit_sessions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status      ENUM('in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'in_progress',
    started_by  INT UNSIGNED NULL,
    started_at  DATETIME NOT NULL,
    closed_by   INT UNSIGNED NULL,
    closed_at   DATETIME NULL,
    created_at  DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_audit_items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id      INT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED NOT NULL,
    system_count    INT NOT NULL DEFAULT 0,
    physical_count  INT NOT NULL DEFAULT 0,
    difference      INT NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL,
    INDEX idx_sai_session (session_id),
    INDEX idx_sai_product (product_id),
    FOREIGN KEY (session_id) REFERENCES stock_audit_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;