

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 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());
