<?php
// member/fix-sqlite.php - Fix SQLite database structure
require_once '../config.php';
requireLogin();

$pdo = getDBConnection();
$output = [];

// Check dpia_records table
$result = $pdo->query("PRAGMA table_info(dpia_records)");
$columns = $result->fetchAll(PDO::FETCH_ASSOC);
$columnNames = array_column($columns, 'name');
$output[] = "dpia_records columns: " . implode(', ', $columnNames);

// Add missing columns
$missingColumns = [
    'user_id' => 'INTEGER',
    'organization_id' => 'INTEGER',
    'dpia_reference' => 'TEXT',
    'project_name' => 'TEXT NOT NULL DEFAULT ""',
    'project_description' => 'TEXT',
    'processing_activity' => 'TEXT',
    'data_categories' => 'TEXT',
    'data_subjects' => 'TEXT',
    'retention_period' => 'TEXT',
    'third_party_sharing' => 'INTEGER DEFAULT 0',
    'third_party_details' => 'TEXT',
    'risk_identification' => 'TEXT',
    'risk_assessment' => 'TEXT',
    'mitigation_measures' => 'TEXT',
    'risk_level' => 'TEXT DEFAULT "Medium"',
    'compliance_score' => 'INTEGER DEFAULT 0',
    'consultation_required' => 'INTEGER DEFAULT 0',
    'consultation_date' => 'TEXT',
    'approval_status' => 'TEXT DEFAULT "draft"',
    'approved_by' => 'INTEGER',
    'approval_date' => 'TEXT',
    'status' => 'TEXT DEFAULT "draft"',
    'created_at' => 'TEXT',
    'updated_at' => 'TEXT',
    'completed_at' => 'TEXT',
    'reviewed_at' => 'TEXT',
    'notes' => 'TEXT'
];

foreach ($missingColumns as $colName => $colType) {
    if (!in_array($colName, $columnNames)) {
        try {
            $pdo->exec("ALTER TABLE dpia_records ADD COLUMN $colName $colType");
            $output[] = "✅ Added column: $colName";
        } catch (Exception $e) {
            $output[] = "⚠️ Could not add $colName: " . $e->getMessage();
        }
    } else {
        $output[] = "✓ Column exists: $colName";
    }
}

// Create missing tables
$tables = [
    'compliance_responses' => "CREATE TABLE IF NOT EXISTS compliance_responses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        organization_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        question_id INTEGER NOT NULL,
        response TEXT NOT NULL,
        response_score INTEGER DEFAULT 0,
        assessment_date TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )",
    'compliance_scores' => "CREATE TABLE IF NOT EXISTS compliance_scores (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        organization_id INTEGER NOT NULL,
        overall_score INTEGER DEFAULT 0,
        dpia_score INTEGER DEFAULT 0,
        breach_readiness_score INTEGER DEFAULT 0,
        processor_management_score INTEGER DEFAULT 0,
        odpc_alignment_score INTEGER DEFAULT 0,
        data_protection_score INTEGER DEFAULT 0,
        risk_level TEXT DEFAULT 'Medium',
        assessment_date TEXT NOT NULL,
        next_assessment_date TEXT,
        recommendations TEXT,
        assessed_by INTEGER,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )",
    'user_activity' => "CREATE TABLE IF NOT EXISTS user_activity (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        action TEXT,
        details TEXT,
        ip_address TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )",
    'website_scan_cache' => "CREATE TABLE IF NOT EXISTS website_scan_cache (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        domain TEXT NOT NULL UNIQUE,
        scan_data TEXT,
        scan_date DATETIME DEFAULT CURRENT_TIMESTAMP
    )"
];

foreach ($tables as $tableName => $sql) {
    try {
        $pdo->exec($sql);
        $output[] = "✅ Table ready: $tableName";
    } catch (Exception $e) {
        $output[] = "⚠️ Error creating $tableName: " . $e->getMessage();
    }
}

// Show all tables
$tables = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
$output[] = "<br><strong>All tables:</strong> " . implode(', ', array_column($tables, 'name'));

echo "<h2>Database Fix Results</h2>";
foreach ($output as $line) {
    echo "<div style='font-family:monospace;padding:4px;'>$line</div>";
}
echo "<p><a href='dashboard.php' class='btn'>Go to Dashboard</a></p>";
?>