-- ══════════════════════════════════════════
--  SUPPORT SYSTEM — MySQL Database Schema
--  Import this SQL to create all tables
-- ══════════════════════════════════════════

-- 1. Departments
CREATE TABLE IF NOT EXISTS support_departments (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    description     TEXT DEFAULT '',
    image_url       VARCHAR(500) DEFAULT '',
    is_active       TINYINT(1) DEFAULT 1,
    sort_order      INT DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Conversations (Tickets)
CREATE TABLE IF NOT EXISTS support_conversations (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id       VARCHAR(50) NOT NULL UNIQUE,
    department_id   INT DEFAULT NULL,
    department_name VARCHAR(255) DEFAULT '',
    department_image VARCHAR(500) DEFAULT '',
    subject         VARCHAR(500) DEFAULT '',
    status          ENUM('open','in_progress','waiting_user','resolved','closed') DEFAULT 'open',
    priority        ENUM('low','medium','high','urgent') DEFAULT 'medium',
    category        VARCHAR(100) DEFAULT 'general',
    created_by      VARCHAR(255) NOT NULL,
    user_name       VARCHAR(255) DEFAULT '',
    user_email      VARCHAR(255) DEFAULT '',
    user_phone      VARCHAR(50) DEFAULT '',
    user_photo      VARCHAR(500) DEFAULT '',
    assigned_admin_id INT DEFAULT NULL,
    admin_name      VARCHAR(255) DEFAULT NULL,
    unread_user     INT DEFAULT 0,
    unread_admin    INT DEFAULT 0,
    last_message    TEXT DEFAULT NULL,
    last_message_at DATETIME DEFAULT NULL,
    last_message_by VARCHAR(50) DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at       DATETIME DEFAULT NULL,
    INDEX idx_created_by (created_by),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_last_message_at (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Messages
CREATE TABLE IF NOT EXISTS support_messages (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT NOT NULL,
    sender_type     ENUM('user','admin') DEFAULT 'user',
    sender_id       VARCHAR(255) DEFAULT '',
    sender_name     VARCHAR(255) DEFAULT '',
    sender_photo    VARCHAR(500) DEFAULT NULL,
    message         TEXT DEFAULT '',
    message_type    ENUM('text','image','audio','video','file') DEFAULT 'text',
    attachment_url  VARCHAR(500) DEFAULT NULL,
    is_read         TINYINT(1) DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_conversation_id (conversation_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Canned Responses
CREATE TABLE IF NOT EXISTS support_canned_responses (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    message         TEXT NOT NULL,
    category        VARCHAR(100) DEFAULT 'general',
    shortcode       VARCHAR(50) DEFAULT NULL,
    usage_count     INT DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. App Settings (key-value)
CREATE TABLE IF NOT EXISTS support_app_settings (
    setting_key     VARCHAR(100) PRIMARY KEY,
    setting_value   TEXT DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Admins
CREATE TABLE IF NOT EXISTS support_admins (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(100) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    display_name    VARCHAR(255) DEFAULT '',
    email           VARCHAR(255) DEFAULT '',
    photo_url       VARCHAR(500) DEFAULT '',
    is_active       TINYINT(1) DEFAULT 1,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ══════════════════════════════════════════
--  DEFAULT DATA
-- ══════════════════════════════════════════

-- Default Departments
INSERT INTO support_departments (name, description, image_url, is_active, sort_order) VALUES
('Deposit Manager',     'Contact for deposit related issues',     '', 1, 1),
('Withdrawal Manager',  'Contact for withdrawal related issues',  '', 1, 2),
('Technical Support',   'App issues, bugs, and crashes',          '', 1, 3),
('General Inquiry',     'Features requests and other questions',  '', 1, 4);

-- Default Canned Responses
INSERT INTO support_canned_responses (title, shortcode, message, category) VALUES
('Greeting',        'hi',       'Hello! How can I help you today?', 'General'),
('Ticket Received', 'received', 'We have received your ticket and are looking into it. Please allow us some time to investigate.', 'Status'),
('Resolution',      'fixed',    'The issue has been resolved. Please update your app and try again. Let us know if you face any further issues.', 'Resolution'),
('Closing Ticket',  'bye',      'Since we haven''t heard from you in a while, we are closing this ticket. Feel free to open a new one if you need more help. Have a great day!', 'Closing');

-- Default App Settings
INSERT INTO support_app_settings (setting_key, setting_value) VALUES
('admin_key',          'FWE_SUPPORT_ADMIN_2024_SECRET'),
('version',            '1'),
('maintenance_mode',   'false'),
('welcome_message',    'Welcome to FWE Support!'),
('max_file_size_mb',   '10'),
('business_open',      '09:00 AM'),
('business_close',     '06:00 PM'),
('off_hours_message',  'We are currently offline. Please leave a message.');

-- Default Admin (password: admin123)
-- Do NOT insert admin via SQL — the bcrypt hash must be generated by PHP on this server.
-- Instead, after importing this file, visit:  reset_admin.php  OR  api/init.php
-- INSERT INTO support_admins (username, password_hash, display_name, email) VALUES
-- ('admin', '<GENERATED_BY_PHP>', 'Support Admin', 'admin@fwegaming.site');