<?php

require_once 'Database.php';
require_once 'Logger.php';

class BaseDatabase extends Database {
    private $logger;
    private $autoLogEnabled = true;
    private $logLevel = 'ALL';

    public function __construct($logger = null, $autoLogEnabled = true) {
        parent::__construct();
        $this->logger = $logger;
        $this->autoLogEnabled = $autoLogEnabled;
    }

    public function setLogger($logger) {
        $this->logger = $logger;
    }

    public function setAutoLog($enabled) {
        $this->autoLogEnabled = $enabled;
    }

    public function setLogLevel($level) {
        $this->logLevel = strtoupper($level);
    }

    public function insert($table, $data, $module = 'database') {
        if (!$this->conn) {
            $this->getConnection();
        }

        try {
            $columns = implode(', ', array_keys($data));
            $placeholders = ':' . implode(', :', array_keys($data));
            $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";

            $stmt = $this->conn->prepare($sql);

            foreach ($data as $key => $value) {
                $stmt->bindValue(":{$key}", $value);
            }

            $result = $stmt->execute();

            if ($result && $this->shouldLog('CREATE')) {
                $insertId = $this->conn->lastInsertId();
                $this->logOperation('CREATE', $module, $insertId, $table, null, $data);
                return $insertId;
            }

            return $result ? $this->conn->lastInsertId() : false;

        } catch (PDOException $e) {
            error_log("Database insert error: " . $e->getMessage());
            return false;
        }
    }

