<?php
require_once 'config.php';

try {
    $db = db();
    $sql_properties = "
    CREATE TABLE IF NOT EXISTS properties (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT NOT NULL,
        rent_amount DECIMAL(10, 2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );";
    $db->exec($sql_properties);

    $sql_tenants = "
    CREATE TABLE IF NOT EXISTS tenants (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        phone VARCHAR(20),
        property_id INT,
        lease_start DATE,
        lease_end DATE,
        rent_due DECIMAL(10, 2),
        security_deposit DECIMAL(10, 2),
        status VARCHAR(50) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
    );";
    $db->exec($sql_tenants);

    $sql_payments = "
    CREATE TABLE IF NOT EXISTS payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT,
        property_id INT,
        amount DECIMAL(10, 2) NOT NULL,
        payment_date DATE NOT NULL,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
    );";
    $db->exec($sql_payments);

    $sql_maintenance = "
    CREATE TABLE IF NOT EXISTS maintenance_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        property_id INT,
        tenant_id INT,
        description TEXT NOT NULL,
        status VARCHAR(50) DEFAULT 'Open',
        reported_date DATE NOT NULL,
        completed_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL,
        FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
    );";
    $db->exec($sql_maintenance);

    $sql_files = "
    CREATE TABLE IF NOT EXISTS files (
        id INT AUTO_INCREMENT PRIMARY KEY,
        file_name VARCHAR(255) NOT NULL,
        file_path VARCHAR(255) NOT NULL,
        property_id INT,
        tenant_id INT,
        payment_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
        FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
        FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
    );";
    $db->exec($sql_files);

    $sql_users = "
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) NOT NULL DEFAULT 'user', -- 'user', 'admin'
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );";
    $db->exec($sql_users);

    // Seed the database with a default admin user
    $admin_user = 'admin';
    $admin_pass = password_hash('password', PASSWORD_DEFAULT);
    $stmt = $db->prepare('INSERT IGNORE INTO users (username, password, role) VALUES (?, ?, ?)');
    $stmt->execute([$admin_user, $admin_pass, 'admin']);

    echo "Tables 'properties', 'tenants', 'payments', 'maintenance_requests', 'files', and 'users' created successfully.";
} catch (PDOException $e) {
    die("DB ERROR: ". $e->getMessage());
}
?>