-- ============================================================
-- EduTrack — Canteen Module Migration
-- File:    database/migrations/canteen_module.sql
-- Idempotent: safe to run multiple times (IF NOT EXISTS / IF EXISTS)
-- Run:     SOURCE database/migrations/canteen_module.sql
-- Verify:  SHOW TABLES LIKE 'canteen%';
-- ============================================================

-- ── Columns added to existing tables ────────────────────────

ALTER TABLE students
  ADD COLUMN IF NOT EXISTS identity_qr_token VARCHAR(128) UNIQUE,
  ADD COLUMN IF NOT EXISTS identity_qr_generated_at DATETIME;

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS recorded_via
    ENUM('standard','express_scanner','ai_agent','import','canteen_bulk')
    DEFAULT 'standard';

-- ── canteen_settings ─────────────────────────────────────────

CREATE TABLE IF NOT EXISTS canteen_settings (
  id                              INT AUTO_INCREMENT PRIMARY KEY,
  school_id                       INT NOT NULL DEFAULT 1,
  qr_enabled                      TINYINT(1) DEFAULT 1,
  fingerprint_enabled             TINYINT(1) DEFAULT 0,
  facial_recognition_enabled      TINYINT(1) DEFAULT 0,
  manual_id_fallback              TINYINT(1) DEFAULT 1,
  qr_validity_hours               INT DEFAULT 24,
  cook_pin_hash                   VARCHAR(255),
  scanner_pin_hash                VARCHAR(255),
  auto_ai_report                  TINYINT(1) DEFAULT 1,
  cook_dashboard_refresh_seconds  INT DEFAULT 30,
  hardware_fingerprint_model      VARCHAR(100),
  hardware_webcam_model           VARCHAR(100),
  facial_min_confidence           DECIMAL(3,2) DEFAULT 0.85,
  setup_completed                 TINYINT(1) DEFAULT 0,
  setup_completed_at              DATETIME,
  updated_at                      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_school (school_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_sessions ─────────────────────────────────────────

CREATE TABLE IF NOT EXISTS canteen_sessions (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  school_id        INT DEFAULT 1,
  session_name     VARCHAR(50) NOT NULL,
  session_date     DATE NOT NULL,
  start_time       TIME,
  end_time         TIME,
  price_per_meal   DECIMAL(10,2),
  classes_enrolled JSON,
  total_enrolled   INT DEFAULT 0,
  status           ENUM('scheduled','active','closed','cancelled') DEFAULT 'scheduled',
  created_by       INT,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date   (session_date),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_entitlements ──────────────────────────────────────

CREATE TABLE IF NOT EXISTS canteen_entitlements (
  id                    INT AUTO_INCREMENT PRIMARY KEY,
  student_id            INT NOT NULL,
  session_id            INT NOT NULL,
  fee_payment_id        INT,
  qr_token              VARCHAR(128) UNIQUE NOT NULL,
  qr_token_expires_at   DATETIME NOT NULL,
  status                ENUM('pending','confirmed','absent','cancelled') DEFAULT 'pending',
  verification_method   ENUM('qr','fingerprint','facial','manual_id'),
  confirmed_at          DATETIME,
  confirmed_by          INT,
  device_info           VARCHAR(255),
  offline_queued        TINYINT(1) DEFAULT 0,
  offline_synced_at     DATETIME,
  created_at            DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_student_session (student_id, session_id),
  INDEX idx_token   (qr_token),
  INDEX idx_session (session_id),
  INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_scans (immutable audit log) ──────────────────────

CREATE TABLE IF NOT EXISTS canteen_scans (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  entitlement_id      INT,
  student_id          INT NOT NULL,
  session_id          INT NOT NULL,
  scan_result         ENUM('success','already_confirmed','invalid_token',
                           'expired_token','not_enrolled','manual_override') NOT NULL,
  override_reason     VARCHAR(50),
  verification_method ENUM('qr','fingerprint','facial','manual_id'),
  confidence_score    DECIMAL(4,3),
  scanned_by          INT NOT NULL,
  scanned_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  device_info         VARCHAR(255),
  ip_address          VARCHAR(45),
  INDEX idx_session_date (session_id, scanned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_biometric_credentials ────────────────────────────

CREATE TABLE IF NOT EXISTS canteen_biometric_credentials (
  id                        INT AUTO_INCREMENT PRIMARY KEY,
  student_id                INT NOT NULL UNIQUE,
  credential_id             VARCHAR(512),
  public_key                TEXT,
  facial_descriptor         JSON,
  facial_enrolled_at        DATETIME,
  fingerprint_enrolled_at   DATETIME,
  enrolled_by               INT,
  last_updated              DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_biometric_access_log (GDPR/DPA compliance) ───────

CREATE TABLE IF NOT EXISTS canteen_biometric_access_log (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  student_id  INT NOT NULL,
  accessed_by INT NOT NULL,
  action      ENUM('enroll','verify','delete','export') NOT NULL,
  method      ENUM('fingerprint','facial'),
  result      ENUM('success','failure','fallback'),
  accessed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_student  (student_id),
  INDEX idx_accessed (accessed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── canteen_offline_queue (IndexedDB sync target) ────────────

CREATE TABLE IF NOT EXISTS canteen_offline_queue (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  device_token VARCHAR(128),
  payload      JSON NOT NULL,
  synced       TINYINT(1) DEFAULT 0,
  synced_at    DATETIME,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Seed initial settings row ─────────────────────────────────

INSERT IGNORE INTO canteen_settings (school_id) VALUES (1);
