-- ================================================================================================
-- MIGRATION: Kitchen Staff Accounts
-- Database  : MariaDB 10.6+ (InnoDB)
-- Project   : Sweet Heart Hotel POS — Kitchen Role Module
-- Created   : 2026-05-12
-- Run       : Execute ONCE manually via your DB client.
--             Safe to re-run — guarded by IF NOT EXISTS.
-- ================================================================================================

-- ── Strict mode & safe encoding ──────────────────────────────────────────────
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- ─────────────────────────────────────────────────────────────────────────────
-- TABLE: kitchen_staff
--
-- Stores kitchen display staff accounts.
-- Each account is:
--   • Created and managed by an Admin.
--   • Authenticated via kitchen_id + 4-digit PIN.
--   • Issued a short-lived JWT (role = 'kitchen') on successful login.
--   • Access to operational KDS routes (/api/kitchen/*) only.
--
-- Columns:
--   kitchen_id  — human-readable unique identifier, e.g. KIT-001
--   is_active   — 0 = login blocked (instant revocation via middleware DB check)
--   is_live     — 0 = hidden from admin live-view panel (login still works)
--   created_by  — FK to admins.id — which admin created this account
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `kitchen_staff` (
    `id`            INT              NOT NULL AUTO_INCREMENT          COMMENT 'Auto-incremented primary key',
    `kitchen_id`    VARCHAR(50)      NOT NULL                         COMMENT 'Unique human-readable ID, e.g. KIT-001',
    `name`          VARCHAR(255)     NOT NULL                         COMMENT 'Display name of the kitchen staff member',
    `pin`           VARCHAR(255)     NOT NULL                         COMMENT 'bcrypt-hashed 4-digit login PIN',
    `is_active`     TINYINT(1)       NOT NULL DEFAULT 1               COMMENT '1 = can login | 0 = login blocked (instant effect)',
    `is_live`       TINYINT(1)       NOT NULL DEFAULT 1               COMMENT '1 = visible in admin live panel | 0 = hidden',
    `created_by`    INT              NULL     DEFAULT NULL            COMMENT 'FK → admins.id — admin who created this account',
    `last_login`    DATETIME         NULL     DEFAULT NULL            COMMENT 'Timestamp of most recent successful login',
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
                                              ON UPDATE CURRENT_TIMESTAMP,

    -- ── Constraints ────────────────────────────────────────────────────────
    PRIMARY KEY (`id`),
    UNIQUE  KEY `uq_kitchen_staff_kitchen_id` (`kitchen_id`),

    -- ── Indexes for common query patterns ──────────────────────────────────
    INDEX `idx_ks_is_active`   (`is_active`),
    INDEX `idx_ks_is_live`     (`is_live`),
    INDEX `idx_ks_created_by`  (`created_by`),
    INDEX `idx_ks_last_login`  (`last_login`),

    -- ── Foreign key — soft link; no CASCADE so deleting an admin does not
    --    cascade-delete all kitchen staff they created. ─────────────────────
    CONSTRAINT `fk_ks_created_by_admin`
        FOREIGN KEY (`created_by`)
        REFERENCES `admins` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  ROW_FORMAT=DYNAMIC
  COMMENT='Kitchen display staff accounts — managed exclusively by admins';

-- ─────────────────────────────────────────────────────────────────────────────
-- Verify
-- ─────────────────────────────────────────────────────────────────────────────
SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_COLLATION,
    TABLE_COMMENT
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = DATABASE()
  AND  TABLE_NAME   = 'kitchen_staff';

-- ── Re-enable FK checks ───────────────────────────────────────────────────────
SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================================================
-- END OF MIGRATION
-- ================================================================================================
