<?php
/**
 * Database schema for SupportEngine Basic (tickets-only). Used by basic version/install.php.
 */

declare(strict_types=1);

function createTablesBasic(PDO $pdo, string $adminEmail, string $adminPasswordPlain): string
{
    $output = '';
    $transactionStarted = false;

    $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    try {
        $pdo->beginTransaction();
        $transactionStarted = true;
        $pdo->exec("SET time_zone = '+00:00'");

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                role ENUM('admin', 'staff', 'member') DEFAULT 'member',
                avatar VARCHAR(255) NULL,
                reset_token VARCHAR(64) NULL,
                reset_token_expires_at DATETIME NULL,
                is_email_verified BOOLEAN DEFAULT FALSE,
                email_validation_token VARCHAR(64) NULL,
                email_validation_token_expires_at DATETIME NULL,
                magic_link_token VARCHAR(64) NULL,
                magic_link_expires_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                company VARCHAR(100),
                address VARCHAR(255) NULL,
                state VARCHAR(255) NULL,
                zipcode VARCHAR(10) NULL,
                country_code VARCHAR(2) NULL,
                city VARCHAR(255) NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
        ");
        $output .= '<p>Users table created/checked successfully.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS custom_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    type ENUM('text','textarea','checkbox','checkboxes','select','radio') DEFAULT 'text',
    zone ENUM('tickets','profile') DEFAULT 'tickets',
    sort_order INT DEFAULT 0,
    required BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT FALSE,
    possible_values TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS custom_field_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_id INT,
    value TEXT,
	item_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");

        $pdo->exec("CREATE TABLE IF NOT EXISTS user_notification_settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            notify_new_user_registration BOOLEAN DEFAULT TRUE,
            notify_ticket_creation BOOLEAN DEFAULT TRUE,
            notify_ticket_status_change BOOLEAN DEFAULT TRUE,
            notify_ticket_comment BOOLEAN DEFAULT TRUE,
            notify_project_task_creation BOOLEAN DEFAULT TRUE,
            notify_project_task_milestone_change BOOLEAN DEFAULT TRUE,
            notify_project_task_completion BOOLEAN DEFAULT TRUE,
            notify_project_file_upload BOOLEAN DEFAULT TRUE,
            notify_project_assignment BOOLEAN DEFAULT TRUE,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>User notification settings table created.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS ticket_categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL UNIQUE,
            is_default BOOLEAN DEFAULT FALSE,
            icon VARCHAR(100) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>Ticket categories table created.</p>';

        $pdo->exec("INSERT IGNORE INTO ticket_categories (name, is_default, icon) VALUES ('General', TRUE, 'info-square'), ('Support', FALSE, 'question-circle')");
        $output .= '<p>Default ticket categories ensured.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS tickets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        category_id INT NULL,
        assigned_to INT NULL,
        subject VARCHAR(100) NOT NULL,
        message TEXT NOT NULL,
        status ENUM('open','closed') DEFAULT 'open',
        priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
        attachment VARCHAR(255),
        email VARCHAR(100),
        name VARCHAR(100),
        company VARCHAR(100),
        related_service VARCHAR(255),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (category_id) REFERENCES ticket_categories(id) ON DELETE SET NULL,
        FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>Tickets table created/updated.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS ticket_replies (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ticket_id INT NOT NULL,
        user_id INT,
        message TEXT NOT NULL,
        attachment VARCHAR(255),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>Ticket replies table created.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS faqs (id INT AUTO_INCREMENT PRIMARY KEY, question TEXT NOT NULL, answer TEXT NOT NULL, category VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>FAQs table created.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS news (id INT AUTO_INCREMENT PRIMARY KEY, title TEXT NOT NULL, message TEXT NOT NULL, category VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>News table created.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS ticket_snippets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            subject VARCHAR(255) NOT NULL,
            category VARCHAR(100) NOT NULL,
            content TEXT NOT NULL,
            created_by INT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");
        $output .= '<p>Ticket snippets table created.</p>';

        $pdo->exec("CREATE TABLE IF NOT EXISTS submission_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    email VARCHAR(255) NULL,
    action_type ENUM('ticket_create', 'register', 'contact', 'magic_link') NOT NULL,
    last_submission DATETIME NOT NULL,
    attempt_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_action (ip_address, action_type),
    INDEX idx_email_action (email, action_type),
    INDEX idx_last_submission (last_submission)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci");

        $adminPasswordHash = password_hash($adminPasswordPlain, PASSWORD_DEFAULT);
        $stmtCheck = $pdo->prepare('SELECT id FROM users WHERE email = ?');
        $stmtCheck->execute([$adminEmail]);
        if ($stmtCheck->fetchColumn() === false) {
            $stmt = $pdo->prepare("INSERT INTO users (name, email, password, role, is_email_verified) VALUES (?, ?, ?, 'admin', TRUE)");
            $stmt->execute(['Admin User', $adminEmail, $adminPasswordHash]);
            $output .= '<p>Admin user created.</p>';
        } else {
            $output .= '<p>Admin user with this email already exists.</p>';
        }

        $adminStmt = $pdo->query("SELECT id FROM users WHERE role = 'admin' ORDER BY id ASC LIMIT 1");
        $adminId = (int) $adminStmt->fetchColumn();
        if ($adminId < 1) {
            throw new RuntimeException('Could not resolve admin user id for sample data.');
        }

        $faqCount = (int) $pdo->query('SELECT COUNT(*) FROM faqs')->fetchColumn();
        if ($faqCount === 0) {
            $pdo->exec("INSERT INTO faqs (question, answer, category) VALUES
                       ('How do I reset my password?', 'You can reset your password by clicking on the \"Forgot Password\" link on the login page.', 'Account'),
                       ('How do I submit a support ticket?', 'Use the Tickets section and choose Create New Ticket.', 'Support'),
                       ('Can I change my email address?', 'Yes, you can change your email address in your profile settings.', 'Account'),
                       ('How long until I get a reply?', 'We usually respond within 24–48 hours on business days.', 'Support')");
            $output .= '<p>Sample FAQs inserted.</p>';
        }

        $snippetCount = (int) $pdo->query('SELECT COUNT(*) FROM ticket_snippets')->fetchColumn();
        if ($snippetCount === 0) {
            $ins = $pdo->prepare('INSERT INTO ticket_snippets (subject, category, content, created_by) VALUES (?, ?, ?, ?)');
            $ins->execute(['Welcome and thank you', 'General', "Thank you for contacting our support team. We have received your request and will get back to you as soon as possible.\n\nOur typical response time is 24-48 hours for non-urgent issues.\n\nBest regards,\nSupport Team", $adminId]);
            $ins->execute(['Request for more information', 'General', "Thank you for contacting us.\n\nTo assist you, please provide:\n• Specific details about the issue\n• Any error messages\n• Steps you already tried\n\nThank you for your patience.", $adminId]);
            $ins->execute(['Issue resolved - closing ticket', 'General', "Thank you for your patience. Your issue has been resolved.\n\nWe are closing this ticket. If you need further help, please open a new support ticket.", $adminId]);
            $output .= '<p>Sample ticket snippets inserted.</p>';
        }

        if ($transactionStarted && $pdo->inTransaction()) {
            $pdo->commit();
            $transactionStarted = false;
            $output .= '<p><strong>Database setup completed successfully!</strong></p>';
        }
    } catch (Throwable $e) {
        if ($transactionStarted && $pdo->inTransaction()) {
            try {
                $pdo->rollback();
            } catch (Exception $rollbackException) {
                error_log('createTablesBasic rollback: ' . $rollbackException->getMessage());
            }
            $transactionStarted = false;
        }
        throw $e;
    } finally {
        try {
            $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
        } catch (PDOException $fkException) {
            error_log('createTablesBasic: failed to re-enable FK checks: ' . $fkException->getMessage());
        }
    }

    return $output;
}
