<?php
require_once dirname(__DIR__) . '/config/config.php';
require_once dirname(__DIR__) . '/config/database.php';

$db = db();
try {
    echo "Starting Roles & Permissions Synchronization...\n";
    
    // Disable foreign key checks for table creation
    $db->exec("SET FOREIGN_KEY_CHECKS=0");

    // 1. Create Tables
    echo "Creating 'roles' table...\n";
    $db->exec("CREATE TABLE IF NOT EXISTS `roles` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `tenant_id` INT UNSIGNED NOT NULL,
      `name` VARCHAR(100) NOT NULL,
      `description` TEXT,
      `is_system` TINYINT(1) DEFAULT 0,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `tenant_id` (`tenant_id`),
      FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    echo "Creating 'permissions' table...\n";
    $db->exec("CREATE TABLE IF NOT EXISTS `permissions` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(100) NOT NULL,
      `display_name` VARCHAR(150) NOT NULL,
      `description` TEXT,
      `category` VARCHAR(50) NOT NULL,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`),
      KEY `category` (`category`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    echo "Creating 'role_permissions' table...\n";
    $db->exec("CREATE TABLE IF NOT EXISTS `role_permissions` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `role_id` INT UNSIGNED NOT NULL,
      `permission_id` INT UNSIGNED NOT NULL,
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `role_permission` (`role_id`, `permission_id`),
      FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
      FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // 2. Alter Existing Tables
    echo "Updating 'users' table...\n";
    $result = $db->query("SHOW COLUMNS FROM `users` LIKE 'role_id'");
    if (!$result->fetch()) {
        $db->exec("ALTER TABLE `users` ADD COLUMN `role_id` INT UNSIGNED DEFAULT NULL AFTER `role`, ADD INDEX (`role_id`), ADD FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL");
    }

    echo "Updating 'sales' table...\n";
    $db->exec("ALTER TABLE sales MODIFY COLUMN payment_status ENUM('paid','partial','due','credit') DEFAULT 'paid'");

    echo "Updating 'purchases' table...\n";
    $db->exec("ALTER TABLE purchases MODIFY COLUMN payment_status ENUM('paid','partial','due','credit') DEFAULT 'paid'");

    // 3. Seed Permissions
    echo "Seeding default permissions...\n";
    $permissions = [
        ['dashboard.view', 'View Dashboard', 'Access to dashboard overview', 'overview'],
        ['products.view', 'View Products', 'View products list', 'inventory'],
        ['products.create', 'Create Products', 'Create new products', 'inventory'],
        ['products.edit', 'Edit Products', 'Edit existing products', 'inventory'],
        ['products.delete', 'Delete Products', 'Delete products', 'inventory'],
        ['categories.manage', 'Manage Categories', 'Manage product categories', 'inventory'],
        ['units.manage', 'Manage Units', 'Manage measurement units', 'inventory'],
        ['stock.view', 'View Stock', 'View stock levels', 'inventory'],
        ['sales.view', 'View Sales', 'View sales history', 'transactions'],
        ['sales.create', 'Create Sales', 'Create new sales', 'transactions'],
        ['pos.access', 'Access POS Terminal', 'Access POS terminal', 'transactions'],
        ['purchases.view', 'View Purchases', 'View purchase history', 'transactions'],
        ['purchases.create', 'Create Purchases', 'Create new purchases', 'transactions'],
        ['customers.view', 'View Customers', 'View customers list', 'contacts'],
        ['customers.create', 'Create Customers', 'Create new customers', 'contacts'],
        ['suppliers.view', 'View Suppliers', 'View suppliers list', 'contacts'],
        ['suppliers.create', 'Create Suppliers', 'Create new suppliers', 'contacts'],
        ['reports.view', 'View Reports', 'Access reports and analytics', 'reports'],
        ['users.manage', 'Manage Users', 'Manage user accounts', 'admin'],
        ['roles.manage', 'Manage Roles', 'Manage roles and permissions', 'admin'],
        ['settings.view', 'View Settings', 'Access settings', 'admin'],
        ['settings.edit', 'Edit Settings', 'Edit business settings', 'admin'],
        ['locations.manage', 'Manage Locations', 'Manage business locations', 'admin']
    ];

    $stmt = $db->prepare("INSERT IGNORE INTO `permissions` (`name`, `display_name`, `description`, `category`) VALUES (?, ?, ?, ?)");
    foreach ($permissions as $p) {
        $stmt->execute($p);
    }

    $db->exec("SET FOREIGN_KEY_CHECKS=1");
    echo "\nRoles & Permissions Synchronization Completed Successfully!\n";
} catch (Exception $e) {
    echo "Sync Error: " . $e->getMessage() . "\n";
}
