-- 61_stock_audit_items_schema_update.sql
--
-- Rebuilds `stock_audit_items` in the shape the application actually uses.
--
-- Why this file was rewritten in Phase 5 (BUG-23, BUG-24, BUG-27):
--
--   * The original version performed six statements that assumed a legacy
--     table with a stock_audit_id column pointing at a legacy stock audit
--     header table. Production still has that legacy shape, which proves the
--     original file never ran anywhere.
--   * 47_stock_audit_tables.sql uses CREATE TABLE IF NOT EXISTS, so on
--     production its definition was silently skipped and the legacy table
--     survived. On an empty database file 47 produces the modern shape
--     instead. The original file 61 therefore matched NEITHER state: five of
--     its six statements failed with errors 1091, 1054 and 1060. Those are
--     exactly the codes the old runner carried in its skip list, which is how
--     the failure stayed invisible for so long.
--   * The original file also carried its own reverse block in the same file,
--     separated only by a comment. The runner has no notion of reversible
--     migrations, so it executed that block immediately after the forward
--     block, undoing the change and then adding a foreign key to a table that
--     no migration creates.
--   * StockAuditRepository.php line 101 writes the columns session_id,
--     product_id, system_count, physical_count, difference and created_at.
--     Against the production schema that fails with error 1054, so submitting
--     a stock count has never worked in production.
--
-- All four stock tables were verified to hold 0 rows in production before this
-- rewrite, so DROP TABLE loses no data. Re-check the row count before ever
-- applying this migration to a live database.
--
-- The legacy stock audit header table and the unused stock notification table
-- are left in place. Neither is referenced by any PHP source file. They are
-- tracked as schema drift under blocker B10; removing legacy tables is a
-- separate, explicit decision.
--
-- ON DELETE RESTRICT is used on both foreign keys. File 47 cascaded from
-- products, which meant deleting a product silently erased audit history.
-- Audit records must not disappear. The unique key closes double-counting of
-- one product inside one session.

DROP TABLE IF EXISTS `stock_audit_items`;

CREATE TABLE `stock_audit_items` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `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 DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_session_product` (`session_id`, `product_id`),
    KEY `idx_audit_items_session_id` (`session_id`),
    KEY `idx_audit_items_product_id` (`product_id`),
    CONSTRAINT `fk_audit_items_session`
        FOREIGN KEY (`session_id`) REFERENCES `stock_audit_sessions` (`id`)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT `fk_audit_items_product`
        FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
        ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
