-- schema.sql

CREATE DATABASE IF NOT EXISTS livechat_support;
USE livechat_support;

-- Tickets Table
CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    status ENUM('open', 'pending', 'closed') DEFAULT 'open',
    unread_admin TINYINT(1) DEFAULT 1 COMMENT '1 = Admin has unread messages',
    unread_user TINYINT(1) DEFAULT 0 COMMENT '1 = User has unread messages',
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Messages Table
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    sender_type ENUM('user', 'admin', 'auto') NOT NULL,
    message_type ENUM('text', 'image', 'video') DEFAULT 'text',
    message TEXT,
    attachment_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);

-- Auto Responses Table
CREATE TABLE IF NOT EXISTS auto_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trigger_keyword VARCHAR(100),
    response_text TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1
);

-- Insert Default Auto Responses
INSERT INTO auto_responses (trigger_keyword, response_text) VALUES 
('refund', 'For refund inquiries, please provide your Order ID. Our billing team will review it shortly.'),
('price', 'You can check our pricing page for detailed information on our plans.'),
('hello', 'Hi there! How can we help you today?'),
('help', 'Sure, let us know what issue you are facing.'),
('payment', 'We accept Credit Cards, PayPal, and Bank Transfer.');