    public function update($table, $data, $conditions, $module = 'database') {
        if (!$this->conn) {
            $this->getConnection();
        }

        try {
            $oldData = null;
            if ($this->shouldLog('UPDATE')) {
                $oldData = $this->getRecordByConditions($table, $conditions);
            }

            $setClause = [];
            foreach ($data as $key => $value) {
                $setClause[] = "{$key} = :{$key}";
            }
            $setClause = implode(', ', $setClause);

            $whereClause = [];
            $params = $data;
            foreach ($conditions as $key => $value) {
                $whereClause[] = "{$key} = :where_{$key}";
                $params["where_{$key}"] = $value;
            }
            $whereClause = implode(' AND ', $whereClause);

            $sql = "UPDATE {$table} SET {$setClause} WHERE {$whereClause}";

            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue(":{$key}", $value);
            }

            $result = $stmt->execute();

            if ($result && $this->shouldLog('UPDATE') && $oldData) {
                $recordId = $this->getRecordIdByConditions($table, $conditions);
                $this->logOperation('UPDATE', $module, $recordId, $table, $oldData, $data);
            }

            return $result;

        } catch (PDOException $e) {
            error_log("Database update error: " . $e->getMessage());
            return false;
        }
    }

    public function delete($table, $conditions, $module = 'database') {
        if (!$this->conn) {
            $this->getConnection();
        }

        try {
            $oldData = null;
            if ($this->shouldLog('DELETE')) {
                $oldData = $this->getRecordByConditions($table, $conditions);
            }

            $whereClause = [];
            $params = [];
            foreach ($conditions as $key => $value) {
                $whereClause[] = "{$key} = :{$key}";
                $params[":{$key}"] = $value;
            }
            $whereClause = implode(' AND ', $whereClause);

            $sql = "DELETE FROM {$table} WHERE {$whereClause}";

            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            $result = $stmt->execute();

            if ($result && $this->shouldLog('DELETE') && $oldData) {
                $recordId = $this->getRecordIdByConditions($table, $conditions);
                $this->logOperation('DELETE', $module, $recordId, $table, $oldData, null);
            }

            return $result;

        } catch (PDOException $e) {
            error_log("Database delete error: " . $e->getMessage());
            return false;
        }
    }

    public function select($sql, $params = []) {
        if (!$this->conn) {
            $this->getConnection();
        }

        try {
            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            error_log("Database select error: " . $e->getMessage());
            return [];
        }
    }

    public function query($sql, $params = [], $operation = null, $module = 'database') {
        if (!$this->conn) {
            $this->getConnection();
        }

        try {
            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            $result = $stmt->execute();

            if ($operation && $this->shouldLog($operation)) {
                $this->logCustomOperation($operation, $module, $sql, $params);
            }

            return $result;

        } catch (PDOException $e) {
            error_log("Database query error: " . $e->getMessage());
            return false;
        }
    }

    private function getRecordByConditions($table, $conditions) {
        try {
            $whereClause = [];
            $params = [];
            foreach ($conditions as $key => $value) {
                $whereClause[] = "{$key} = :{$key}";
                $params[":{$key}"] = $value;
            }
            $whereClause = implode(' AND ', $whereClause);

            $sql = "SELECT * FROM {$table} WHERE {$whereClause} LIMIT 1";
            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            error_log("Error getting record for logging: " . $e->getMessage());
            return null;
        }
    }

    private function getRecordIdByConditions($table, $conditions) {
        try {
            $primaryKey = $this->getPrimaryKeyColumn($table);

            $whereClause = [];
            $params = [];
            foreach ($conditions as $key => $value) {
                $whereClause[] = "{$key} = :{$key}";
                $params[":{$key}"] = $value;
            }
            $whereClause = implode(' AND ', $whereClause);

            $sql = "SELECT {$primaryKey} FROM {$table} WHERE {$whereClause} LIMIT 1";
            $stmt = $this->conn->prepare($sql);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result ? $result[$primaryKey] : null;

        } catch (PDOException $e) {
            error_log("Error getting record ID for logging: " . $e->getMessage());
            return null;
        }
    }

    private function getPrimaryKeyColumn($table) {
        $primaryKeys = [
            'branches' => 'branch_id',
            'items' => 'item_id',
            'operational_expenses' => 'expense_id',
            'orders' => 'order_id',
            'quotations' => 'quotation_id',
            'sold_items' => 'sale_id',
            'shipments' => 'shipment_id',
            'loans_laybuys' => 'record_id',
            'deposits' => 'id',
            'users' => 'user_id',
            'system_logs' => 'log_id'
        ];

        return $primaryKeys[$table] ?? 'id';
    }

    private function shouldLog($operation) {
        if (!$this->autoLogEnabled || !$this->logger) {
            return false;
        }

        switch ($this->logLevel) {
            case 'ALL':
                return true;
            case 'CRUD':
                return in_array($operation, ['CREATE', 'UPDATE', 'DELETE']);
            case 'NONE':
            default:
                return false;
        }
    }

    private function logOperation($operation, $module, $recordId, $table, $oldData, $newData) {
        if (!$this->logger) return;

        $action = $operation . '_' . strtoupper($table);
        $details = $this->buildOperationDetails($operation, $table, $oldData, $newData);

        $this->logger->log($action, $module, $details, $recordId, $table, $oldData, $newData);
    }

    private function logCustomOperation($operation, $module, $sql, $params) {
        if (!$this->logger) return;

        $details = "Custom query executed: " . substr($sql, 0, 100) . "...";
        if (!empty($params)) {
            $details .= " | Params: " . json_encode($params);
        }

        $this->logger->log('CUSTOM_' . $operation, $module, $details);
    }

    private function buildOperationDetails($operation, $table, $oldData, $newData) {
        $tableName = ucfirst(str_replace(['_', 'operational'], [' ', ''], $table));

        switch ($operation) {
            case 'CREATE':
                return "Created new record in {$tableName}";
            case 'UPDATE':
                return "Updated record in {$tableName}";
            case 'DELETE':
                return "Deleted record from {$tableName}";
            default:
                return "{$operation} operation on {$tableName}";
        }
    }

    public function beginTransaction() {
        if ($this->conn) {
            $this->conn->beginTransaction();
        }
    }

    public function commit() {
        if ($this->conn) {
            $this->conn->commit();
        }
    }

    public function rollback() {
        if ($this->conn) {
            $this->conn->rollback();
        }
    }

    public function lastInsertId() {
        return $this->conn ? $this->conn->lastInsertId() : null;
    }
}
?>