-- =========================================================
-- 044_spk_monitor_foundation.sql
-- Sprint 17: SPK monitor + terminal foundation
-- =========================================================

CREATE TABLE IF NOT EXISTS terminal_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  production_department_id BIGINT UNSIGNED NULL,
  terminal_type ENUM('monitor','department') NOT NULL DEFAULT 'monitor',
  username VARCHAR(80) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  pin_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_terminal_accounts_location_username (location_id, username),
  KEY idx_terminal_accounts_location_type_active (location_id, terminal_type, is_active),
  KEY idx_terminal_accounts_department (production_department_id),
  CONSTRAINT fk_terminal_accounts_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_terminal_accounts_department FOREIGN KEY (production_department_id) REFERENCES production_departments(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS terminal_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  terminal_account_id BIGINT UNSIGNED NOT NULL,
  session_token_hash VARCHAR(255) NOT NULL,
  user_agent VARCHAR(255) NULL,
  ip_address VARCHAR(64) NULL,
  expires_at DATETIME NOT NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_terminal_sessions_token_hash (session_token_hash),
  KEY idx_terminal_sessions_terminal_expiry (terminal_account_id, expires_at),
  CONSTRAINT fk_terminal_sessions_account FOREIGN KEY (terminal_account_id) REFERENCES terminal_accounts(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS spk_status_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  spk_id BIGINT UNSIGNED NOT NULL,
  department_id BIGINT UNSIGNED NULL,
  event_type ENUM('department_started','department_finished','spk_status_changed','handover') NOT NULL,
  from_status VARCHAR(32) NULL,
  to_status VARCHAR(32) NULL,
  spk_no_snapshot VARCHAR(60) NOT NULL,
  customer_name_snapshot VARCHAR(180) NULL,
  department_name_snapshot VARCHAR(120) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  actor_name_snapshot VARCHAR(120) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_spk_status_events_location_created (location_id, created_at),
  KEY idx_spk_status_events_spk_created (spk_id, created_at),
  KEY idx_spk_status_events_department_created (department_id, created_at),
  KEY idx_spk_status_events_type_created (event_type, created_at),
  CONSTRAINT fk_spk_status_events_spk FOREIGN KEY (spk_id) REFERENCES spk(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_spk_status_events_department FOREIGN KEY (department_id) REFERENCES production_departments(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;