-- ============================================
-- LIVE CHAT MULTI-DOMAIN (VERSI PHP) - SCHEMA
-- ============================================

CREATE TABLE IF NOT EXISTS sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_key VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  domain VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS visitors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  external_user_id VARCHAR(100) DEFAULT NULL,
  username VARCHAR(100) DEFAULT NULL,
  guest_label VARCHAR(50) DEFAULT NULL,
  status ENUM('open','closed') DEFAULT 'open',
  last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  visitor_id INT NOT NULL,
  sender ENUM('visitor','admin') NOT NULL,
  admin_name VARCHAR(50) DEFAULT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- CONTOH DATA (sesuaikan domain & site_key lo)
-- ============================================
INSERT INTO sites (site_key, name, domain) VALUES
('sk_alexatoto_9f2a7c1e4b', 'ALEXATOTO', 'alexatoto.shop'),
('sk_ratubet378_7d3e9a2c8f', 'RATUBET378', 'ratubet378.shop'),
('sk_oscar4d_1b6f4e8d2a', 'OSCAR4D', 'vip2.it.com');