<?php
require_once 'config.php';

// Helper functions
function sanitizeInput($input) {
    return htmlspecialchars(strip_tags(trim($input)));
}

function validateRequired($fields) {
    foreach ($fields as $name => $value) {
        if (empty($value)) {
            return "Полето '$name' е задължително.";
        }
    }
    return null;
}

// Function to get all articles
function getCat() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM `item` GROUP by `item`.`product_category` ORDER BY `item`.`product_category` DESC");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getAllArticles() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM item ORDER BY name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to add new article
function addArticle($name, $image, $unit, $price = 0.00) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO articles (name, image, unit, price) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$name, $image, $unit, $price]);
}

// Function to update article
function updateArticle($id, $name, $image, $unit, $price = 0.00) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("UPDATE articles SET name = ?, image= ?, unit = ?, price = ? WHERE id = ?");
    return $stmt->execute([$name, $image, $unit, $price, $id]);
}

// Function to delete article
function deleteArticle($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM articles WHERE id = ?");
    return $stmt->execute([$id]);
}

// Function to get article by ID
function getArticleById($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM articles WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to get all protocols
function getAllProtocols() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM protocols ORDER BY id DESC");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getAllProtocolsLimit() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM protocols ORDER BY id DESC LIMIT 5");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to add new protocol
function addProtocol($issuer, $executor, $protocol_date) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO protocols (issuer, executor, protocol_date) VALUES (?, ?, ?)");
    $stmt->execute([$issuer, $executor, $protocol_date]);
    return $pdo->lastInsertId();
}

// Function to get protocol by ID
function getProtocolById($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM protocols WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to delete protocol
function deleteProtocol($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM protocols WHERE id = ?");
    return $stmt->execute([$id]);
}

// Function to add protocol item
function addProtocolItem($protocol_id, $article_id, $quantity, $unit) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO protocol_items (protocol_id, article_id, quantity, unit) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$protocol_id, $article_id, $quantity, $unit]);
}

// Function to get protocol with items
function getProtocolWithItems($protocol_id) {
    $pdo = getDBConnection();
    
    // Get protocol info
    $stmt = $pdo->prepare("SELECT * FROM protocols WHERE id = ?");
    $stmt->execute([$protocol_id]);
    $protocol = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$protocol) {
        return null;
    }
    
    // Get protocol items
    $stmt = $pdo->prepare("
        SELECT pi.*, a.name as article_name 
        FROM protocol_items pi 
        JOIN articles a ON pi.article_id = a.id 
        WHERE pi.protocol_id = ?
    ");
    $stmt->execute([$protocol_id]);
    $protocol['items'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return $protocol;
}

// Function to delete protocol items by protocol ID
function deleteProtocolItems($protocol_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM protocol_items WHERE protocol_id = ?");
    return $stmt->execute([$protocol_id]);
}

// Functions for working with prices and offers

// Function to update article price
function updateArticlePrice($id, $price) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("UPDATE articles SET price = ? WHERE id = ?");
    return $stmt->execute([$price, $id]);
}

// Function to get all offers
function getAllOffers() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM offers ORDER BY id DESC");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to add new offer
function addOffer($offer_number, $issuer, $recipient, $offer_date) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO offers (offer_number, issuer, recipient, offer_date) VALUES (?, ?, ?, ?)");
    $stmt->execute([$offer_number, $issuer, $recipient, $offer_date]);
    return $pdo->lastInsertId();
}

// Function to update offer
function updateOffer($id, $offer_number, $issuer, $recipient, $offer_date) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("UPDATE offers SET offer_number = ?, issuer = ?, recipient = ?, offer_date = ? WHERE id = ?");
    return $stmt->execute([$offer_number, $issuer, $recipient, $offer_date, $id]);
}

// Function to delete offer
function deleteOffer($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM offers WHERE id = ?");
    return $stmt->execute([$id]);
}

