<?php
// Migration script to update posts and invoices tables from client_id to owner_id

require_once __DIR__ . '/../config/bootstrap.php';

use SSCI\Classes\Database;

// Load database configuration
$dbConfig = require __DIR__ . '/../config/database.php';

// Create database instance
$db = new Database($dbConfig);

echo "Starting owner_id migration...\n";

try {
    // Check invoices table structure
    $columns = $db->fetchAll("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'invoices' AND TABLE_SCHEMA = DATABASE()");
    $columnNames = array_column($columns, 'COLUMN_NAME');
    
    if (in_array('client_id', $columnNames)) {
        echo "Migrating invoices table...\n";
        
        // Drop foreign key constraint
        $db->query("ALTER TABLE invoices DROP FOREIGN KEY invoices_ibfk_1");
        echo "- Dropped old foreign key\n";
        
        // Rename column
        $db->query("ALTER TABLE invoices CHANGE COLUMN client_id owner_id INT NOT NULL");
        echo "- Renamed client_id to owner_id\n";
        
        // Drop old index
        $db->query("ALTER TABLE invoices DROP INDEX client_id");
        echo "- Dropped old index\n";
        
        // Add new foreign key
        $db->query("ALTER TABLE invoices ADD CONSTRAINT invoices_owner_fk FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE");
        echo "- Added new foreign key to users table\n";
        
        // Add new index
        $db->query("ALTER TABLE invoices ADD INDEX owner_id (owner_id)");
        echo "- Added new index\n";
        
        echo "✓ invoices table migrated successfully\n";
    } else {
        echo "✓ invoices table already has owner_id, skipping...\n";
    }
    
    // Check posts table structure
    $columns = $db->fetchAll("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'posts' AND TABLE_SCHEMA = DATABASE()");
    $columnNames = array_column($columns, 'COLUMN_NAME');
    
    if (in_array('client_id', $columnNames)) {
        echo "\nMigrating posts table...\n";
        
        // Drop foreign key if exists
        $db->query("ALTER TABLE posts DROP FOREIGN KEY posts_ibfk_2");
        echo "- Dropped client_id foreign key\n";
        
        // Drop index if exists
        $db->query("ALTER TABLE posts DROP INDEX client_id");
        echo "- Dropped client_id index\n";
        
        // Drop column
        $db->query("ALTER TABLE posts DROP COLUMN client_id");
        echo "- Removed client_id column\n";
        
        echo "✓ posts table migrated successfully\n";
    } else {
        echo "✓ posts table already has client_id removed, skipping...\n";
    }
    
    echo "\n✓ Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "✗ Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>
