<?php

class Migration_20240919191939_add_hardwares_table
{
    public static function up(PDO $pdo): void
    {
        $sql = "
            CREATE TABLE IF NOT EXISTS `hardware_devices` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `property_id` INT NOT NULL,
                `model` VARCHAR(255) NOT NULL,
                `device_type` VARCHAR(255) NOT NULL,
                `image_path` LONGTEXT NULL,
                `ip_address` VARCHAR(15) NOT NULL,
                `subnet` VARCHAR(15) NULL,
                `gateway` VARCHAR(15) NULL,
                `dns1` VARCHAR(15) NULL,
                `dns2` VARCHAR(15),
                `protocol` ENUM('TCP', 'UDP', 'HTTP', 'HTTPS', 'SSH', 'FTP','SMTP') DEFAULT 'HTTP',
                `http_port` INT,
                `https_port` INT,
                `smtp_port` INT,
                `complete_url` VARCHAR(255) NULL,
                `created_by` INT NOT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_by` VARCHAR(50),
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                 FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
                 FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`)
            );
        ";

        $pdo->exec($sql);

        $sql4 = "
        CREATE TABLE IF NOT EXISTS gate_door_group (
            id INT AUTO_INCREMENT PRIMARY KEY,
            groupName VARCHAR(255) NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );
        ";

        $pdo->exec(statement: $sql4);

        $sql1 = "
        CREATE TABLE IF NOT EXISTS gate_doors (
            id INT AUTO_INCREMENT PRIMARY KEY,
            group_by VARCHAR(255) NOT NULL,
            door_name VARCHAR(255) NOT NULL,
            door_image LONGTEXT NULL,
            property_id INT NULL,
            location VARCHAR(255) NULL,
            access_code VARCHAR(50) NULL,
            status ENUM('locked', 'unlocked', 'faulty','unknown') DEFAULT 'unknown',
            hardware_id INT NULL,
            complete_url_assigned VARCHAR(255) NULL,
            type VARCHAR(50) NOT NULL,
            door_key INT,
            schedulesId INT,
            description TEXT NULL,
            connectionStatus ENUM('online', 'offline', 'unknown') DEFAULT 'unknown',
            output_assign VARCHAR(255) NULL,
            input_assign TEXT NULL,
            iiNumber VARCHAR(50) NULL,
            iiToneOpenNumber VARCHAR(50) NULL,
            CreatedBy INT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

            -- Foreign Key Constraints with unique names
            CONSTRAINT fk_gate_hardware_id FOREIGN KEY (hardware_id) REFERENCES hardware_devices(id) ON DELETE SET NULL,
            CONSTRAINT fk_properties_id_doors FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
            CONSTRAINT fk_gate_schedules_id FOREIGN KEY (schedulesId) REFERENCES calendar_added_events(id) ON DELETE SET NULL,
            CONSTRAINT fk_gate_CreatedBy FOREIGN KEY (CreatedBy) REFERENCES users(id) ON DELETE SET NULL
        );
        ";

        $pdo->exec($sql1);

        $sql2 = "
        CREATE TABLE IF NOT EXISTS `hardwares_settings` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `device_id` INT NOT NULL,
            `section` VARCHAR(10) NULL,
            `assigned_door` INT NOT NULL,
            `output` VARCHAR(256) NOT NULL,
            `inputs` TEXT NOT NULL,
            `function` TEXT NULL,
            `status` TEXT NOT NULL,
            `timer` INT NULL,
            `ipAddress` VARCHAR(256) NULL,
            `ipPort` INT NULL,
            `completeUrl` VARCHAR(256) NULL,
            `created_by` INT NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

            CONSTRAINT fk_hardware_settings_device_id FOREIGN KEY (`device_id`) REFERENCES `hardware_devices`(`id`),
            CONSTRAINT fk_hardware__setting_assigned_door_id FOREIGN KEY (`assigned_door`) REFERENCES `gate_doors`(`id`),
            CONSTRAINT fk_hardware__setting_schedule_id FOREIGN KEY (`timer`) REFERENCES `calendar_added_events`(`id`) ON DELETE SET NULL ON UPDATE SET NULL,
            CONSTRAINT fk_hardware__created_by FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
        );
    ";

        $pdo->exec($sql2);
        $sql5 = "
        CREATE TABLE IF NOT EXISTS `hardwares_settings_events` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `eventName` VARCHAR(100) NULL,
            `eventType` VARCHAR(50) NULL,
            `eventOf` INT NULL,
            `URL` VARCHAR(100) NULL,
            `eventDetail` VARCHAR(100) NULL,
            `created_by` INT NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

            FOREIGN KEY (`eventOf`) REFERENCES `hardwares_settings`(`id`) ON DELETE SET NULL ON UPDATE SET NULL,
            CONSTRAINT fk_hardware_events_created_by FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
        );
    ";

        $pdo->exec(statement: $sql5);
    }

    public static function down(PDO $pdo): void
    {

        $sql = " DROP TABLE IF EXISTS `hardwares_settings`;
        DROP TABLE IF EXISTS `gate_doors`;
        DROP TABLE IF EXISTS `gate_door_group`;
        DROP TABLE IF EXISTS `hardware_devices`;";
        $pdo->exec($sql);
    }
}
