<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateQuantumCoreTables extends Migration
{
    public function up()
    {
        $this->createOrganizations();
        $this->createSites();
        $this->createTurbines();
        $this->createMaintenanceInterventions();
        $this->createMaintenanceTasks();
        $this->createMaintenanceInterventionTasks();
        $this->createMaintenanceEvents();
        $this->createMaintenanceFiles();
        $this->createBlockchainTransactions();
    }

    public function down()
    {
        $this->forge->dropTable('blockchain_transactions', true);
        $this->forge->dropTable('maintenance_files', true);
        $this->forge->dropTable('maintenance_events', true);
        $this->forge->dropTable('maintenance_intervention_tasks', true);
        $this->forge->dropTable('maintenance_tasks', true);
        $this->forge->dropTable('maintenance_interventions', true);
        $this->forge->dropTable('turbines', true);
        $this->forge->dropTable('sites', true);
        $this->forge->dropTable('organizations', true);
    }

    private function createOrganizations(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 160],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 120],
            'organization_type' => ['type' => 'VARCHAR', 'constraint' => 50],
            'country_code' => ['type' => 'CHAR', 'constraint' => 2, 'null' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 160, 'null' => true],
            'website' => ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('slug');
        $this->forge->createTable('organizations', true);
    }

    private function createSites(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'organization_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 160],
            'site_code' => ['type' => 'VARCHAR', 'constraint' => 80],
            'site_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'default' => 'wind_farm'],
            'country_code' => ['type' => 'CHAR', 'constraint' => 2, 'null' => true],
            'region' => ['type' => 'VARCHAR', 'constraint' => 80, 'null' => true],
            'location' => ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true],
            'latitude' => ['type' => 'DECIMAL', 'constraint' => '10,7', 'null' => true],
            'longitude' => ['type' => 'DECIMAL', 'constraint' => '10,7', 'null' => true],
            'commissioned_at' => ['type' => 'DATE', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('organization_id');
        $this->forge->addUniqueKey(['organization_id', 'site_code'], 'sites_organization_site_code_unique');
        $this->forge->addForeignKey('organization_id', 'organizations', 'id', 'CASCADE', 'CASCADE', 'sites_org_fk');
        $this->forge->createTable('sites', true);
    }

    private function createTurbines(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'site_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'turbine_code' => ['type' => 'VARCHAR', 'constraint' => 80],
            'name' => ['type' => 'VARCHAR', 'constraint' => 160, 'null' => true],
            'manufacturer' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'model' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'serial_number' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'rated_power_kw' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'null' => true],
            'hub_height_m' => ['type' => 'DECIMAL', 'constraint' => '8,2', 'null' => true],
            'rotor_diameter_m' => ['type' => 'DECIMAL', 'constraint' => '8,2', 'null' => true],
            'commissioned_at' => ['type' => 'DATE', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'active'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('site_id');
        $this->forge->addUniqueKey(['site_id', 'turbine_code'], 'turbines_site_turbine_code_unique');
        $this->forge->addForeignKey('site_id', 'sites', 'id', 'CASCADE', 'CASCADE', 'turbines_site_fk');
        $this->forge->createTable('turbines', true);
    }

    private function createMaintenanceInterventions(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'organization_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'site_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'turbine_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'intervention_code' => ['type' => 'VARCHAR', 'constraint' => 120],
            'title' => ['type' => 'VARCHAR', 'constraint' => 160],
            'description' => ['type' => 'TEXT', 'null' => true],
            'intervention_type' => ['type' => 'VARCHAR', 'constraint' => 40],
            'maintenance_mode' => ['type' => 'VARCHAR', 'constraint' => 40, 'default' => 'corrective'],
            'priority' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'medium'],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'planned'],
            'reported_at' => ['type' => 'DATETIME', 'null' => true],
            'scheduled_start_at' => ['type' => 'DATETIME', 'null' => true],
            'scheduled_end_at' => ['type' => 'DATETIME', 'null' => true],
            'started_at' => ['type' => 'DATETIME', 'null' => true],
            'completed_at' => ['type' => 'DATETIME', 'null' => true],
            'created_by_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'assigned_to_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'external_reference' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('organization_id');
        $this->forge->addKey('site_id');
        $this->forge->addKey('turbine_id');
        $this->forge->addKey('created_by_user_id');
        $this->forge->addKey('assigned_to_user_id');
        $this->forge->addUniqueKey(['organization_id', 'intervention_code'], 'maintenance_interventions_org_code_unique');
        $this->forge->addForeignKey('organization_id', 'organizations', 'id', 'CASCADE', 'CASCADE', 'mi_org_fk');
        $this->forge->addForeignKey('site_id', 'sites', 'id', 'CASCADE', 'CASCADE', 'mi_site_fk');
        $this->forge->addForeignKey('turbine_id', 'turbines', 'id', 'SET NULL', 'CASCADE', 'mi_turbine_fk');
        $this->forge->addForeignKey('created_by_user_id', 'users', 'id', 'SET NULL', 'CASCADE', 'mi_created_fk');
        $this->forge->addForeignKey('assigned_to_user_id', 'users', 'id', 'SET NULL', 'CASCADE', 'mi_assigned_fk');
        $this->forge->createTable('maintenance_interventions', true);
    }

    private function createMaintenanceTasks(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'organization_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'task_code' => ['type' => 'VARCHAR', 'constraint' => 80],
            'name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'description' => ['type' => 'TEXT', 'null' => true],
            'category' => ['type' => 'VARCHAR', 'constraint' => 60, 'null' => true],
            'estimated_duration_minutes' => ['type' => 'INT', 'constraint' => 11, 'null' => true],
            'requires_evidence' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'active'],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('organization_id');
        $this->forge->addUniqueKey(['organization_id', 'task_code'], 'maintenance_tasks_org_code_unique');
        $this->forge->addForeignKey('organization_id', 'organizations', 'id', 'CASCADE', 'CASCADE', 'mt_org_fk');
        $this->forge->createTable('maintenance_tasks', true);
    }

    private function createMaintenanceInterventionTasks(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'maintenance_intervention_id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true],
            'maintenance_task_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'sequence_no' => ['type' => 'INT', 'constraint' => 11, 'default' => 1],
            'task_label' => ['type' => 'VARCHAR', 'constraint' => 160],
            'task_description_snapshot' => ['type' => 'TEXT', 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'default' => 'pending'],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'performed_by_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'due_at' => ['type' => 'DATETIME', 'null' => true],
            'completed_at' => ['type' => 'DATETIME', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('maintenance_intervention_id');
        $this->forge->addKey('maintenance_task_id');
        $this->forge->addKey('performed_by_user_id');
        $this->forge->addUniqueKey(['maintenance_intervention_id', 'sequence_no'], 'maintenance_intervention_tasks_intervention_sequence_unique');
        $this->forge->addForeignKey('maintenance_intervention_id', 'maintenance_interventions', 'id', 'CASCADE', 'CASCADE', 'mit_intervention_fk');
        $this->forge->addForeignKey('maintenance_task_id', 'maintenance_tasks', 'id', 'RESTRICT', 'CASCADE', 'mit_task_fk');
        $this->forge->addForeignKey('performed_by_user_id', 'users', 'id', 'SET NULL', 'CASCADE', 'mit_user_fk');
        $this->forge->createTable('maintenance_intervention_tasks', true);
    }

    private function createMaintenanceEvents(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'maintenance_intervention_id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true],
            'event_type' => ['type' => 'VARCHAR', 'constraint' => 40],
            'event_message' => ['type' => 'VARCHAR', 'constraint' => 190],
            'event_data' => ['type' => 'TEXT', 'null' => true],
            'happened_at' => ['type' => 'DATETIME'],
            'created_by_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('maintenance_intervention_id');
        $this->forge->addKey('created_by_user_id');
        $this->forge->addForeignKey('maintenance_intervention_id', 'maintenance_interventions', 'id', 'CASCADE', 'CASCADE', 'me_intervention_fk');
        $this->forge->addForeignKey('created_by_user_id', 'users', 'id', 'SET NULL', 'CASCADE', 'me_user_fk');
        $this->forge->createTable('maintenance_events', true);
    }

    private function createMaintenanceFiles(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'maintenance_intervention_id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true],
            'maintenance_intervention_task_id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'null' => true],
            'uploaded_by_user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'file_category' => ['type' => 'VARCHAR', 'constraint' => 40, 'default' => 'attachment'],
            'original_name' => ['type' => 'VARCHAR', 'constraint' => 190],
            'stored_name' => ['type' => 'VARCHAR', 'constraint' => 190],
            'storage_path' => ['type' => 'VARCHAR', 'constraint' => 255],
            'mime_type' => ['type' => 'VARCHAR', 'constraint' => 120, 'null' => true],
            'file_size_bytes' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'null' => true],
            'checksum_sha256' => ['type' => 'CHAR', 'constraint' => 64, 'null' => true],
            'description' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('maintenance_intervention_id');
        $this->forge->addKey('maintenance_intervention_task_id');
        $this->forge->addKey('uploaded_by_user_id');
        $this->forge->addForeignKey('maintenance_intervention_id', 'maintenance_interventions', 'id', 'CASCADE', 'CASCADE', 'mf_intervention_fk');
        $this->forge->addForeignKey('maintenance_intervention_task_id', 'maintenance_intervention_tasks', 'id', 'SET NULL', 'CASCADE', 'mf_task_fk');
        $this->forge->addForeignKey('uploaded_by_user_id', 'users', 'id', 'SET NULL', 'CASCADE', 'mf_user_fk');
        $this->forge->createTable('maintenance_files', true);
    }

    private function createBlockchainTransactions(): void
    {
        $this->forge->addField([
            'id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true, 'auto_increment' => true],
            'maintenance_intervention_id' => ['type' => 'BIGINT', 'constraint' => 20, 'unsigned' => true],
            'network' => ['type' => 'VARCHAR', 'constraint' => 60],
            'transaction_hash' => ['type' => 'VARCHAR', 'constraint' => 190],
            'contract_address' => ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true],
            'block_number' => ['type' => 'BIGINT', 'constraint' => 20, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 20, 'default' => 'pending'],
            'anchor_type' => ['type' => 'VARCHAR', 'constraint' => 40, 'default' => 'intervention'],
            'anchored_hash' => ['type' => 'VARCHAR', 'constraint' => 190, 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('maintenance_intervention_id');
        $this->forge->addUniqueKey('transaction_hash');
        $this->forge->addForeignKey('maintenance_intervention_id', 'maintenance_interventions', 'id', 'CASCADE', 'CASCADE', 'bt_intervention_fk');
        $this->forge->createTable('blockchain_transactions', true);
    }
}
