-- =====================================================================
-- Support Ticketing System — Database Schema
-- Built from: BRD_Support_Ticketing_Module.pdf v1.0 (Phase 1 scope)
-- Engine: MySQL 5.7+/MariaDB 10.3+, InnoDB, utf8mb4
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Internal users (agents / leads / admins) — stands in for the HR
-- Employee master (FR-SUP-110) until a real ERP integration exists.
-- ---------------------------------------------------------------------
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE, -- L1, L2, L3, LEAD, HEAD, FINANCE, ADMIN
  name VARCHAR(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_no VARCHAR(30) NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role_id INT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  out_of_office_until DATE NULL,
  in_app_alerts TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fine-grained, admin-editable permission grants per role (BRD section 9's permission
-- matrix, made configurable rather than hardcoded). ADMIN is not represented here — it
-- always passes every check in code (App\Core\Auth::can()), so the matrix can never be
-- configured into a state that locks every account out of the system. The canonical set
-- of permission keys lives in App\Core\Permissions::all(), not in this table.
CREATE TABLE role_permissions (
  role_id INT NOT NULL,
  permission_key VARCHAR(60) NOT NULL,
  PRIMARY KEY (role_id, permission_key),
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ERP masters this module consumes (built-in stand-ins per BRD 5.4 / 8)
-- ---------------------------------------------------------------------
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  code VARCHAR(30) NULL,
  industry VARCHAR(80) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  phone VARCHAR(40) NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  portal_access TINYINT(1) NOT NULL DEFAULT 0,
  password_hash VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  module VARCHAR(120) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contracts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  contract_no VARCHAR(40) NOT NULL,
  tier VARCHAR(30) NOT NULL, -- Standard / Gold / Platinum
  support_hours_total DECIMAL(8,2) NOT NULL DEFAULT 0,
  support_hours_used DECIMAL(8,2) NOT NULL DEFAULT 0,
  coverage_window VARCHAR(40) NOT NULL DEFAULT 'Business hours',
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contract_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contract_id INT NOT NULL,
  product_id INT NOT NULL,
  FOREIGN KEY (contract_id) REFERENCES contracts(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  UNIQUE KEY uniq_contract_product (contract_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Queues, teams, routing
-- ---------------------------------------------------------------------
CREATE TABLE teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE team_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  user_id INT NOT NULL,
  skills VARCHAR(255) NULL,
  FOREIGN KEY (team_id) REFERENCES teams(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY uniq_team_user (team_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  team_id INT NULL,
  description VARCHAR(255) NULL,
  auto_assign ENUM('none','round_robin','least_loaded') NOT NULL DEFAULT 'none',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE queue_routing_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  queue_id INT NOT NULL,
  product_id INT NULL,
  category_id INT NULL,
  customer_id INT NULL,
  contract_tier VARCHAR(30) NULL,
  priority_order INT NOT NULL DEFAULT 100,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (queue_id) REFERENCES queues(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Taxonomy: categories, priorities
-- ---------------------------------------------------------------------
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE priorities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE, -- P1..P4
  name VARCHAR(40) NOT NULL,
  definition VARCHAR(255) NOT NULL,
  color VARCHAR(20) NOT NULL DEFAULT '#64748b',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sub_statuses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(30) NOT NULL, -- which fixed status this sub-status belongs to
  name VARCHAR(60) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SLA: business calendars, holidays, policies, targets, escalation
-- ---------------------------------------------------------------------
CREATE TABLE business_calendars (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Dhaka',
  work_days VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5', -- ISO 1=Mon..7=Sun
  start_time TIME NOT NULL DEFAULT '09:00:00',
  end_time TIME NOT NULL DEFAULT '18:00:00',
  is_default TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holidays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  calendar_id INT NOT NULL,
  holiday_date DATE NOT NULL,
  name VARCHAR(120) NOT NULL,
  FOREIGN KEY (calendar_id) REFERENCES business_calendars(id),
  UNIQUE KEY uniq_calendar_date (calendar_id, holiday_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sla_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  contract_tier VARCHAR(30) NULL,
  product_id INT NULL,
  calendar_id INT NOT NULL,
  is_24x7 TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL,
  version INT NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (calendar_id) REFERENCES business_calendars(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sla_targets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sla_policy_id INT NOT NULL,
  priority_id INT NOT NULL,
  first_response_minutes INT NOT NULL,
  resolution_minutes INT NOT NULL,
  update_frequency_minutes INT NULL,
  warn_pct_1 INT NOT NULL DEFAULT 50,
  warn_pct_2 INT NOT NULL DEFAULT 80,
  FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id),
  FOREIGN KEY (priority_id) REFERENCES priorities(id),
  UNIQUE KEY uniq_policy_priority (sla_policy_id, priority_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE escalation_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sla_policy_id INT NOT NULL,
  level INT NOT NULL,
  trigger_pct INT NULL,        -- e.g. 80 = 80% of resolution target elapsed
  trigger_on_breach TINYINT(1) NOT NULL DEFAULT 0,
  notify_role VARCHAR(30) NOT NULL, -- ASSIGNEE, LEAD, HEAD, ACCOUNT_MANAGER
  action_note VARCHAR(255) NULL,
  FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- The ticket, and everything hung off it
-- ---------------------------------------------------------------------
CREATE TABLE tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_no VARCHAR(30) NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  contact_id INT NOT NULL,
  contract_id INT NULL,
  product_id INT NOT NULL,
  version VARCHAR(30) NULL,
  subject VARCHAR(200) NOT NULL,
  description MEDIUMTEXT NOT NULL,
  category_id INT NOT NULL,
  sub_category_id INT NULL,
  priority_id INT NOT NULL,
  severity VARCHAR(30) NOT NULL DEFAULT 'Minor impact',
  channel ENUM('Portal','Email','Telephone','Walk-in','Internal','API') NOT NULL,
  status ENUM('NEW','ASSIGNED','IN_PROGRESS','PENDING_CUSTOMER','PENDING_THIRD_PARTY','ESCALATED','RESOLVED','CLOSED','REOPENED','REJECTED') NOT NULL DEFAULT 'NEW',
  sub_status_id INT NULL,
  queue_id INT NULL,
  assignee_id INT NULL,
  sla_policy_id INT NULL,
  first_response_minutes INT NULL,   -- snapshot of the policy in force at creation
  resolution_minutes INT NULL,
  response_due_at DATETIME NULL,
  resolution_due_at DATETIME NULL,
  first_response_at DATETIME NULL,
  resolved_at DATETIME NULL,
  closed_at DATETIME NULL,
  sla_paused_seconds INT NOT NULL DEFAULT 0,
  paused_at DATETIME NULL,
  is_response_breached TINYINT(1) NOT NULL DEFAULT 0,
  is_resolution_breached TINYINT(1) NOT NULL DEFAULT 0,
  breach_justification VARCHAR(255) NULL,
  escalation_level INT NOT NULL DEFAULT 0,
  resolution_code VARCHAR(80) NULL,
  root_cause VARCHAR(120) NULL,
  is_out_of_entitlement TINYINT(1) NOT NULL DEFAULT 0,
  is_billable TINYINT(1) NOT NULL DEFAULT 0,
  linked_record_type VARCHAR(30) NULL,
  linked_record_ref VARCHAR(60) NULL,
  linked_record_status VARCHAR(30) NULL,
  parent_ticket_id INT NULL,
  reassignment_count INT NOT NULL DEFAULT 0,
  rejected_reason VARCHAR(255) NULL,
  reopen_count INT NOT NULL DEFAULT 0,
  csat_requested TINYINT(1) NOT NULL DEFAULT 0,
  created_by_user_id INT NULL,
  created_by_contact_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (contract_id) REFERENCES contracts(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (category_id) REFERENCES categories(id),
  FOREIGN KEY (priority_id) REFERENCES priorities(id),
  FOREIGN KEY (queue_id) REFERENCES queues(id),
  FOREIGN KEY (assignee_id) REFERENCES users(id),
  FOREIGN KEY (sla_policy_id) REFERENCES sla_policies(id),
  INDEX idx_status (status),
  INDEX idx_assignee (assignee_id),
  INDEX idx_customer (customer_id),
  INDEX idx_resolution_due (resolution_due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ticket_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT NULL,
  contact_id INT NULL,
  type ENUM('public_reply','internal_note','inbound_email','system') NOT NULL,
  body MEDIUMTEXT NOT NULL,
  kb_article_id INT NULL,
  is_from_canned TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  INDEX idx_ticket_created (ticket_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ticket_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  ticket_message_id INT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  size_bytes INT NOT NULL,
  mime_type VARCHAR(120) NULL,
  uploaded_by_user_id INT NULL,
  uploaded_by_contact_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  FOREIGN KEY (ticket_message_id) REFERENCES ticket_messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ticket_status_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  from_status VARCHAR(30) NULL,
  to_status VARCHAR(30) NOT NULL,
  changed_by_user_id INT NULL,
  changed_by_contact_id INT NULL,
  reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Append-only audit trail: every field change, assignment and comms event (FR-SUP-018)
CREATE TABLE ticket_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT NULL,
  action VARCHAR(60) NOT NULL,
  field_name VARCHAR(60) NULL,
  old_value VARCHAR(500) NULL,
  new_value VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sla_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  event_type VARCHAR(30) NOT NULL, -- start, pause, resume, warn_50, warn_80, response_breach, resolution_breach, escalate, met
  event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  meta VARCHAR(255) NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE escalation_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  level INT NOT NULL,
  notified VARCHAR(255) NULL,
  reason VARCHAR(255) NULL,
  created_by_user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE watchers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY uniq_ticket_watcher (ticket_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Knowledge base
-- ---------------------------------------------------------------------
CREATE TABLE kb_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES kb_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE kb_articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  product_id INT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  body MEDIUMTEXT NOT NULL,
  keywords VARCHAR(255) NULL,
  visibility ENUM('internal','customer') NOT NULL DEFAULT 'internal',
  status ENUM('draft','review','published','retired') NOT NULL DEFAULT 'draft',
  views INT NOT NULL DEFAULT 0,
  helpful_yes INT NOT NULL DEFAULT 0,
  helpful_no INT NOT NULL DEFAULT 0,
  version INT NOT NULL DEFAULT 1,
  author_id INT NULL,
  published_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES kb_categories(id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FULLTEXT KEY ft_title_body_keywords (title, body, keywords)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE kb_article_versions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kb_article_id INT NOT NULL,
  version INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  edited_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kb_article_id) REFERENCES kb_articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE kb_article_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kb_article_id INT NOT NULL,
  ticket_id INT NULL,
  action ENUM('suggested','viewed','deflected','used_in_reply') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kb_article_id) REFERENCES kb_articles(id),
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Time / effort / billing
-- ---------------------------------------------------------------------
CREATE TABLE billing_summaries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  total_minutes INT NOT NULL DEFAULT 0,
  total_billable_minutes INT NOT NULL DEFAULT 0,
  status ENUM('draft','posted') NOT NULL DEFAULT 'draft',
  sales_invoice_ref VARCHAR(60) NULL,
  posted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE time_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT NOT NULL,
  log_date DATE NOT NULL,
  duration_minutes INT NOT NULL,
  activity_type VARCHAR(60) NOT NULL,
  description VARCHAR(500) NULL,
  is_billable TINYINT(1) NOT NULL DEFAULT 0,
  billable_reason VARCHAR(255) NULL,
  billing_summary_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (billing_summary_id) REFERENCES billing_summaries(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- CSAT
-- ---------------------------------------------------------------------
CREATE TABLE csat_responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL UNIQUE,
  rating TINYINT NOT NULL,
  comment VARCHAR(1000) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Notifications
-- ---------------------------------------------------------------------
CREATE TABLE notification_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_code VARCHAR(60) NOT NULL,
  channel ENUM('email','in_app') NOT NULL DEFAULT 'email',
  language VARCHAR(10) NOT NULL DEFAULT 'en',
  subject VARCHAR(200) NULL,
  body MEDIUMTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_event_channel_lang (event_code, channel, language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notification_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NULL,
  event_code VARCHAR(60) NOT NULL,
  channel VARCHAR(20) NOT NULL,
  recipient VARCHAR(160) NOT NULL,
  subject VARCHAR(200) NULL,
  body MEDIUMTEXT NULL,
  status ENUM('sent','failed','queued') NOT NULL DEFAULT 'queued',
  error VARCHAR(500) NULL,
  retry_count INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE in_app_notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  ticket_id INT NULL,
  message VARCHAR(255) NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE canned_responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  category_id INT NULL,
  created_by INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Mail ingest quarantine (FR-SUP-006)
-- ---------------------------------------------------------------------
CREATE TABLE mail_quarantine (
  id INT AUTO_INCREMENT PRIMARY KEY,
  from_address VARCHAR(160) NOT NULL,
  subject VARCHAR(255) NULL,
  body MEDIUMTEXT NULL,
  reason VARCHAR(255) NOT NULL,
  raw_message_id VARCHAR(255) NULL,
  status ENUM('pending','resolved','discarded') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Admin / config audit + generic settings
-- ---------------------------------------------------------------------
CREATE TABLE admin_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(60) NOT NULL,
  entity_id INT NULL,
  details VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  key_name VARCHAR(80) PRIMARY KEY,
  value VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;