-- ============================================
--  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: 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: 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;

-- 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?');
