<?php
/**
 * IBVTE Management System - Database Connection
 * Centralized database handler with query builder
 */

if (!defined('IBVTE_ACCESS')) {
    die('Direct access not permitted');
}

require_once __DIR__ . '/../config.php';

class Database {
    private static $instance = null;
    private $pdo;
    private $stmt; public $lastError = "";
    private $lastQuery = '';
    private $queryCount = 0;
    private $error = null;
    
    private function __construct() {
        try {
            // First try to connect without database to check if server is running
            $dsn = "mysql:host=" . DB_HOST . ";charset=" . DB_CHARSET;
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                @PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE utf8mb4_unicode_ci, time_zone = '+05:30'"
            ];
            
            $tempPdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
            
            // Check if database exists
            $stmt = $tempPdo->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" . DB_NAME . "'");
            if (!$stmt->fetch()) {
                // Database doesn't exist - create it
                $tempPdo->exec("CREATE DATABASE IF NOT EXISTS " . DB_NAME . " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            }
            
            // Now connect to the actual database
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
            $this->pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
            
        } catch (PDOException $e) {
            @logError('Database connection failed', ['error' => $e->getMessage()]);
            // Don't throw - set a flag that will be checked later
            $this->error = $e->getMessage();
            $this->pdo = null;
        }
    }
    
    /**
     * Get singleton instance
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Get PDO connection
     */
    public function getConnection() {
        return $this->pdo;
    }
    
    /**
     * Execute raw query
     */
    public function query($sql, $params = []) {
        $this->error = null;
        $this->lastQuery = $sql;
        $this->queryCount++;
        
        // Check if database connection is available
        if ($this->pdo === null) {
            $this->error = 'Database connection not available';
            return false;
        }
        
        try {
            $this->stmt = $this->pdo->prepare($sql);
            $this->stmt->execute($params);
            return $this->stmt;
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            logError('Query failed', [
                'query' => $sql,
                'params' => $params,
                'error' => $e->getMessage()
            ]);
            throw new Exception('Database query failed');
        }
    }
    
    /**
     * SELECT with conditions
     */
    public function select($table, $columns = '*', $where = [], $orderBy = '', $limit = '') {
        $sql = "SELECT " . $columns . " FROM " . $table;
        $params = [];
        
        if (!empty($where)) {
            $conditions = [];
            foreach ($where as $key => $value) {
                if (is_array($value)) {
                    // Operator condition: ['age', '>', 18]
                    $conditions[] = "$value[0] $value[1] ?";
                    $params[] = $value[2];
                } else {
                    $conditions[] = "$key = ?";
                    $params[] = $value;
                }
            }
            $sql .= " WHERE " . implode(' AND ', $conditions);
        }
        
        if (!empty($orderBy)) {
            $sql .= " ORDER BY " . $orderBy;
        }
        
        if (!empty($limit)) {
            $sql .= " LIMIT " . $limit;
        }
        
        return $this->query($sql, $params);
    }
    
    /**
     * INSERT single record
     */
    public function insert($table, $data) {
        if ($this->pdo === null) return 0;
        
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO " . $table . " (" . $columns . ") VALUES (" . $placeholders . ")";
        
        $result = $this->query($sql, array_values($data));
        if ($result === false) return 0;
        
        return $this->pdo->lastInsertId();
    }
    
    /**
     * INSERT batch records
     */
    public function insertBatch($table, $dataArray) {
        if (empty($dataArray) || $this->pdo === null) return 0;
        
        $columns = implode(', ', array_keys($dataArray[0]));
        $placeholders = '(' . implode(', ', array_fill(0, count($dataArray[0]), '?')) . ')';
        $placeholdersArray = array_fill(0, count($dataArray), $placeholders);
        
        $sql = "INSERT INTO " . $table . " (" . $columns . ") VALUES " . implode(', ', $placeholdersArray);
        
        $params = [];
        foreach ($dataArray as $data) {
            $params = array_merge($params, array_values($data));
        }
        
        $result = $this->query($sql, $params);
        if ($result === false) return 0;
        
        return $this->stmt->rowCount();
    }
    
    /**
     * UPDATE records
     */
    public function update($table, $data, $where) {
        if ($this->pdo === null) return 0;
        
        $setParts = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $setParts[] = "$key = ?";
            $params[] = $value;
        }
        
        $whereParts = [];
        foreach ($where as $key => $value) {
            $whereParts[] = "$key = ?";
            $params[] = $value;
        }
        
        $sql = "UPDATE " . $table . " SET " . implode(', ', $setParts) . " WHERE " . implode(' AND ', $whereParts);
        $result = $this->query($sql, $params);
        if ($result === false) return 0;
        
        return $this->stmt->rowCount();
    }
    
    /**
     * DELETE records
     */
    public function delete($table, $where) {
        if ($this->pdo === null) return 0;
        
        $whereParts = [];
        $params = [];
        
        foreach ($where as $key => $value) {
            $whereParts[] = "$key = ?";
            $params[] = $value;
        }
        
        $sql = "DELETE FROM " . $table . " WHERE " . implode(' AND ', $whereParts);
        $result = $this->query($sql, $params);
        if ($result === false) return 0;
        
        return $this->stmt->rowCount();
    }
    
    /**
     * Fetch single row
     */
    public function fetchOne($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        if ($stmt === false) return null;
        return $stmt->fetch();
    }
    
