-- V9 schema (messages.file_id as TEXT)

CREATE TABLE IF NOT EXISTS processed_updates (
  update_id BIGINT PRIMARY KEY,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  username VARCHAR(64) NULL,
  first_name VARCHAR(128) NULL,
  last_name VARCHAR(128) NULL,
  language VARCHAR(8) DEFAULT 'en',
  language_selected TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name_en VARCHAR(128) NOT NULL,
  name_fa VARCHAR(128) NULL,
  support_chat_id BIGINT NULL,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL,
  role ENUM('manager','agent') NOT NULL DEFAULT 'agent',
  department_id INT NULL,
  UNIQUE KEY uniq_admin (telegram_id, department_id),
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid VARCHAR(32) NOT NULL UNIQUE,
  user_id INT NOT NULL,
  user_telegram_id BIGINT NOT NULL,
  department_id INT NOT NULL,
  assigned_admin_id BIGINT NULL,
  status ENUM('open','answered','waiting_agent','closed') NOT NULL DEFAULT 'open',
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  last_message_by ENUM('user','agent','system') DEFAULT 'user',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT,
  INDEX (department_id),
  INDEX (user_telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  sender_type ENUM('user','agent','system') NOT NULL,
  sender_telegram_id BIGINT NULL,
  text MEDIUMTEXT NULL,
  file_type ENUM('photo','video','document','voice','audio') NULL,
  file_id TEXT NULL,
  is_agent_reply TINYINT(1) DEFAULT 0,
  is_read_by_user TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  INDEX (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  telegram_id BIGINT PRIMARY KEY,
  mode VARCHAR(64) NOT NULL,
  data_json JSON NULL,
  expires_at DATETIME NOT NULL,
  INDEX (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  admin_telegram_id BIGINT NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO departments (name_en, name_fa, support_chat_id, sort_order) VALUES
('General Support', 'پشتیبانی عمومی', NULL, 1),
('Billing', 'مالی', NULL, 2);

INSERT INTO admin_users (telegram_id, role, department_id) VALUES (123456789, 'manager', NULL);