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

try {
    $pdo->exec("
        -- Documents Table
        CREATE TABLE IF NOT EXISTS documents (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT UNSIGNED NULL,
            property_id BIGINT UNSIGNED NULL,
            title VARCHAR(255) NOT NULL,
            type ENUM('allocation_letter', 'contract', 'receipt', 'other') NOT NULL,
            file_path VARCHAR(255) NOT NULL,
            uploaded_by BIGINT UNSIGNED NOT NULL,
            status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
        );

        -- Referrals Table (for Agents)
        CREATE TABLE IF NOT EXISTS referrals (
            id INT AUTO_INCREMENT PRIMARY KEY,
            agent_id BIGINT UNSIGNED NOT NULL,
            client_name VARCHAR(255) NOT NULL,
            client_email VARCHAR(255),
            client_phone VARCHAR(50),
            status ENUM('new', 'contacted', 'converted', 'closed') DEFAULT 'new',
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE
        );

        -- Commissions Table
        CREATE TABLE IF NOT EXISTS commissions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            agent_id BIGINT UNSIGNED NOT NULL,
            property_id BIGINT UNSIGNED NULL,
            amount DECIMAL(10,2) NOT NULL,
            status ENUM('pending', 'approved', 'paid') DEFAULT 'pending',
            date_earned DATE NOT NULL,
            date_paid DATE NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
        );

        -- Invoices Table (Rental Management)
        CREATE TABLE IF NOT EXISTS invoices (
            id INT AUTO_INCREMENT PRIMARY KEY,
            lease_id BIGINT UNSIGNED NOT NULL,
            tenant_id BIGINT UNSIGNED NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            due_date DATE NOT NULL,
            status ENUM('unpaid', 'paid', 'overdue', 'cancelled') DEFAULT 'unpaid',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE,
            FOREIGN KEY (tenant_id) REFERENCES users(id) ON DELETE CASCADE
        );

        -- Newsletter Subscriptions
        CREATE TABLE IF NOT EXISTS newsletter_subs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            email VARCHAR(255) NOT NULL UNIQUE,
            is_active BOOLEAN DEFAULT TRUE,
            subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        -- Audit Logs
        CREATE TABLE IF NOT EXISTS audit_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT UNSIGNED NULL,
            action VARCHAR(255) NOT NULL,
            details TEXT,
            ip_address VARCHAR(45),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- Purchase Requests
        CREATE TABLE IF NOT EXISTS purchase_requests (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT UNSIGNED NOT NULL,
            property_id BIGINT UNSIGNED NOT NULL,
            status ENUM('pending', 'reviewed', 'approved', 'rejected') DEFAULT 'pending',
            message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
        );
    ");

    echo "Database schema enhanced successfully for full enterprise specs.";

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