    /**
     * Fetch all rows
     */
    public function fetchAll($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        if ($stmt === false) return [];
        return $stmt->fetchAll();
    }
    
    /**
     * Fetch column value
     */
    public function fetchColumn($sql, $params = [], $columnIndex = 0) {
        $stmt = $this->query($sql, $params);
        if ($stmt === false) return false;
        return $stmt->fetchColumn($columnIndex);
    }
    
    /**
     * Count rows
     */
    public function count($table, $where = []) {
        $sql = "SELECT COUNT(*) FROM " . $table;
        $params = [];
        
        if (!empty($where)) {
            $conditions = [];
            foreach ($where as $key => $value) {
                $conditions[] = "$key = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $conditions);
        }
        
        return (int) $this->fetchColumn($sql, $params);
    }
    
    /**
     * Check if record exists
     */
    public function exists($table, $where) {
        return $this->count($table, $where) > 0;
    }
    
    /**
     * Get sum of column
     */
    public function sum($table, $column, $where = []) {
        $sql = "SELECT SUM(" . $column . ") FROM " . $table;
        $params = [];
        
        if (!empty($where)) {
            $conditions = [];
            foreach ($where as $key => $value) {
                $conditions[] = "$key = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $conditions);
        }
        
        $result = $this->fetchColumn($sql, $params);
        return $result ? (float) $result : 0;
    }
    
    /**
     * Begin transaction
     */
    public function beginTransaction() {
        if ($this->pdo === null) return false;
        return $this->pdo->beginTransaction();
    }
    
    /**
     * Commit transaction
     */
    public function commit() {
        if ($this->pdo === null) return false;
        return $this->pdo->commit();
    }
    
    /**
     * Rollback transaction
     */
    public function rollback() {
        if ($this->pdo === null) return false;
        return $this->pdo->rollback();
    }
    
    /**
     * Get last insert ID
     */
    public function lastInsertId() {
        if ($this->pdo === null) return 0;
        return $this->pdo->lastInsertId();
    }
    
    /**
     * Get last error
     */
    public function getLastError() {
        return $this->error;
    }
    
    /**
     * Get query count
     */
    public function getQueryCount() {
        return $this->queryCount;
    }
    
    /**
     * Get last query
     */
    public function getLastQuery() {
        return $this->lastQuery;
    }
    
    /**
     * Close connection
     */
    public function close() {
        $this->pdo = null;
        self::$instance = null;
    }
    
    /**
     * Check if database is connected
     */
    public function isConnected() {
        return $this->pdo !== null;
    }
    
    /**
     * Get connection error
     */
    public function getError() {
        return $this->error;
    }
}

/**
 * Helper function to get database instance
 */
function DB() {
    $db = Database::getInstance();
    return $db;
}

/**
 * Helper function to check and display DB connection error
 * Use this in admin pages that require database
 */
function checkDBConnection() {
    $db = Database::getInstance();
    if (!$db->isConnected()) {
        // Show user-friendly error without crashing
        if (!headers_sent()) {
            http_response_code(503);
        }
        echo '<div style="padding: 20px; background: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; border-radius: 5px; margin: 20px; font-family: Arial, sans-serif;">
            <h3 style="margin-top: 0;"><i class="fas fa-exclamation-triangle"></i> Database Connection Error</h3>
            <p>Unable to connect to the database. Please ensure:</p>
            <ul>
                <li>XAMPP MySQL service is running</li>
                <li>Database credentials are correct in config.php</li>
                <li>The database server is accessible</li>
            </ul>
            <p><strong>Technical details:</strong> ' . htmlspecialchars($db->getError()) . '</p>
        </div>';
        return false;
    }
    return $db;
}
/**
 * Generate a unique 10-digit roll number based on College and Course numeric parts
 */
function generateRollNumber($studentId) {
    $db = DB();
    // Get student with college and course details
    $s = $db->fetchOne("
        SELECT s.id, u.institution_code, c.course_code 
        FROM students s
        JOIN users u ON s.institution_id = u.id
        JOIN courses c ON s.course_id = c.id
        WHERE s.id = ?
    ", [$studentId]);

    if (!$s) return null;

    // Extract numbers from institution code (last 3 digits)
    $instNum = preg_replace('/[^0-9]/', '', $s['institution_code']);
    $instPart = substr($instNum, -3);
    if (strlen($instPart) < 3) $instPart = str_pad($instPart, 3, '0', STR_PAD_LEFT);

    // Extract numbers from course code (last 3 digits)
    $courseNum = preg_replace('/[^0-9]/', '', $s['course_code']);
    $coursePart = substr($courseNum, -3);
    if (strlen($coursePart) < 3) $coursePart = str_pad($coursePart, 3, '0', STR_PAD_LEFT);

    // Sequential part (4 digits)
    // To ensure uniqueness, we'll use a sequential ID for that specific college+course combo or just global
    // User said "roll numbner mei phle college code fir course code bhi aayega"
    $seqPart = str_pad($s['id'] % 10000, 4, '0', STR_PAD_LEFT);

    $roll = $instPart . $coursePart . $seqPart;
    
    // Ensure it's exactly 10 digits
    if (strlen($roll) > 10) $roll = substr($roll, -10);
    elseif (strlen($roll) < 10) $roll = str_pad($roll, 10, '0', STR_PAD_LEFT);

    return $roll;
}