<?php
/**
 * Migration Script: Add Field Configuration System
 * 
 * Creates:
 * - field_configurations table
 * - field_conditions table
 * - Adds columns to deals and persons tables
 * - Seeding lookup groups
 * - Initial field configurations
 */

require_once __DIR__ . '/../../app/config/constants.php';
require_once __DIR__ . '/../../vendor/autoload.php';

use App\Database\Connection;
use App\Utils\Logger;

class FieldConfigurationMigration
{
    private $db;
    private $pdo;

    public function __construct()
    {
        try {
            $this->db = Connection::getInstance();
            $this->pdo = $this->db->getPdo();
            echo "✓ Database connection established\n";
        } catch (Exception $e) {
            echo "✗ Database connection failed: " . $e->getMessage() . "\n";
            exit(1);
        }
    }

    public function run()
    {
        echo "\n=== Field Configuration Migration Started ===\n\n";

        try {
            // Step 1: Create field_configurations table
            echo "Step 1/5: Creating field_configurations table...\n";
            $this->createFieldConfigurationsTable();

            // Step 2: Create field_conditions table
            echo "Step 2/5: Creating field_conditions table...\n";
            $this->createFieldConditionsTable();

            // Step 3: Add columns to deals and persons
            echo "Step 3/5: Adding columns to deals and persons tables...\n";
            $this->addColumnsToExistingTables();

            // Step 4: Seed lookup groups
            echo "Step 4/5: Seeding lookup groups...\n";
            $this->seedLookupGroups();

            // Step 5: Seed initial field configurations
            echo "Step 5/5: Seeding initial field configurations...\n";
            $this->seedFieldConfigurations();

            echo "\n=== Migration Completed Successfully ===\n";
            echo "✓ All changes applied\n\n";

        } catch (Exception $e) {
            echo "\n✗ Migration failed: " . $e->getMessage() . "\n";
            Logger::logError("Field Configuration Migration failed", $e);
            exit(1);
        }
    }