// Function to get offer by ID
function getOfferById($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM offers WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to add offer item
function addOfferItem($offer_id, $article_id, $image, $quantity, $unit, $price_per_unit) {
    $pdo = getDBConnection();
    $total_price = $quantity * $price_per_unit;
    $stmt = $pdo->prepare("INSERT INTO offer_items (offer_id, article_id, image, quantity, unit, price_per_unit, total_price) VALUES (?, ?, ?, ?, ?, ?, ?)");
    return $stmt->execute([$offer_id, $article_id, $image, $quantity, $unit, $price_per_unit, $total_price]);
}

// Function to get offer with items
function getOfferWithItems($offer_id) {
    $pdo = getDBConnection();
    
    // Get offer info
    $stmt = $pdo->prepare("SELECT * FROM offers WHERE id = ?");
    $stmt->execute([$offer_id]);
    $offer = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$offer) {
        return null;
    }
    
    // Get offer items
    $stmt = $pdo->prepare("
        SELECT oi.*, a.name as article_name 
        FROM offer_items oi 
        JOIN item a ON oi.article_id = a.product_id 
        WHERE oi.offer_id = ?
    ");
    $stmt->execute([$offer_id]);
    $offer['items'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return $offer;
}

// Function to update offer total amount
function updateOfferTotalAmount($offer_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        UPDATE offers 
        SET total_amount = (
            SELECT COALESCE(SUM(total_price), 0) 
            FROM offer_items 
            WHERE offer_id = ?
        ) 
        WHERE id = ?
    ");
    return $stmt->execute([$offer_id, $offer_id]);
}

// Function to delete offer items by offer ID
function deleteOfferItems($offer_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM offer_items WHERE offer_id = ?");
    return $stmt->execute([$offer_id]);
}

// Function to generate unique offer number
function generateOfferNumber() {
    $year = date('Y');
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM offers WHERE YEAR(created_at) = ?");
    $stmt->execute([$year]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $next_number = $result['count'] + 1;
    return sprintf("OFF-%s-%04d", $year, $next_number);
}

// Function to validate offer number uniqueness
function isOfferNumberUnique($offer_number, $exclude_id = null) {
    $pdo = getDBConnection();
    if ($exclude_id) {
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM offers WHERE offer_number = ? AND id != ?");
        $stmt->execute([$offer_number, $exclude_id]);
    } else {
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM offers WHERE offer_number = ?");
        $stmt->execute([$offer_number]);
    }
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result['count'] == 0;
}

// Functions for working with clients

// Function to get all clients
function getAllClients() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("SELECT * FROM clients ORDER BY name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to add new client
function addClient($name, $address, $phone, $email) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO clients (name, address, phone, email) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$name, $address, $phone, $email]);
}

// Function to get client by ID
function getClientById($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM clients WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Function to update client
function updateClient($id, $name, $address, $phone, $email) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("UPDATE clients SET name = ?, address = ?, phone = ?, email = ? WHERE id = ?");
    return $stmt->execute([$name, $address, $phone, $email, $id]);
}

// Function to delete client
function deleteClient($id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM clients WHERE id = ?");
    return $stmt->execute([$id]);
}

// Function to validate email format
function validateEmail($email) {
    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

// Function to validate phone format (basic validation)
function validatePhone($phone) {
    // Allow digits, spaces, +, -, (, )
    return preg_match('/^[\d\s\+\-\(\)]+$/', $phone);
}



// Functions for working with company data

// Function to get company data
function getCompanyData() {
    try {
        $pdo = getDBConnection();
        $stmt = $pdo->query("SELECT * FROM company_data LIMIT 1");
        return $stmt->fetch(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        error_log("Error getting company data: " . $e->getMessage());
        return false;
    }
}

// Function to update company data
function updateCompanyData($company_name, $mol, $bulstat, $bank_account, $address, $phone, $email) {
    try {
        $pdo = getDBConnection();
        
        // Check if company data exists
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM company_data");
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($result['count'] > 0) {
            // Update existing record
            $stmt = $pdo->prepare("UPDATE company_data SET company_name = ?, mol = ?, bulstat = ?, bank_account = ?, address = ?, phone = ?, email = ? WHERE id = 1");
            return $stmt->execute([$company_name, $mol, $bulstat, $bank_account, $address, $phone, $email]);
        } else {
            // Insert new record
            $stmt = $pdo->prepare("INSERT INTO company_data (company_name, mol, bulstat, bank_account, address, phone, email) VALUES (?, ?, ?, ?, ?, ?)");
            return $stmt->execute([$company_name, $mol, $bulstat, $bank_account, $address, $phone, $email]);
        }
    } catch (PDOException $e) {
        error_log("Error updating company data: " . $e->getMessage());
        return false;
    }
}

// Function to get company name for display
function getCompanyName() {
    $companyData = getCompanyData();
    return $companyData ? $companyData['company_name'] : 'Моята фирма';
}

function getMol() {
    $companyData = getCompanyData();
    return $companyData ? $companyData['mol'] : '';
}

// Function to get formatted company info for documents
function getFormattedCompanyInfo() {
    $companyData = getCompanyData();
    if (!$companyData) {
        return 'Общинска охрана и СОТ - Сливен ЕООД';
    }
    
    $info = $companyData['company_name'];
    if (!empty($companyData['bulstat'])) {
        $info .= ', БУЛСТАТ: ' . $companyData['bulstat'];
    }
    if (!empty($companyData['address'])) {
        $info .= ', ' . $companyData['address'];
    }
    
    return $info;
}



// Functions for client documents

// Function to add a client document
function addClientDocument($client_id, $document_name, $file_path, $document_type) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO client_documents (client_id, document_name, file_path, document_type) VALUES (?, ?, ?, ?)");
    return $stmt->execute([$client_id, $document_name, $file_path, $document_type]);
}

// Function to get client documents by client ID
function getClientDocuments($client_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM client_documents WHERE client_id = ? ORDER BY uploaded_at DESC");
    $stmt->execute([$client_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to delete a client document
function deleteClientDocument($document_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("DELETE FROM client_documents WHERE id = ?");
    return $stmt->execute([$document_id]);
}

// Function to get offers by client ID
function getOffersByClientId($client_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM offers WHERE recipient_client_id = ? ORDER BY offer_date DESC");
    $stmt->execute([$client_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to get protocols by client ID
function getProtocolsByClientId($client_id) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT * FROM protocols WHERE issuer_client_id = ? ORDER BY protocol_date DESC");
    $stmt->execute([$client_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}


function globalSearch($query) {
    $results = [
        'clients' => [],
        'protocols' => [],
        'offers' => [],
        'documents' => []
    ];
    
    if (empty(trim($query))) {
        return $results;
    }
    
    $pdo = getDBConnection();
    $searchTerm = '%' . trim($query) . '%';
    
    // Search in clients
    $stmt = $pdo->prepare("
        SELECT id, name, address, phone, email, 'client' as type 
        FROM clients 
        WHERE name LIKE ? OR phone LIKE ? OR address LIKE ? OR email LIKE ?
        ORDER BY name
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm, $searchTerm]);
    $results['clients'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Search in protocols (by issuer and executor names)
    $stmt = $pdo->prepare("
        SELECT id, issuer, executor, protocol_date, created_at, 'protocol' as type 
        FROM protocols 
        WHERE issuer LIKE ? OR executor LIKE ?
        ORDER BY protocol_date DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm]);
    $results['protocols'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Search in offers (by issuer and recipient names)
    $stmt = $pdo->prepare("
        SELECT id, offer_number, issuer, recipient, offer_date, total_amount, 'offer' as type 
        FROM offers 
        WHERE issuer LIKE ? OR recipient LIKE ? OR offer_number LIKE ?
        ORDER BY offer_date DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm]);
    $results['offers'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Search in client documents (by document name and client name)
    $stmt = $pdo->prepare("
        SELECT cd.id, cd.document_name, cd.document_type, cd.uploaded_at, 
               c.name as client_name, c.id as client_id, 'document' as type 
        FROM client_documents cd 
        JOIN clients c ON cd.client_id = c.id 
        WHERE cd.document_name LIKE ? OR c.name LIKE ? OR c.phone LIKE ? OR c.address LIKE ?
        ORDER BY cd.uploaded_at DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm, $searchTerm]);
    $results['documents'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return $results;
}

// Function to search only in clients
function searchClients($query) {
    if (empty(trim($query))) {
        return getAllClients();
    }
    
    $pdo = getDBConnection();
    $searchTerm = '%' . trim($query) . '%';
    
    $stmt = $pdo->prepare("
        SELECT * FROM clients 
        WHERE name LIKE ? OR phone LIKE ? OR address LIKE ? OR email LIKE ?
        ORDER BY name
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm, $searchTerm]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to search only in protocols
function searchProtocols($query) {
    if (empty(trim($query))) {
        return getAllProtocols();
    }
    
    $pdo = getDBConnection();
    $searchTerm = '%' . trim($query) . '%';
    
    $stmt = $pdo->prepare("
        SELECT * FROM protocols 
        WHERE issuer LIKE ? OR executor LIKE ?
        ORDER BY protocol_date DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to search only in offers
function searchOffers($query) {
    if (empty(trim($query))) {
        return getAllOffers();
    }
    
    $pdo = getDBConnection();
    $searchTerm = '%' . trim($query) . '%';
    
    $stmt = $pdo->prepare("
        SELECT * FROM offers 
        WHERE issuer LIKE ? OR recipient LIKE ? OR offer_number LIKE ?
        ORDER BY offer_date DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Function to search in client documents
function searchClientDocuments($query) {
    if (empty(trim($query))) {
        return [];
    }
    
    $pdo = getDBConnection();
    $searchTerm = '%' . trim($query) . '%';
    
    $stmt = $pdo->prepare("
        SELECT cd.*, c.name as client_name 
        FROM client_documents cd 
        JOIN clients c ON cd.client_id = c.id 
        WHERE cd.document_name LIKE ? OR c.name LIKE ? OR c.phone LIKE ? OR c.address LIKE ?
        ORDER BY cd.uploaded_at DESC
    ");
    $stmt->execute([$searchTerm, $searchTerm, $searchTerm, $searchTerm]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}