<?php

class Migration_20240712000007_create_tickets_table
{
    public static function up(PDO $pdo)
    {
        $sql = "
            CREATE TABLE IF NOT EXISTS support_tickets (
                id INT AUTO_INCREMENT PRIMARY KEY,
                subject VARCHAR(255) NOT NULL,
                status VARCHAR(255) NOT NULL,
                type VARCHAR(255) NOT NULL,
                category VARCHAR(255),
                priority VARCHAR(255) NOT NULL,
                description MEDIUMTEXT,
                owner INT NOT NULL,
                createdBy INT NOT NULL,
                property INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (owner) REFERENCES users(id),
                FOREIGN KEY (createdBy) REFERENCES users(id),
                FOREIGN KEY (property) REFERENCES properties(id)
            );

            CREATE TABLE IF NOT EXISTS ticketComments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                comment TEXT NOT NULL,
                createdBy INT NOT NULL,
                ticketId INT NOT NULL,
                createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (createdBy) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (ticketId) REFERENCES support_tickets(id) ON DELETE CASCADE
            );
        ";

        $pdo->exec($sql);
    }

    public static function down(PDO $pdo)
    {
        $sql = "
            DROP TABLE IF EXISTS ticketComments;
            DROP TABLE IF EXISTS support_tickets;
        ";

        $pdo->exec($sql);
    }
}
