<?php
require_once 'includes/db.php';

try {
    // 1. Update Users Table for New Roles
    // Current enum: 'admin', 'agent', 'client', 'finance', 'operations'
    // New roles: 'super_admin', 'estate_manager', 'sales_manager', 'sales_agent' (mapped to agent), 'finance_officer' (mapped to finance), 'operations_officer' (mapped to operations)
    // We will expand the ENUM.
    $pdo->exec("ALTER TABLE users MODIFY COLUMN role ENUM('super_admin', 'admin', 'estate_manager', 'sales_manager', 'agent', 'client', 'finance', 'operations') NOT NULL DEFAULT 'client'");
    echo "Updated users role ENUM.<br>";

    // 2. Add Soft Deletes (deleted_at) to key tables
    $tables_to_update = ['users', 'properties', 'clients', 'leases', 'allocations'];
    foreach ($tables_to_update as $table) {
        // Check if column exists first to avoid error
        $stmt = $pdo->query("SHOW COLUMNS FROM $table LIKE 'deleted_at'");
        if ($stmt->rowCount() == 0) {
            $pdo->exec("ALTER TABLE $table ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL");
            echo "Added deleted_at to $table.<br>";
        }
    }

    // 3. Create Support Tickets Table (Client Portal)
    $pdo->exec("CREATE TABLE IF NOT EXISTS support_tickets (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        subject VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
        priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )");
    echo "Created support_tickets table.<br>";

    // 4. Create Maintenance Requests Table (Rental Module)
    $pdo->exec("CREATE TABLE IF NOT EXISTS maintenance_requests (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        property_id BIGINT UNSIGNED NOT NULL,
        tenant_id BIGINT UNSIGNED NOT NULL,
        issue_type VARCHAR(100) NOT NULL,
        description TEXT NOT NULL,
        status ENUM('pending', 'scheduled', 'completed', 'cancelled') DEFAULT 'pending',
        priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
        scheduled_date DATE NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
        FOREIGN KEY (tenant_id) REFERENCES users(id) ON DELETE CASCADE
    )");
    echo "Created maintenance_requests table.<br>";

    // 5. Create Tasks Table (Agent/Admin Workflow)
    $pdo->exec("CREATE TABLE IF NOT EXISTS tasks (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        assigned_to BIGINT UNSIGNED NOT NULL,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        due_date DATE,
        status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
        priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
        created_by BIGINT UNSIGNED,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE CASCADE
    )");
    echo "Created tasks table.<br>";

    // 6. Create Agent Targets Table (Gamification/Performance)
    $pdo->exec("CREATE TABLE IF NOT EXISTS agent_targets (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        agent_id BIGINT UNSIGNED NOT NULL,
        month INT NOT NULL,
        year INT NOT NULL,
        target_amount DECIMAL(15, 2) DEFAULT 0.00,
        achieved_amount DECIMAL(15, 2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE
    )");
    echo "Created agent_targets table.<br>";

    // 7. Create Payment Schedules Table (Installments)
    $pdo->exec("CREATE TABLE IF NOT EXISTS payment_schedules (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        allocation_id BIGINT UNSIGNED,
        lease_id BIGINT UNSIGNED,
        amount DECIMAL(15, 2) NOT NULL,
        due_date DATE NOT NULL,
        status ENUM('pending', 'paid', 'overdue') DEFAULT 'pending',
        paid_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Created payment_schedules table.<br>";

    echo "Database schema updated successfully for Premium Edition.";

} catch (PDOException $e) {
    die("DB Error: " . $e->getMessage());
}
?>