-- ============================================================
-- Inventory Count Management System - Database Schema (Legacy Format)
-- ============================================================

CREATE DATABASE IF NOT EXISTS `invcount_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `invcount_db`;

-- -----------------------------------------------------------
-- Users Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `role` ENUM('admin', 'controller', 'counter', 'checker') NOT NULL DEFAULT 'counter',
    `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    `last_login` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_role` (`role`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Sessions Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `status` ENUM('draft', 'open', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    `start_date` DATETIME DEFAULT NULL,
    `end_date` DATETIME DEFAULT NULL,
    `created_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,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Items Table (Legacy Exact Match)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `Item_Code` VARCHAR(50) NOT NULL UNIQUE,
    `Item_Description` VARCHAR(255) DEFAULT NULL,
    `Item_Group` VARCHAR(100) DEFAULT NULL,
    `Stores` VARCHAR(100) DEFAULT NULL,
    `Rack` VARCHAR(50) DEFAULT NULL,
    `Level` VARCHAR(50) DEFAULT NULL,
    `Bin` VARCHAR(50) DEFAULT NULL,
    `Uom` VARCHAR(20) DEFAULT 'pcs',
    `System_Qty` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    `Status` VARCHAR(20) NOT NULL DEFAULT 'Active',
    `Created_At` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Updated_At` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_item_code` (`Item_Code`),
    INDEX `idx_item_group` (`Item_Group`),
    INDEX `idx_stores` (`Stores`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Assignments Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `assignments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT UNSIGNED NOT NULL,
    `counter_id` INT UNSIGNED NOT NULL,
    `checker_id` INT UNSIGNED DEFAULT NULL,
    `item_id` INT UNSIGNED NOT NULL,
    `zone` VARCHAR(100) DEFAULT NULL,
    `status` ENUM('pending', 'in_progress', 'counted', 'checked', 'approved', 'rejected', 'recount') NOT NULL DEFAULT 'pending',
    `priority` ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    `notes` TEXT DEFAULT NULL,
    `assigned_by` INT UNSIGNED NOT NULL,
    `assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`counter_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`checker_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_session` (`session_id`),
    INDEX `idx_counter` (`counter_id`),
    INDEX `idx_checker` (`checker_id`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Count Entries Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `count_entries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `assignment_id` INT UNSIGNED NOT NULL,
    `counter_id` INT UNSIGNED NOT NULL,
    `counted_quantity` DECIMAL(12,2) NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `count_method` ENUM('manual', 'barcode', 'rfid') NOT NULL DEFAULT 'manual',
    `counted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`counter_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_assignment` (`assignment_id`),
    INDEX `idx_counter` (`counter_id`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- PWA Offline Sync columns for count_entries (migration section)
-- Run these ALTER TABLE statements on an existing database to add
-- offline replication columns after deploying the PWA service worker.
-- -----------------------------------------------------------
ALTER TABLE `count_entries`
  ADD COLUMN IF NOT EXISTS `sync_local_id`   VARCHAR(128) NULL
    COMMENT 'Client-generated sync identifier for deduplication' AFTER `created_at`,
  ADD COLUMN IF NOT EXISTS `synced_at`        DATETIME NULL
    COMMENT 'Timestamp when the offline record was first synced to the server' AFTER `sync_local_id`,
  ADD COLUMN IF NOT EXISTS `sync_origin`      VARCHAR(20) DEFAULT 'online'
    COMMENT 'Origin of the record: online (default) or offline (drained from browser queue)' AFTER `synced_at`;
ALTER TABLE `count_entries`
  ADD INDEX IF NOT EXISTS `idx_sync_local_id` (`sync_local_id`),
  ADD INDEX IF NOT EXISTS `idx_synced_at`     (`synced_at`),
  ADD INDEX IF NOT EXISTS `idx_sync_origin`   (`sync_origin`);

-- -----------------------------------------------------------
-- Check Reviews Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `check_reviews` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `assignment_id` INT UNSIGNED NOT NULL,
    `count_entry_id` INT UNSIGNED NOT NULL,
    `checker_id` INT UNSIGNED NOT NULL,
    `decision` ENUM('approved', 'rejected') NOT NULL,
    `comments` TEXT DEFAULT NULL,
    `reviewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`count_entry_id`) REFERENCES `count_entries`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`checker_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_assignment` (`assignment_id`),
    INDEX `idx_checker` (`checker_id`),
    INDEX `idx_decision` (`decision`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Counting Instructions Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `counting_instructions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(200) NOT NULL,
    `content` TEXT NOT NULL,
    `session_id` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_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,
    FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Checking Guidelines Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `checking_guidelines` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(200) NOT NULL,
    `content` TEXT NOT NULL,
    `session_id` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_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,
    FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Audit Log Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(50) NOT NULL,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED DEFAULT NULL,
    `details` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_entity` (`entity_type`, `entity_id`),
    INDEX `idx_action` (`action`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Monthly Inventory Snapshots Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `MonthlyInventorySnapshots` (
    `SnapshotID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `SnapshotDate` DATE NOT NULL,              -- Month-end date (e.g., 2024-01-31)
    `ProductID` INT UNSIGNED NOT NULL,         -- items.id
    `SystemQty` DECIMAL(18, 2) NOT NULL,
    `RecordedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `RecordedBy` INT UNSIGNED NULL,            -- users.id
    FOREIGN KEY (`ProductID`) REFERENCES `items`(`id`) ON DELETE CASCADE,
    CONSTRAINT `UQ_Product_Month` UNIQUE (`SnapshotDate`, `ProductID`),
    INDEX `idx_snapshot_date` (`SnapshotDate`),
    INDEX `idx_product` (`ProductID`),
    INDEX `idx_recorded_by` (`RecordedBy`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Add image_path to items table (for product images)
-- -----------------------------------------------------------
ALTER TABLE `items` ADD COLUMN IF NOT EXISTS `image_path` VARCHAR(255) NULL AFTER `Status`;
ALTER TABLE `items` ADD INDEX IF NOT EXISTS `idx_image_path` (`image_path`);

-- -----------------------------------------------------------
-- System Settings Table
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT DEFAULT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT 'string',
    `group` VARCHAR(50) NOT NULL DEFAULT 'general',
    `label` VARCHAR(100) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `options` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_group` (`group`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------
-- Default Admin User (password: admin123)
-- -----------------------------------------------------------
INSERT INTO `users` (`username`, `email`, `password_hash`, `full_name`, `role`, `status`)
VALUES ('admin', 'admin@invcount.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin', 'active')
ON DUPLICATE KEY UPDATE `username` = `username`;
