CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','agent') NOT NULL DEFAULT 'agent',
  api_key VARCHAR(80) NOT NULL UNIQUE,
  daily_target INT NOT NULL DEFAULT 20,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_code VARCHAR(80) NOT NULL,
  title VARCHAR(255) NOT NULL,
  client_name VARCHAR(180) NOT NULL,
  brand VARCHAR(120) NOT NULL,
  priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  action_type ENUM('send','reply','update') NOT NULL DEFAULT 'reply',
  ticket_url TEXT NULL,
  notes TEXT NULL,
  counts INT NOT NULL DEFAULT 1,
  open_minutes INT NOT NULL DEFAULT 0,
  sla_status ENUM('normal','breach') NOT NULL DEFAULT 'normal',
  is_bookmarked TINYINT(1) NOT NULL DEFAULT 0,
  agent_user_id INT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY idx_tickets_created_at (created_at),
  KEY idx_tickets_agent (agent_user_id),
  CONSTRAINT fk_tickets_user FOREIGN KEY (agent_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (name, email, password_hash, role, api_key, daily_target, is_active, created_at)
VALUES ('Super Admin','admin@tickettracker.local','demo_fallback_password','admin','demo_admin_api_key_1234567890',25,1,NOW())
ON DUPLICATE KEY UPDATE email=email;

INSERT INTO settings (setting_key, setting_value) VALUES
('reply_button_selectors', 'button[type="submit"], .btn-reply, .btn-send, [data-action="reply"]'),
('ticket_id_selectors', 'input[name="ticket_id"], .ticket-id, [data-ticket-id]'),
('title_selectors', 'input[name="subject"], .ticket-subject, .subject'),
('client_selectors', 'input[name="client"], .client-name, .customer-name'),
('brand_selectors', 'select[name="brand"], .brand-name, [data-brand]'),
('priority_selectors', 'select[name="priority"], .priority, [data-priority]'),
('note_selectors', 'textarea[name="reply"], .reply-editor, .note-editor')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

INSERT INTO tickets (ticket_code, title, client_name, brand, priority, action_type, ticket_url, notes, counts, open_minutes, sla_status, agent_user_id, created_at, updated_at) VALUES
('LJT-734-84879','Astra Starter Template Issue','KroppShop Ltd.','Verpex','high','reply','https://ticketx.hellome.my.id/ticket/1','Template issue handled',1,45,'normal',1,NOW(),NOW()),
('NNP-915-28421','Email Settings','Angel Rubin','Verpex','medium','reply','https://ticketx.hellome.my.id/ticket/2','Mail settings updated',1,60,'normal',1,NOW(),NOW()),
('QSX-213-74154','cPanel link to user home not working inside whm','Glen Hoey','Verpex','urgent','reply','https://ticketx.hellome.my.id/ticket/3','Escalated follow-up',1,180,'breach',1,NOW(),NOW());