    private function createFieldConfigurationsTable()
    {
        $sql = "CREATE TABLE IF NOT EXISTS field_configurations (
            id INT PRIMARY KEY AUTO_INCREMENT,
            field_name VARCHAR(100) UNIQUE NOT NULL,
            field_label VARCHAR(255) NOT NULL,
            field_type ENUM('text', 'number', 'select', 'textarea', 'date', 'email') NOT NULL,
            entity_type VARCHAR(50) NOT NULL,
            is_required TINYINT(1) DEFAULT 0,
            is_editable TINYINT(1) DEFAULT 1,
            is_active TINYINT(1) DEFAULT 1,
            is_system TINYINT(1) DEFAULT 0,
            edit_allow_roles VARCHAR(500) NULL,
            lookup_group_code VARCHAR(100) NULL,
            sort_order INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_field (entity_type, field_name),
            INDEX idx_entity_type (entity_type),
            INDEX idx_active (is_active),
            INDEX idx_field_name (field_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        try {
            $this->pdo->exec($sql);
            echo "  ✓ field_configurations table created\n";
        } catch (Exception $e) {
            if (strpos($e->getMessage(), 'already exists') === false) {
                throw $e;
            }
            echo "  ℹ field_configurations table already exists\n";
        }
    }

    private function createFieldConditionsTable()
    {
        $sql = "CREATE TABLE IF NOT EXISTS field_conditions (
            id INT PRIMARY KEY AUTO_INCREMENT,
            trigger_field VARCHAR(100) NOT NULL,
            trigger_value VARCHAR(255) NOT NULL,
            action_field VARCHAR(100) NOT NULL,
            action_type ENUM('show', 'hide', 'require', 'unrequire') NOT NULL,
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_trigger_field (trigger_field),
            INDEX idx_action_field (action_field)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        try {
            $this->pdo->exec($sql);
            echo "  ✓ field_conditions table created\n";
        } catch (Exception $e) {
            if (strpos($e->getMessage(), 'already exists') === false) {
                throw $e;
            }
            echo "  ℹ field_conditions table already exists\n";
        }
    }

    private function addColumnsToExistingTables()
    {
        // Add to deals table
        $dealsColumns = [
            'destination_bank' => "ALTER TABLE deals ADD COLUMN destination_bank VARCHAR(100) NULL AFTER payment_method",
        ];

        foreach ($dealsColumns as $colName => $sql) {
            try {
                if (!$this->columnExists('deals', $colName)) {
                    $this->pdo->exec($sql);
                    echo "  ✓ Added $colName to deals table\n";
                } else {
                    echo "  ℹ Column $colName already exists in deals\n";
                }
            } catch (Exception $e) {
                echo "  ⚠ Error adding $colName: " . $e->getMessage() . "\n";
            }
        }

        // Add to persons table
        $personsColumns = [
            'payment_method' => "ALTER TABLE persons ADD COLUMN payment_method VARCHAR(100) NULL",
            'destination_bank' => "ALTER TABLE persons ADD COLUMN destination_bank VARCHAR(100) NULL",
        ];

        foreach ($personsColumns as $colName => $sql) {
            try {
                if (!$this->columnExists('persons', $colName)) {
                    $this->pdo->exec($sql);
                    echo "  ✓ Added $colName to persons table\n";
                } else {
                    echo "  ℹ Column $colName already exists in persons\n";
                }
            } catch (Exception $e) {
                echo "  ⚠ Error adding $colName: " . $e->getMessage() . "\n";
            }
        }

        // Add indexes
        $this->addIndexIfNotExists('deals', 'idx_payment_method', 'payment_method');
        $this->addIndexIfNotExists('deals', 'idx_destination_bank', 'destination_bank');
        $this->addIndexIfNotExists('persons', 'idx_payment_method', 'payment_method');
        $this->addIndexIfNotExists('persons', 'idx_destination_bank', 'destination_bank');
    }

    private function seedLookupGroups()
    {
        // Check if groups already exist
        $stmt = $this->pdo->prepare("SELECT id FROM lookup_groups WHERE code = ?");

        $groups = [
            [
                'code' => 'payment_methods',
                'title' => 'نحوه‌های پرداخت',
                'description' => 'روش‌های پرداخت موجود برای معاملات'
            ],
            [
                'code' => 'destination_banks',
                'title' => 'بانک‌های مقصد پرداخت',
                'description' => 'بانک‌های مقصد برای انتقال وجه'
            ]
        ];

        foreach ($groups as $group) {
            $stmt->execute([$group['code']]);
            if ($stmt->fetch()) {
                echo "  ℹ Group '{$group['code']}' already exists\n";
                continue;
            }

            $insertStmt = $this->pdo->prepare(
                "INSERT INTO lookup_groups (code, title, description, sort_order, is_active, is_system)
                 VALUES (?, ?, ?, ?, 1, 0)"
            );
            $insertStmt->execute([$group['code'], $group['title'], $group['description'], 0]);
            echo "  ✓ Created group '{$group['code']}'\n";
        }

        // Seed payment_methods items
        $this->seedPaymentMethods();

        // Seed destination_banks items
        $this->seedDestinationBanks();
    }

    private function seedPaymentMethods()
    {
        $paymentGroup = $this->getGroupByCode('payment_methods');
        if (!$paymentGroup) {
            echo "  ✗ payment_methods group not found\n";
            return;
        }

        $methods = [
            ['code' => 'card_to_card', 'title' => 'کارت به کارت', 'value' => 'CC', 'sort' => 1],
            ['code' => 'payment_gateway', 'title' => 'درگاه پرداخت', 'value' => 'GW', 'sort' => 2],
            ['code' => 'check', 'title' => 'چک', 'value' => 'CK', 'sort' => 3],
            ['code' => 'purchase_order', 'title' => 'سفارش خرید', 'value' => 'PO', 'sort' => 4],
            ['code' => 'other', 'title' => 'سایر', 'value' => 'OTHER', 'sort' => 5],
        ];

        $this->seedLookupItems($paymentGroup['id'], $methods);
    }

    private function seedDestinationBanks()
    {
        $bankGroup = $this->getGroupByCode('destination_banks');
        if (!$bankGroup) {
            echo "  ✗ destination_banks group not found\n";
            return;
        }

        $banks = [
            ['code' => 'parsian', 'title' => 'بانک پارسیان', 'value' => 'PSN', 'sort' => 1],
            ['code' => 'pasargad', 'title' => 'بانک پاسارگاد', 'value' => 'PSG', 'sort' => 2],
            ['code' => 'melli', 'title' => 'بانک ملی', 'value' => 'MELLI', 'sort' => 3],
            ['code' => 'ayandeh', 'title' => 'بانک آینده', 'value' => 'AYANDEH', 'sort' => 4],
            ['code' => 'melat', 'title' => 'بانک ملت', 'value' => 'MELAT', 'sort' => 5],
            ['code' => 'tejarat', 'title' => 'بانک تجارت', 'value' => 'TEJARAT', 'sort' => 6],
            ['code' => 'other', 'title' => 'سایر', 'value' => 'OTHER', 'sort' => 7],
        ];

        $this->seedLookupItems($bankGroup['id'], $banks);
    }

    private function seedLookupItems($groupId, $items)
    {
        $checkStmt = $this->pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND code = ?");
        $insertStmt = $this->pdo->prepare(
            "INSERT INTO lookup_items (group_id, code, title, value, sort_order, is_active, is_system)
             VALUES (?, ?, ?, ?, ?, 1, 0)"
        );

        foreach ($items as $item) {
            $checkStmt->execute([$groupId, $item['code']]);
            if ($checkStmt->fetch()) {
                echo "  ℹ Item '{$item['code']}' already exists\n";
                continue;
            }

            $insertStmt->execute([$groupId, $item['code'], $item['title'], $item['value'], $item['sort']]);
            echo "  ✓ Created item '{$item['code']}'\n";
        }
    }

    private function seedFieldConfigurations()
    {
        $fields = [
            [
                'field_name' => 'payment_method',
                'field_label' => 'نحوه پرداخت',
                'field_type' => 'select',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist', 'agent']),
                'lookup_group_code' => 'payment_methods',
                'sort_order' => 1
            ],
            [
                'field_name' => 'payment_card_last4',
                'field_label' => 'آخر ۴ رقم کارت',
                'field_type' => 'text',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist']),
                'lookup_group_code' => null,
                'sort_order' => 2
            ],
            [
                'field_name' => 'destination_bank',
                'field_label' => 'بانک مقصد پرداخت',
                'field_type' => 'select',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist']),
                'lookup_group_code' => 'destination_banks',
                'sort_order' => 3
            ]
        ];

        $checkStmt = $this->pdo->prepare("SELECT id FROM field_configurations WHERE field_name = ?");
        $insertStmt = $this->pdo->prepare(
            "INSERT INTO field_configurations 
             (field_name, field_label, field_type, entity_type, is_required, is_editable, 
              is_active, edit_allow_roles, lookup_group_code, sort_order)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );

        foreach ($fields as $field) {
            $checkStmt->execute([$field['field_name']]);
            if ($checkStmt->fetch()) {
                echo "  ℹ Field '{$field['field_name']}' already configured\n";
                continue;
            }

            $insertStmt->execute([
                $field['field_name'],
                $field['field_label'],
                $field['field_type'],
                $field['entity_type'],
                $field['is_required'],
                $field['is_editable'],
                $field['is_active'],
                $field['edit_allow_roles'],
                $field['lookup_group_code'],
                $field['sort_order']
            ]);
            echo "  ✓ Configured field '{$field['field_name']}'\n";
        }

        // Seed conditional logic
        $this->seedFieldConditions();
    }

    private function seedFieldConditions()
    {
        $conditions = [
            [
                'trigger_field' => 'payment_method',
                'trigger_value' => 'card_to_card',
                'action_field' => 'payment_card_last4',
                'action_type' => 'require'
            ],
            [
                'trigger_field' => 'payment_method',
                'trigger_value' => 'card_to_card',
                'action_field' => 'destination_bank',
                'action_type' => 'show'
            ],
            [
                'trigger_field' => 'payment_method',
                'trigger_value' => 'check',
                'action_field' => 'destination_bank',
                'action_type' => 'hide'
            ]
        ];

        $checkStmt = $this->pdo->prepare(
            "SELECT id FROM field_conditions WHERE trigger_field = ? AND trigger_value = ? AND action_field = ?"
        );
        $insertStmt = $this->pdo->prepare(
            "INSERT INTO field_conditions (trigger_field, trigger_value, action_field, action_type, is_active)
             VALUES (?, ?, ?, ?, 1)"
        );

        foreach ($conditions as $condition) {
            $checkStmt->execute([
                $condition['trigger_field'],
                $condition['trigger_value'],
                $condition['action_field']
            ]);
            if ($checkStmt->fetch()) {
                echo "  ℹ Condition already exists\n";
                continue;
            }

            $insertStmt->execute([
                $condition['trigger_field'],
                $condition['trigger_value'],
                $condition['action_field'],
                $condition['action_type']
            ]);
            echo "  ✓ Created condition: {$condition['trigger_field']} = {$condition['trigger_value']} => {$condition['action_field']} ({$condition['action_type']})\n";
        }
    }

    private function columnExists($table, $column)
    {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?"
        );
        $stmt->execute([$table, $column]);
        return $stmt->fetchColumn() > 0;
    }

    private function addIndexIfNotExists($table, $indexName, $column)
    {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?"
        );
        $stmt->execute([$table, $indexName]);

        if ($stmt->fetchColumn() === 0) {
            try {
                $this->pdo->exec("CREATE INDEX $indexName ON $table ($column)");
                echo "  ✓ Created index $indexName on $table\n";
            } catch (Exception $e) {
                echo "  ⚠ Could not create index: " . $e->getMessage() . "\n";
            }
        }
    }

    private function getGroupByCode($code)
    {
        $stmt = $this->pdo->prepare("SELECT id, code, title FROM lookup_groups WHERE code = ?");
        $stmt->execute([$code]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }
}

// Run migration
$migration = new FieldConfigurationMigration();
$migration->run();

echo "\n📝 Next steps:\n";
echo "1. Create Repositories: FieldConfigRepository, FieldConditionRepository\n";
echo "2. Create Service: FieldPermissionService\n";
echo "3. Create Controller: FieldConfigController\n";
echo "4. Update existing Repositories for validation\n";
echo "5. Update Bootstrap for DI\n";
echo "6. Update Frontend for UI components\n\n";
?>

