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

try {
    // 1. Rename workflow_stage to status and set ENUM
    // Check if column exists first to avoid error if already renamed
    $stmt = $pdo->query("SHOW COLUMNS FROM documents LIKE 'workflow_stage'");
    if ($stmt->fetch()) {
        $pdo->exec("ALTER TABLE documents CHANGE COLUMN workflow_stage status ENUM('draft', 'pending', 'approved', 'issued', 'signed', 'rejected', 'archived') DEFAULT 'draft'");
        echo "Renamed 'workflow_stage' to 'status' and updated ENUM.\n";
    } else {
        // If status exists, just modify it
        $pdo->exec("ALTER TABLE documents MODIFY COLUMN status ENUM('draft', 'pending', 'approved', 'issued', 'signed', 'rejected', 'archived') DEFAULT 'draft'");
        echo "Updated 'status' ENUM.\n";
    }

    // 2. Add uploaded_by if missing (schema showed it missing in Describe output? No wait, Describe showed it? Let me check)
    // Describe output: id, user_id, deal_id, title, type, file_path, description, created_at, updated_at, version, workflow_stage, is_locked.
    // uploaded_by is MISSING.
    
    $stmt = $pdo->query("SHOW COLUMNS FROM documents LIKE 'uploaded_by'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE documents ADD COLUMN uploaded_by INT AFTER file_path");
        echo "Added 'uploaded_by' column.\n";
    }
    
    // 3. Add property_id if missing (Describe output: missing)
    $stmt = $pdo->query("SHOW COLUMNS FROM documents LIKE 'property_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE documents ADD COLUMN property_id INT AFTER user_id");
        echo "Added 'property_id' column.\n";
    }

    // 4. Create Document Versions Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS document_versions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        document_id BIGINT UNSIGNED NOT NULL,
        version_number VARCHAR(10) NOT NULL,
        file_path VARCHAR(255) NOT NULL,
        changelog TEXT,
        uploaded_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
    )");
    
    echo "Checked/Created 'document_versions' table.\n";

    // 5. Create Document Approvals Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS document_approvals (
        id INT AUTO_INCREMENT PRIMARY KEY,
        document_id BIGINT UNSIGNED NOT NULL,
        approver_id INT,
        status VARCHAR(50) NOT NULL,
        comments TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
    )");
    echo "Checked/Created 'document_approvals' table.\n";

    echo "Document workflow tables setup successfully.\n";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>