<?php
// Migration runner - creates membership linking tables if they don't exist
require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/src/config/env.php';
require_once __DIR__ . '/src/config/functions.php';

$conn = openDatabaseConnection();

if (!$conn) {
    die("Database connection failed\n");
}

echo "Connected to database successfully.\n\n";

// Check if membership_links table exists
$checkTable = $conn->query("SHOW TABLES LIKE 'membership_links'");
if ($checkTable->num_rows > 0) {
    echo "✓ membership_links table already exists\n";
} else {
    echo "Creating membership_links table...\n";
    
    $createLink = $conn->query("
        CREATE TABLE IF NOT EXISTS `membership_links` (
            `link_id` INT AUTO_INCREMENT PRIMARY KEY,
            `primary_user_id` INT NOT NULL,
            `secondary_user_id` INT NOT NULL,
            `relationship` VARCHAR(50) NOT NULL DEFAULT 'spouse',
            `linked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            
            CONSTRAINT `fk_membership_links_primary` FOREIGN KEY (`primary_user_id`) 
                REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT `fk_membership_links_secondary` FOREIGN KEY (`secondary_user_id`) 
                REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
            
            INDEX `idx_primary_user` (`primary_user_id`),
            INDEX `idx_secondary_user` (`secondary_user_id`),
            
            UNIQUE KEY `unique_link` (`primary_user_id`, `secondary_user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    if ($createLink) {
        echo "✓ membership_links table created successfully\n";
    } else {
        echo "✗ Error creating membership_links table: " . $conn->error . "\n";
    }
}

// Check if membership_permissions table exists
$checkTable = $conn->query("SHOW TABLES LIKE 'membership_permissions'");
if ($checkTable->num_rows > 0) {
    echo "✓ membership_permissions table already exists\n";
} else {
    echo "Creating membership_permissions table...\n";
    
    $createPerm = $conn->query("
        CREATE TABLE IF NOT EXISTS `membership_permissions` (
            `permission_id` INT AUTO_INCREMENT PRIMARY KEY,
            `link_id` INT NOT NULL,
            `permission_name` VARCHAR(100) NOT NULL,
            `granted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            
            CONSTRAINT `fk_membership_permissions_link` FOREIGN KEY (`link_id`) 
                REFERENCES `membership_links`(`link_id`) ON DELETE CASCADE ON UPDATE CASCADE,
            
            INDEX `idx_link` (`link_id`),
            
            UNIQUE KEY `unique_permission` (`link_id`, `permission_name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    
    if ($createPerm) {
        echo "✓ membership_permissions table created successfully\n";
    } else {
        echo "✗ Error creating membership_permissions table: " . $conn->error . "\n";
    }
}

// Create or replace the view
echo "\nCreating linked_membership_users view...\n";
$createView = $conn->query("
    CREATE OR REPLACE VIEW `linked_membership_users` AS
    SELECT 
        primary_user_id,
        secondary_user_id,
        relationship,
        linked_at
    FROM membership_links
    UNION ALL
    SELECT 
        primary_user_id,
        primary_user_id as secondary_user_id,
        'primary' as relationship,
        linked_at
    FROM membership_links
");

if ($createView) {
    echo "✓ View created successfully\n";
} else {
    echo "✗ Error creating view: " . $conn->error . "\n";
}

$conn->close();
echo "\n✓ Migration completed successfully!\n";
?>