-- ใช้คำนำหน้า queue_ ทั้งหมด
-- =========================================
-- CORE MASTER
-- =========================================
CREATE TABLE queue_company (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_branch (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(company_id, code),
  INDEX(company_id),
  CONSTRAINT fk_queue_branch_company FOREIGN KEY (company_id) REFERENCES queue_company(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_site (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  branch_id BIGINT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  timezone VARCHAR(50) DEFAULT 'Asia/Bangkok',
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(branch_id, code),
  INDEX(branch_id),
  CONSTRAINT fk_queue_site_branch FOREIGN KEY (branch_id) REFERENCES queue_branch(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_site_whitelist (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  source_site_id BIGINT NOT NULL,
  allowed_site_id BIGINT NOT NULL,
  UNIQUE(source_site_id, allowed_site_id),
  CONSTRAINT fk_queue_sw_src FOREIGN KEY (source_site_id) REFERENCES queue_site(id),
  CONSTRAINT fk_queue_sw_dst FOREIGN KEY (allowed_site_id) REFERENCES queue_site(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- KIOSK / ROOM
-- =========================================
CREATE TABLE queue_kiosk (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  site_id BIGINT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(site_id, code),
  INDEX(site_id),
  CONSTRAINT fk_queue_kiosk_site FOREIGN KEY (site_id) REFERENCES queue_site(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_room (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  site_id BIGINT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  capacity INT DEFAULT 1,
  call_policy ENUM('new_only','recall_allowed') DEFAULT 'recall_allowed',
  max_queue_length INT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(site_id, code),
  INDEX(site_id),
  CONSTRAINT fk_queue_room_site FOREIGN KEY (site_id) REFERENCES queue_site(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_room_service_skill (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  room_id BIGINT NOT NULL,
  service_key VARCHAR(50) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(room_id, service_key),
  INDEX(service_key),
  CONSTRAINT fk_queue_skill_room FOREIGN KEY (room_id) REFERENCES queue_room(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- FLOW / STEP
-- =========================================
CREATE TABLE queue_flow_definition (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  site_id BIGINT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE(site_id, code),
  INDEX(site_id),
  CONSTRAINT fk_queue_flow_site FOREIGN KEY (site_id) REFERENCES queue_site(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_flow_step (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  flow_id BIGINT NOT NULL,
  step_order INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  service_key VARCHAR(50) NULL,
  room_id BIGINT NULL,
  auto_assign_strategy ENUM('least_load','round_robin','fixed') DEFAULT 'least_load',
  sla_seconds INT NULL,
  INDEX(flow_id, step_order),
  CONSTRAINT fk_queue_step_flow FOREIGN KEY (flow_id) REFERENCES queue_flow_definition(id),
  CONSTRAINT fk_queue_step_room FOREIGN KEY (room_id) REFERENCES queue_room(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_flow_transition (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  from_step_id BIGINT NOT NULL,
  to_step_id BIGINT NOT NULL,
  condition_key VARCHAR(50) NULL,
  condition_val VARCHAR(50) NULL,
  INDEX(from_step_id),
  CONSTRAINT fk_queue_tr_from FOREIGN KEY (from_step_id) REFERENCES queue_flow_step(id),
  CONSTRAINT fk_queue_tr_to FOREIGN KEY (to_step_id) REFERENCES queue_flow_step(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================
-- TICKET
-- =========================================
CREATE TABLE queue_ticket (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  site_id BIGINT NOT NULL,
  kiosk_id BIGINT NULL,
  flow_id BIGINT NOT NULL,
  current_step_id BIGINT NULL,
  service_key VARCHAR(50) NULL,
  ticket_date DATE NOT NULL,
  ticket_no VARCHAR(20) NOT NULL,
  status ENUM('waiting','queued','serving','paused','skipped','completed','cancelled') NOT NULL DEFAULT 'waiting',
  created_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  qr_uid VARCHAR(64) NULL,
  counter_label VARCHAR(50) NULL,
  UNIQUE(site_id, ticket_date, ticket_no),
  INDEX(site_id, status),
  INDEX(current_step_id),
  CONSTRAINT fk_queue_ticket_site FOREIGN KEY (site_id) REFERENCES queue_site(id),
  CONSTRAINT fk_queue_ticket_kiosk FOREIGN KEY (kiosk_id) REFERENCES queue_kiosk(id),
  CONSTRAINT fk_queue_ticket_flow FOREIGN KEY (flow_id) REFERENCES queue_flow_definition(id),
  CONSTRAINT fk_queue_ticket_step FOREIGN KEY (current_step_id) REFERENCES queue_flow_step(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_ticket_step_assignment (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  ticket_id BIGINT NOT NULL,
  step_id BIGINT NOT NULL,
  assigned_room BIGINT NOT NULL,
  assigned_at DATETIME NOT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  status ENUM('queued','serving','done','skipped','cancelled') DEFAULT 'queued',
  INDEX(ticket_id, step_id),
  INDEX(assigned_room, status),
  CONSTRAINT fk_queue_tsa_ticket FOREIGN KEY (ticket_id) REFERENCES queue_ticket(id),
  CONSTRAINT fk_queue_tsa_step FOREIGN KEY (step_id) REFERENCES queue_flow_step(id),
  CONSTRAINT fk_queue_tsa_room FOREIGN KEY (assigned_room) REFERENCES queue_room(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_ticket_event (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  ticket_id BIGINT NOT NULL,
  event_type ENUM('created','moved','assigned','called','served','paused','resumed','skipped','completed','cancelled') NOT NULL,
  from_step_id BIGINT NULL,
  to_step_id BIGINT NULL,
  room_id BIGINT NULL,
  by_user_id BIGINT NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX(ticket_id, created_at),
  CONSTRAINT fk_queue_evt_ticket FOREIGN KEY (ticket_id) REFERENCES queue_ticket(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_ticket_sequence (
  site_id BIGINT NOT NULL,
  ticket_date DATE NOT NULL,
  prefix VARCHAR(10) NOT NULL DEFAULT 'A',
  last_seq INT NOT NULL DEFAULT 0,
  PRIMARY KEY (site_id, ticket_date, prefix),
  CONSTRAINT fk_queue_seq_site FOREIGN KEY (site_id) REFERENCES queue_site(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_plan (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(50) UNIQUE NOT NULL, -- FREE/PRO/BIZ
  name VARCHAR(100) NOT NULL,
  price_month DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_feature (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(50) UNIQUE NOT NULL, -- e.g. ADS_OFF, REPORT_XLSX, PREORDER
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_plan_feature (
  plan_id BIGINT NOT NULL,
  feature_id BIGINT NOT NULL,
  PRIMARY KEY(plan_id, feature_id),
  FOREIGN KEY(plan_id) REFERENCES queue_plan(id),
  FOREIGN KEY(feature_id) REFERENCES queue_feature(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_company_plan (
  company_id BIGINT NOT NULL,
  plan_id BIGINT NOT NULL,
  started_at DATETIME NOT NULL,
  expired_at DATETIME NULL,
  PRIMARY KEY(company_id, plan_id, started_at),
  FOREIGN KEY(company_id) REFERENCES queue_company(id),
  FOREIGN KEY(plan_id) REFERENCES queue_plan(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;