customer_webhook/src/Database.php

<?php
class Database {
    private static $instance = null;
    private $connection = null;

    private function __construct() {
        try {
            $this->connection = new PDO(
                "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME,
                DB_USER,
                DB_PASS,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        } catch (PDOException $e) {
            error_log("Database Connection Error: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

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

    public function saveCallRecord($data) {
        try {
            $sql = "INSERT INTO call_records (
                id, customer_id, caller_number, called_number,
                transcription, summary, sentiment, keywords,
                created_at, updated_at
            ) VALUES (
                :id, :customer_id, :caller_number, :called_number,
                :transcription, :summary, :sentiment, :keywords,
                NOW(), NOW()
            )";

            $stmt = $this->connection->prepare($sql);
            
            return $stmt->execute([
                'id' => $data['call_id'],
                'customer_id' => $data['customer_id'],
                'caller_number' => $data['caller_number'],
                'called_number' => $data['called_number'],
                'transcription' => $data['transcription'],
                'summary' => $data['summary'],
                'sentiment' => $data['sentiment'],
                'keywords' => $data['keywords']
            ]);

        } catch (PDOException $e) {
            error_log("Database Error: " . $e->getMessage());
            throw new Exception("Failed to save call record");
        }
    }

    public function getCallRecord($callId) {
        try {
            $sql = "SELECT * FROM call_records WHERE id = :id";
            $stmt = $this->connection->prepare($sql);
            $stmt->execute(['id' => $callId]);
            return $stmt->fetch();

        } catch (PDOException $e) {
            error_log("Database Error: " . $e->getMessage());
            throw new Exception("Failed to retrieve call record");
        }
    }

    public function searchCallRecords($filters = []) {
        try {
            $sql = "SELECT * FROM call_records WHERE 1=1";
            $params = [];

            if (!empty($filters['start_date'])) {
                $sql .= " AND created_at >= :start_date";
                $params['start_date'] = $filters['start_date'];
            }

            if (!empty($filters['end_date'])) {
                $sql .= " AND created_at <= :end_date";
                $params['end_date'] = $filters['end_date'];
            }

            if (!empty($filters['caller_number'])) {
                $sql .= " AND caller_number = :caller_number";
                $params['caller_number'] = $filters['caller_number'];
            }

            if (!empty($filters['called_number'])) {
                $sql .= " AND called_number = :called_number";
                $params['called_number'] = $filters['called_number'];
            }

            $sql .= " ORDER BY created_at DESC";

            $stmt = $this->connection->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Database Error: " . $e->getMessage());
            throw new Exception("Failed to search call records");
        }
    }
}