<?php
/**
 * Enterprise HRM ERP System - Create Missing Tables
 * This script creates missing tables required for login
 * DELETE THIS FILE AFTER USE FOR SECURITY
 */

require_once __DIR__ . '/includes/init.php';

$errors = [];
$success = [];

try {
    $db = getDB();
    $db->beginTransaction();
    
    // Check and create permissions table
    $stmt = $db->query("SHOW TABLES LIKE 'permissions'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `permissions` (
              `permission_id` int(11) NOT NULL AUTO_INCREMENT,
              `permission_code` varchar(100) NOT NULL,
              `permission_name` varchar(255) NOT NULL,
              `permission_module` varchar(100) NOT NULL,
              `permission_description` text,
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`permission_id`),
              UNIQUE KEY `permission_code` (`permission_code`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'permissions' table";
    } else {
        $success[] = "'permissions' table already exists";
    }
    
    // Check and create role_permissions table
    $stmt = $db->query("SHOW TABLES LIKE 'role_permissions'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `role_permissions` (
              `role_permission_id` int(11) NOT NULL AUTO_INCREMENT,
              `role_id` int(11) NOT NULL,
              `permission_id` int(11) NOT NULL,
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`role_permission_id`),
              UNIQUE KEY `unique_role_permission` (`role_id`,`permission_id`),
              KEY `fk_role_permission_role` (`role_id`),
              KEY `fk_role_permission_permission` (`permission_id`),
              CONSTRAINT `fk_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE,
              CONSTRAINT `fk_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`permission_id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'role_permissions' table";
    } else {
        $success[] = "'role_permissions' table already exists";
    }
    
    // Check and create roles table if missing
    $stmt = $db->query("SHOW TABLES LIKE 'roles'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `roles` (
              `role_id` int(11) NOT NULL AUTO_INCREMENT,
              `role_code` varchar(50) NOT NULL,
              `role_name` varchar(100) NOT NULL,
              `role_description` text,
              `is_system_role` tinyint(1) DEFAULT 0,
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`role_id`),
              UNIQUE KEY `role_code` (`role_code`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'roles' table";
        
        // Insert default roles
        $db->exec("
            INSERT IGNORE INTO `roles` (`role_id`, `role_code`, `role_name`, `role_description`, `is_system_role`) VALUES
            (1, 'super_admin', 'Super Administrator', 'Full system access', 1),
            (2, 'hr_admin', 'HR Administrator', 'HR management access', 1),
            (3, 'hr_manager', 'HR Manager', 'HR operations management', 1),
            (4, 'hr_officer', 'HR Officer', 'HR daily operations', 1),
            (5, 'manager', 'Manager', 'Department manager', 0),
            (6, 'employee', 'Employee', 'Regular employee', 0)
        ");
        $success[] = "Inserted default roles";
    } else {
        $success[] = "'roles' table already exists";
    }
    
    // Check and create sites table if missing
    $stmt = $db->query("SHOW TABLES LIKE 'sites'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `sites` (
              `site_id` int(11) NOT NULL AUTO_INCREMENT,
              `site_code` varchar(50) NOT NULL,
              `site_name` varchar(255) NOT NULL,
              `site_address` text,
              `site_phone` varchar(50),
              `site_email` varchar(255),
              `site_status` enum('active','inactive') DEFAULT 'active',
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`site_id`),
              UNIQUE KEY `site_code` (`site_code`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'sites' table";
        
        // Insert default site
        $db->exec("
            INSERT IGNORE INTO `sites` (`site_id`, `site_code`, `site_name`, `site_status`) VALUES
            (1, 'MAIN', 'Main Office', 'active')
        ");
        $success[] = "Inserted default site";
    } else {
        $success[] = "'sites' table already exists";
    }
    
    // Check and create user_sessions table if missing
    $stmt = $db->query("SHOW TABLES LIKE 'user_sessions'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `user_sessions` (
              `session_id` varchar(128) NOT NULL,
              `user_id` int(11) NOT NULL,
              `ip_address` varchar(45),
              `user_agent` text,
              `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`session_id`),
              KEY `fk_session_user` (`user_id`),
              CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'user_sessions' table";
    } else {
        $success[] = "'user_sessions' table already exists";
    }
    
    // Check and create user_sites table if missing
    $stmt = $db->query("SHOW TABLES LIKE 'user_sites'");
    if ($stmt->rowCount() == 0) {
        $db->exec("
            CREATE TABLE IF NOT EXISTS `user_sites` (
              `user_site_id` int(11) NOT NULL AUTO_INCREMENT,
              `user_id` int(11) NOT NULL,
              `site_id` int(11) NOT NULL,
              `is_primary` tinyint(1) DEFAULT 0,
              `assigned_at` datetime DEFAULT CURRENT_TIMESTAMP,
              `assigned_by` int(11),
              PRIMARY KEY (`user_site_id`),
              UNIQUE KEY `unique_user_site` (`user_id`,`site_id`),
              KEY `fk_user_site_user` (`user_id`),
              KEY `fk_user_site_site` (`site_id`),
              KEY `fk_user_site_assigned_by` (`assigned_by`),
              CONSTRAINT `fk_user_site_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
              CONSTRAINT `fk_user_site_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE,
              CONSTRAINT `fk_user_site_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        $success[] = "Created 'user_sites' table";
    } else {
        $success[] = "'user_sites' table already exists";
    }
    
    $db->commit();
    
} catch (Exception $e) {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
    $errors[] = "Error: " . $e->getMessage();
    error_log("Create missing tables error: " . $e->getMessage());
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Create Missing Tables</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            display: flex;
            justify-content: center;
            align-items: center;
            padding: 20px;
        }
        .container {
            background: white;
            padding: 40px;
            border-radius: 12px;
            box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1);
            max-width: 600px;
            width: 100%;
        }
        h1 {
            color: #333;
            margin-bottom: 10px;
        }
        .subtitle {
            color: #666;
            margin-bottom: 30px;
        }
        .alert {
            padding: 15px;
            border-radius: 6px;
            margin-bottom: 15px;
        }
        .alert-error {
            background: #fee2e2;
            color: #991b1b;
            border-left: 4px solid #dc2626;
        }
        .alert-success {
            background: #d1fae5;
            color: #065f46;
            border-left: 4px solid #10b981;
        }
        .success-item {
            padding: 8px 0;
            border-bottom: 1px solid #e5e7eb;
        }
        .success-item:last-child {
            border-bottom: none;
        }
        .btn {
            width: 100%;
            padding: 12px;
            background: #1e293b;
            color: white;
            border: none;
            border-radius: 6px;
            font-size: 16px;
            font-weight: 500;
            cursor: pointer;
            margin-top: 20px;
            text-decoration: none;
            display: inline-block;
            text-align: center;
        }
        .btn:hover {
            background: #1d4ed8;
        }
        .warning {
            background: #fef3c7;
            color: #92400e;
            padding: 15px;
            border-radius: 6px;
            margin-top: 20px;
            border-left: 4px solid #f59e0b;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Create Missing Tables</h1>
        <p class="subtitle">Database Setup for Login System</p>
        
        <?php if (!empty($errors)): ?>
        <div class="alert alert-error">
            <?php foreach ($errors as $error): ?>
                <div><?php echo htmlspecialchars($error); ?></div>
            <?php endforeach; ?>
        </div>
        <?php endif; ?>
        
        <?php if (!empty($success)): ?>
        <div class="alert alert-success">
            <strong>✓ Success!</strong>
            <?php foreach ($success as $msg): ?>
                <div class="success-item"><?php echo htmlspecialchars($msg); ?></div>
            <?php endforeach; ?>
        </div>
        
        <div class="warning">
            <strong>⚠️ IMPORTANT:</strong><br>
            Delete this file (create_missing_tables.php) immediately for security
        </div>
        
        <a href="<?php echo BASE_URL; ?>/login.php" class="btn">Go to Login Page</a>
        <?php endif; ?>
    </div>
</body>
</html>
