<?php

class Migration_20240712000011_create_audit_log_table
{
    public static function up(PDO $pdo)
    {
        $sqlCreateTable = "
            CREATE TABLE IF NOT EXISTS audit_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                action VARCHAR(50),
                table_name VARCHAR(100),
                record_id INT,
                property_id INT NULL,
                old_value TEXT NULL,
                new_value TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                additional_info TEXT NULL
            )
        ";

        $sqlAlterTables = "
            ALTER TABLE audit_trail_login ADD COLUMN deleted_at DATETIME DEFAULT NULL;
            ALTER TABLE property_users_data ADD COLUMN deleted_at DATETIME DEFAULT NULL;
            ALTER TABLE profile_photo ADD COLUMN deleted_at DATETIME DEFAULT NULL;
            ALTER TABLE property_users ADD COLUMN deleted_at DATETIME DEFAULT NULL;
        ";

        // Create the audit_logs table
        $pdo->exec($sqlCreateTable);

        // Execute each alter table statement
        $alterStatements = explode(';', $sqlAlterTables);
        foreach ($alterStatements as $statement) {
            if (trim($statement)) {
                $pdo->exec($statement);
            }
        }
    }

    public static function down(PDO $pdo)
    {
        $sqlDropTable = "DROP TABLE IF EXISTS audit_logs";

        $sqlDropColumns = "
            ALTER TABLE audit_trail_login DROP COLUMN deleted_at;
            ALTER TABLE property_users_data DROP COLUMN deleted_at;
            ALTER TABLE profile_photo DROP COLUMN deleted_at;
            ALTER TABLE property_users DROP COLUMN deleted_at;
        ";

        // Drop the audit_logs table
        $pdo->exec($sqlDropTable);

        // Execute each drop column statement
        $dropStatements = explode(';', $sqlDropColumns);
        foreach ($dropStatements as $statement) {
            if (trim($statement)) {
                $pdo->exec($statement);
            }
        }
    }
}
