CREATE DATABASE IF NOT EXISTS chatbox CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE chatbox;
CREATE TABLE users(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',status ENUM('pending','active','expired','suspended') NOT NULL DEFAULT 'pending',active_until DATETIME NULL,display_name VARCHAR(190) NULL,profile_picture VARCHAR(500) NULL,phone VARCHAR(50) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_role_status(role,status));
CREATE TABLE global_settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value TEXT NULL,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE admin_settings(admin_id BIGINT UNSIGNED PRIMARY KEY,chat_enabled TINYINT(1) NOT NULL DEFAULT 1,display_name VARCHAR(190) NULL,profile_picture VARCHAR(500) NULL,phone VARCHAR(50) NULL,color VARCHAR(30) NOT NULL DEFAULT '#2563eb',position ENUM('left','right') NOT NULL DEFAULT 'right',greeting_text TEXT NULL,greeting_delay INT NOT NULL DEFAULT 5,email_notifications TINYINT(1) NOT NULL DEFAULT 1,FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE customers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id BIGINT UNSIGNED NOT NULL,visitor_cookie VARCHAR(190) NOT NULL,name VARCHAR(190) NULL,email VARCHAR(190) NULL,phone VARCHAR(50) NULL,website_url VARCHAR(500) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_admin_cookie(admin_id,visitor_cookie),INDEX idx_customer_admin(admin_id,last_seen_at),FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE messages(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id BIGINT UNSIGNED NOT NULL,customer_id BIGINT UNSIGNED NOT NULL,sender_type ENUM('customer','admin') NOT NULL,message TEXT NOT NULL,is_read TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_chat(admin_id,customer_id,id),FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE);
CREATE TABLE auto_messages(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id BIGINT UNSIGNED NOT NULL,message TEXT NOT NULL,delay_seconds INT NOT NULL DEFAULT 5,sort_order INT NOT NULL DEFAULT 0,FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE impersonation_tokens(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,token_hash CHAR(64) NOT NULL UNIQUE,admin_id BIGINT UNSIGNED NOT NULL,expires_at DATETIME NOT NULL,used_at DATETIME NULL,FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE);
INSERT INTO global_settings(setting_key,setting_value) VALUES('dashboard_background','#f4f7fb'),('embed_base_url','http://localhost/chatbox/public'),('notification_sound',''),('announcement','') ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);