-- =============================================================================
-- XPERT 3PL - Migracion 028
-- Conteo de inventario y ajuste de stock (ver ReplenishmentService/
-- RelocationService para el mismo espiritu: task_type=COUNT y
-- movement_type=ADJUSTMENT ya existian en los enums de 001_schema.sql
-- desde el arranque del proyecto, sin uso hasta ahora).
--
-- Conteo ciego (el operador nunca ve system_qty), con tolerancia por
-- empresa que dispara reconteo automatico, y aprobacion obligatoria por
-- alguien distinto de quien conto/reconto antes de tocar stock real.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `stock_counts` (
    `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`          INT UNSIGNED NOT NULL,
    `warehouse_id`        INT UNSIGNED NOT NULL,
    `bin_id`              INT UNSIGNED NOT NULL,
    `doc_number`          VARCHAR(30) NOT NULL,
    `status`              ENUM('PENDING','COUNTING','NEEDS_RECOUNT','PENDING_APPROVAL','APPROVED','REJECTED','CANCELLED')
                          NOT NULL DEFAULT 'PENDING',
    `reason`              TEXT NULL COMMENT 'Motivo del conteo ad-hoc (sospecha, previo a despacho grande, etc.)',
    `assigned_to`         INT UNSIGNED NULL,
    `counted_by`          INT UNSIGNED NULL,
    `counted_at`          DATETIME NULL,
    `recount_assigned_to` INT UNSIGNED NULL,
    `recounted_by`        INT UNSIGNED NULL,
    `recounted_at`        DATETIME NULL,
    `approved_by`         INT UNSIGNED NULL,
    `approved_at`         DATETIME NULL,
    `approval_notes`      TEXT NULL,
    `requested_by`        INT UNSIGNED 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_stockcounts_docnumber` (`doc_number`),
    KEY `idx_stockcounts_company` (`company_id`),
    KEY `idx_stockcounts_status` (`status`),
    KEY `idx_stockcounts_bin` (`bin_id`),
    CONSTRAINT `fk_stockcounts_company`   FOREIGN KEY (`company_id`)   REFERENCES `companies`(`id`),
    CONSTRAINT `fk_stockcounts_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_stockcounts_bin`       FOREIGN KEY (`bin_id`)       REFERENCES `bins`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Documento de conteo de inventario ad-hoc, por bin';

CREATE TABLE IF NOT EXISTS `stock_count_lines` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `stock_count_id`  INT UNSIGNED NOT NULL,
    `item_id`         INT UNSIGNED NOT NULL,
    `batch_id`        INT UNSIGNED NULL,
    `serial_number`   VARCHAR(100) NULL,
    `system_qty`      DECIMAL(15,4) NOT NULL COMMENT 'Snapshot al crear el conteo. Nunca se expone al operador.',
    `counted_qty`     DECIMAL(15,4) NULL,
    `recounted_qty`   DECIMAL(15,4) NULL,
    `reason_code`     VARCHAR(30) NULL COMMENT 'DAMAGED, EXPIRED, SHRINKAGE, COUNT_ERROR, OVERAGE, OTHER',
    `reason_notes`    TEXT NULL,
    `status`          ENUM('PENDING','COUNTED','NEEDS_RECOUNT','RECOUNTED','ADJUSTED') NOT NULL DEFAULT 'PENDING',
    `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_stockcountlines_count` (`stock_count_id`),
    CONSTRAINT `fk_stockcountlines_count` FOREIGN KEY (`stock_count_id`) REFERENCES `stock_counts`(`id`),
    CONSTRAINT `fk_stockcountlines_item`  FOREIGN KEY (`item_id`)        REFERENCES `items`(`id`),
    CONSTRAINT `fk_stockcountlines_batch` FOREIGN KEY (`batch_id`)       REFERENCES `batches`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Una fila por item+lote+serie presente en el bin al momento de crear el conteo';

ALTER TABLE `company_settings`
    ADD COLUMN `count_tolerance_pct` DECIMAL(5,2) NOT NULL DEFAULT 5.00
        COMMENT 'Variacion %% (respecto de system_qty) que dispara reconteo automatico en vez de ir directo a aprobacion'
        AFTER `shelf_life_policy`;

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`)
VALUES ('STOCK_COUNT', 'AJ-', 0, 8)
ON DUPLICATE KEY UPDATE `prefix` = `prefix`;
