<?php

declare(strict_types=1);

require_once __DIR__ . '/../src/db.php';

echo "Creating contract system tables...\n";

try {
    $pdo = getPDO();

    // Contract modules table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contract_modules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content_html TEXT NOT NULL,
            category TEXT NOT NULL DEFAULT 'legal',
            sort_order INTEGER DEFAULT 0,
            default_required INTEGER DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    ");

    // Contract templates table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contract_templates (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            header_html TEXT,
            footer_html TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    ");

    // Contract template modules (junction table)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contract_template_modules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_template_id INTEGER NOT NULL,
            contract_module_id INTEGER NOT NULL,
            sort_order INTEGER DEFAULT 0,
            enabled INTEGER DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (contract_template_id) REFERENCES contract_templates(id) ON DELETE CASCADE,
            FOREIGN KEY (contract_module_id) REFERENCES contract_modules(id) ON DELETE CASCADE,
            UNIQUE(contract_template_id, contract_module_id)
        )
    ");

    // Contracts table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contracts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            contract_number TEXT UNIQUE NOT NULL,
            proposal_id INTEGER,
            client_id INTEGER NOT NULL,
            contract_template_id INTEGER,
            title TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'draft',
            modules_json TEXT,
            variables_json TEXT,
            pricing_json TEXT,
            currency TEXT DEFAULT 'USD',
            client_signature_data TEXT,
            client_signed_at TEXT,
            client_ip TEXT,
            client_user_agent TEXT,
            internal_signature_data TEXT,
            internal_signed_at TEXT,
            internal_ip TEXT,
            internal_user_agent TEXT,
            signing_token_client TEXT,
            signing_token_internal TEXT,
            pdf_path TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            executed_at TEXT,
            FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE SET NULL,
            FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
            FOREIGN KEY (contract_template_id) REFERENCES contract_templates(id) ON DELETE SET NULL
        )
    ");

    // Email templates table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS email_templates (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            type TEXT NOT NULL,
            subject TEXT NOT NULL,
            body_html TEXT NOT NULL,
            variables TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    ");

    // Settings table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key TEXT UNIQUE NOT NULL,
            value TEXT,
            type TEXT DEFAULT 'text',
            description TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    ");

    echo "Tables created successfully!\n";

} catch (Exception $e) {
    echo "Error creating tables: " . $e->getMessage() . "\n";
    exit(1);
}
