-- ============================================
--  LiveChat Pro - Database Schema
--  Compatible: MySQL 5.7+ / MariaDB 10.3+
-- ============================================

CREATE DATABASE IF NOT EXISTS `livechat_pro`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `livechat_pro`;

-- ============================================
--  TABEL: operators (admin/agent)
-- ============================================
CREATE TABLE IF NOT EXISTS `operators` (
  `id`           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(100)    NOT NULL,
  `email`        VARCHAR(150)    NOT NULL UNIQUE,
  `password`     VARCHAR(255)    NOT NULL,
  `avatar`       VARCHAR(255)    DEFAULT NULL,
  `status`       ENUM('online','away','offline') DEFAULT 'offline',
  `role`         ENUM('admin','agent') DEFAULT 'agent',
  `last_seen`    DATETIME        DEFAULT NULL,
  `created_at`   DATETIME        DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: chat_sessions (setiap sesi visitor)
-- ============================================
CREATE TABLE IF NOT EXISTS `chat_sessions` (
  `id`              VARCHAR(64)     NOT NULL,           -- UUID session
  `widget_id`       VARCHAR(64)     NOT NULL,           -- ID widget/site
  `visitor_name`    VARCHAR(100)    DEFAULT 'Visitor',
  `visitor_email`   VARCHAR(150)    DEFAULT NULL,
  `visitor_ip`      VARCHAR(45)     DEFAULT NULL,
  `visitor_ua`      TEXT            DEFAULT NULL,       -- User-Agent
  `page_url`        VARCHAR(500)    DEFAULT NULL,       -- Halaman saat chat
  `operator_id`     INT UNSIGNED    DEFAULT NULL,
  `status`          ENUM('waiting','active','closed') DEFAULT 'waiting',
  `rating`          TINYINT         DEFAULT NULL,       -- 1-5
  `rating_comment`  TEXT            DEFAULT NULL,
  `started_at`      DATETIME        DEFAULT CURRENT_TIMESTAMP,
  `closed_at`       DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_widget`    (`widget_id`),
  KEY `idx_operator`  (`operator_id`),
  KEY `idx_status`    (`status`),
  KEY `idx_started`   (`started_at`),
  CONSTRAINT `fk_session_operator`
    FOREIGN KEY (`operator_id`) REFERENCES `operators`(`id`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: messages
-- ============================================
CREATE TABLE IF NOT EXISTS `messages` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id`  VARCHAR(64)     NOT NULL,
  `sender`      ENUM('visitor','operator','system') NOT NULL,
  `sender_id`   INT UNSIGNED    DEFAULT NULL,           -- operator id jika pengirim operator
  `message`     TEXT            NOT NULL,
  `type`        ENUM('text','image','file','system') DEFAULT 'text',
  `file_url`    VARCHAR(500)    DEFAULT NULL,
  `is_read`     TINYINT(1)      DEFAULT 0,
  `sent_at`     DATETIME(3)     DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_session`   (`session_id`),
  KEY `idx_sent`      (`sent_at`),
  CONSTRAINT `fk_msg_session`
    FOREIGN KEY (`session_id`) REFERENCES `chat_sessions`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: widgets (multi-site support)
-- ============================================
CREATE TABLE IF NOT EXISTS `widgets` (
  `id`              VARCHAR(64)     NOT NULL,           -- widget key/ID
  `name`            VARCHAR(100)    NOT NULL,
  `domain`          VARCHAR(255)    DEFAULT NULL,
  `greeting`        TEXT            DEFAULT 'Halo! Ada yang bisa kami bantu? 👋',
  `color_primary`   VARCHAR(7)      DEFAULT '#6366f1',
  `color_secondary` VARCHAR(7)      DEFAULT '#4f46e5',
  `position`        ENUM('bottom-right','bottom-left','top-right','top-left') DEFAULT 'bottom-right',
  `auto_open`       TINYINT(1)      DEFAULT 0,
  `auto_open_delay` INT             DEFAULT 3,          -- detik
  `offline_msg`     TEXT            DEFAULT 'Kami sedang offline. Tinggalkan pesan Anda!',
  `is_active`       TINYINT(1)      DEFAULT 1,
  `created_at`      DATETIME        DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: quick_replies (template balasan cepat)
-- ============================================
CREATE TABLE IF NOT EXISTS `quick_replies` (
  `id`          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `operator_id` INT UNSIGNED    DEFAULT NULL,           -- NULL = global
  `shortcut`    VARCHAR(50)     NOT NULL,
  `message`     TEXT            NOT NULL,
  `created_at`  DATETIME        DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_operator` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: typing_status (polling-based)
-- ============================================
CREATE TABLE IF NOT EXISTS `typing_status` (
  `session_id`  VARCHAR(64)     NOT NULL,
  `sender`      ENUM('visitor','operator') NOT NULL,
  `updated_at`  DATETIME(3)     DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`session_id`, `sender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  TABEL: poll_state (long-polling / SSE state)
-- ============================================
CREATE TABLE IF NOT EXISTS `poll_state` (
  `session_id`    VARCHAR(64)   NOT NULL,
  `last_msg_id`   BIGINT        DEFAULT 0,
  `updated_at`    DATETIME(3)   DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
--  DATA AWAL (SEED)
-- ============================================

-- Default operator (password: admin123)
INSERT INTO `operators` (`name`, `email`, `password`, `status`, `role`) VALUES
('Super Admin', 'admin@livechat.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'online', 'admin'),
('Agent Andi',  'andi@livechat.com',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'online', 'agent');

-- Default widget
INSERT INTO `widgets` (`id`, `name`, `domain`, `greeting`) VALUES
('widget_demo_001', 'Demo Website', 'localhost', 'Halo! Ada yang bisa kami bantu hari ini? 😊'),
('widget_demo_002', 'Toko Online', 'myshop.com', 'Selamat datang di toko kami! Ada yang bisa kami bantu? 🛍️');

-- Quick replies
INSERT INTO `quick_replies` (`operator_id`, `shortcut`, `message`) VALUES
(NULL, '/hi',    'Halo! Selamat datang. Ada yang bisa saya bantu?'),
(NULL, '/bye',   'Terima kasih sudah menghubungi kami. Semoga harimu menyenangkan! 😊'),
(NULL, '/wait',  'Mohon tunggu sebentar, saya sedang memeriksa informasinya untuk Anda.'),
(NULL, '/done',  'Masalahnya sudah selesai. Apakah ada yang ingin Anda tanyakan lagi?');