<?php
// resources/dbschema/dbupdates.php (Full v3.8.8 Schema)
// This file echoes SQL containing all CREATE TABLE statements used by Support Board.

header('Content-Type: text/plain');

$sql = "";

/* -------------------------------------------------------
 * CORE TABLES
 * ----------------------------------------------------- */

$sql .= "CREATE TABLE IF NOT EXISTS sb_users (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    password VARCHAR(255),
    email VARCHAR(191) UNIQUE,
    profile_image VARCHAR(191),
    user_type VARCHAR(10) NOT NULL,
    creation_time DATETIME NOT NULL,
    token VARCHAR(100) NOT NULL UNIQUE,
    last_activity DATETIME,
    typing INT DEFAULT -1,
    department TINYINT,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_users_data (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    slug VARCHAR(191) NOT NULL,
    name VARCHAR(191) NOT NULL,
    value LONGTEXT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE,
    UNIQUE INDEX sb_users_data_index (user_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_conversations (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(191),
    creation_time DATETIME NOT NULL,
    status_code TINYINT DEFAULT 0,
    department SMALLINT,
    agent_id INT,
    source VARCHAR(10),
    extra VARCHAR(191),
    extra_2 VARCHAR(191),
    extra_3 VARCHAR(191),
    tags VARCHAR(191),
    PRIMARY KEY (id),
    FOREIGN KEY (agent_id) REFERENCES sb_users(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_messages (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    message LONGTEXT NOT NULL,
    creation_time DATETIME NOT NULL,
    status_code TINYINT DEFAULT 0,
    attachments LONGTEXT,
    payload LONGTEXT,
    conversation_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES sb_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_settings (
    name VARCHAR(191) NOT NULL,
    value LONGTEXT,
    PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_reports (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    value LONGTEXT NOT NULL,
    creation_time DATE NOT NULL,
    external_id INT,
    extra VARCHAR(191),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_articles (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(191) NOT NULL,
    content LONGTEXT NOT NULL,
    editor_js LONGTEXT NOT NULL,
    nav TEXT,
    link VARCHAR(191),
    category VARCHAR(191),
    parent_category VARCHAR(191),
    language VARCHAR(10),
    parent_id INT,
    slug VARCHAR(191),
    update_time DATE NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES sb_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

/* -------------------------------------------------------
 * ADDITIONAL SYSTEM TABLES
 * ----------------------------------------------------- */

$sql .= "CREATE TABLE IF NOT EXISTS sb_chatbot (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    type VARCHAR(50),
    configuration LONGTEXT,
    active TINYINT DEFAULT 1,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_attachments (
    id INT NOT NULL AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    user_id INT,
    file_name VARCHAR(191),
    file_type VARCHAR(50),
    file_size BIGINT,
    file_path VARCHAR(255),
    upload_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (conversation_id) REFERENCES sb_conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_languages (
    code VARCHAR(10) NOT NULL,
    name VARCHAR(50) NOT NULL,
    active TINYINT DEFAULT 1,
    PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_departments (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    description LONGTEXT,
    active TINYINT DEFAULT 1,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_schedule (
    id INT NOT NULL AUTO_INCREMENT,
    department_id INT,
    day_of_week TINYINT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES sb_departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_events (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(191) NOT NULL,
    description LONGTEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES sb_departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_blocked (
    id INT NOT NULL AUTO_INCREMENT,
    ip_address VARCHAR(45),
    user_id INT,
    reason VARCHAR(191),
    blocked_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_user_agents (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    last_seen DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_jwt (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    token VARCHAR(512) NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_social_login (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    provider VARCHAR(50) NOT NULL,
    provider_id VARCHAR(191) NOT NULL,
    access_token VARCHAR(512),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_faq (
    id INT NOT NULL AUTO_INCREMENT,
    question VARCHAR(255) NOT NULL,
    answer LONGTEXT NOT NULL,
    category VARCHAR(100),
    language VARCHAR(10) DEFAULT 'en',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_canned_messages (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(191),
    message LONGTEXT NOT NULL,
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES sb_departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_push_notifications (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    message LONGTEXT NOT NULL,
    read_status TINYINT DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

/* -------------------------------------------------------
 * OFFICIAL SUPPORT BOARD TABLES
 * ----------------------------------------------------- */

$sql .= "CREATE TABLE IF NOT EXISTS sb_customers (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(191),
    email VARCHAR(191),
    phone VARCHAR(50),
    company VARCHAR(191),
    country VARCHAR(191),
    city VARCHAR(191),
    address VARCHAR(191),
    website VARCHAR(191),
    creation_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_tickets (
    id INT NOT NULL AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    subject VARCHAR(191),
    priority VARCHAR(20) DEFAULT 'normal',
    status VARCHAR(50) DEFAULT 'open',
    last_update DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (conversation_id) REFERENCES sb_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_ratings (
    id INT NOT NULL AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    rating TINYINT NOT NULL,
    feedback LONGTEXT,
    creation_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (conversation_id) REFERENCES sb_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_emails_queue (
    id INT NOT NULL AUTO_INCREMENT,
    email_to VARCHAR(191) NOT NULL,
    subject VARCHAR(191) NOT NULL,
    message LONGTEXT NOT NULL,
    headers LONGTEXT,
    created_at DATETIME NOT NULL,
    sent TINYINT DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_webhooks (
    id INT NOT NULL AUTO_INCREMENT,
    event VARCHAR(191) NOT NULL,
    payload LONGTEXT NOT NULL,
    sent TINYINT DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_sessions (
    id VARCHAR(191) NOT NULL PRIMARY KEY,
    user_id INT,
    last_activity DATETIME NOT NULL,
    device VARCHAR(191),
    ip VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_notifications (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    type VARCHAR(50),
    message LONGTEXT,
    created_at DATETIME NOT NULL,
    read_status TINYINT DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_logs (
    id INT NOT NULL AUTO_INCREMENT,
    type VARCHAR(50),
    message LONGTEXT,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

$sql .= "CREATE TABLE IF NOT EXISTS sb_bot_logs (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    message LONGTEXT,
    creation_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n";

/* -------------------------------------------------------
 * OUTPUT SQL
 * ----------------------------------------------------- */

echo $sql;