<?php
// src/Database.php
class Database {
    private $pdo;
    private $config;

    public function __construct() {
        $this->config = require __DIR__ . '/../config/database.php';
        $this->createConnection();
        $this->createTablesIfNotExist();
    }

    private function createConnection() {
        $dsn = "mysql:host={$this->config['host']};charset=utf8";
        try {
            $this->pdo = new PDO($dsn, $this->config['username'], $this->config['password']);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // Create database if not exists
            $this->pdo->exec("CREATE DATABASE IF NOT EXISTS `{$this->config['dbname']}`");
            
            // Select the database
            $this->pdo->exec("USE `{$this->config['dbname']}`");
        } catch (PDOException $e) {
            die('Database connection failed: ' . $e->getMessage());
        }
    }

    private function createTablesIfNotExist() {
        try {
            // Users table
            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(255) NOT NULL UNIQUE,
                    email VARCHAR(255) NOT NULL UNIQUE,
                    password VARCHAR(255) NOT NULL,
                    full_name VARCHAR(255),
                    role ENUM('admin', 'staff', 'viewer') DEFAULT 'viewer',
                    status ENUM('active', 'inactive') DEFAULT 'active',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX(username),
                    INDEX(email),
                    INDEX(role)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");

            // Create other existing tables if needed
            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS ussd_mpesa_credentials (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    business_name VARCHAR(255),
                    paybill_number VARCHAR(10) NOT NULL,
                    shortcode VARCHAR(10),
                    account_ref VARCHAR(50),
                    transaction_type VARCHAR(50),
                    consumer_key VARCHAR(255),
                    consumer_secret VARCHAR(255),
                    passkey VARCHAR(255),
                    party_b VARCHAR(50),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY(paybill_number)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
            
            // Ensure ussd_mpesa_credentials table has all required columns
            $this->ensurePaybillColumns();

            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS mpesa_stk_queue (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    phone VARCHAR(20) NOT NULL,
                    amount DECIMAL(10, 2),
                    paybill_ref VARCHAR(50),
                    checkout_id VARCHAR(100),
                    is_paid ENUM('yes', 'no') DEFAULT 'no',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX(phone),
                    INDEX(is_paid),
                    INDEX(checkout_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");

            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS contacts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    phone_number VARCHAR(20) NOT NULL UNIQUE,
                    name VARCHAR(255),
                    status VARCHAR(50),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX(phone_number)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
            
            // Ensure contacts table has all required columns
            $this->ensureContactsColumns();

            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS loan_requests (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    msisdn VARCHAR(20) NOT NULL,
                    id_number VARCHAR(50) NOT NULL,
                    loan_amount DECIMAL(10, 2),
                    status ENUM('pending', 'approved', 'rejected', 'disbursed') DEFAULT 'pending',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX(msisdn),
                    INDEX(status),
                    INDEX(created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");

        } catch (PDOException $e) {
            error_log('Error creating tables: ' . $e->getMessage());
        }
    }

    private function ensureContactsColumns() {
        try {
            // Check if created_at column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM contacts LIKE 'created_at'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE contacts ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
            }
            
            // Check if updated_at column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM contacts LIKE 'updated_at'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE contacts ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
            }
        } catch (PDOException $e) {
            error_log('Error ensuring contacts columns: ' . $e->getMessage());
        }
    }

    private function ensurePaybillColumns() {
        try {
            // Check if transaction_type column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM ussd_mpesa_credentials LIKE 'transaction_type'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE ussd_mpesa_credentials ADD COLUMN transaction_type VARCHAR(50)");
            }
            
            // Check if consumer_key column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM ussd_mpesa_credentials LIKE 'consumer_key'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE ussd_mpesa_credentials ADD COLUMN consumer_key VARCHAR(255)");
            }
            
            // Check if consumer_secret column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM ussd_mpesa_credentials LIKE 'consumer_secret'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE ussd_mpesa_credentials ADD COLUMN consumer_secret VARCHAR(255)");
            }
            
            // Check if passkey column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM ussd_mpesa_credentials LIKE 'passkey'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE ussd_mpesa_credentials ADD COLUMN passkey VARCHAR(255)");
            }
            
            // Check if party_b column exists
            $result = $this->pdo->query("SHOW COLUMNS FROM ussd_mpesa_credentials LIKE 'party_b'");
            if ($result->rowCount() === 0) {
                $this->pdo->exec("ALTER TABLE ussd_mpesa_credentials ADD COLUMN party_b VARCHAR(50)");
            }
        } catch (PDOException $e) {
            error_log('Error ensuring paybill columns: ' . $e->getMessage());
        }
    }

    public function getConnection() {
        return $this->pdo;
    }
}
