<?php
// includes/functions.php

function get_work_orders_by_status($status) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE status = ? ORDER BY created_at DESC");
    $stmt->execute([$status]);
    return $stmt->fetchAll();
}

function get_all_work_orders() {
    $pdo = db();
    $stmt = $pdo->query("SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id ORDER BY created_at DESC");
    return $stmt->fetchAll();
}

function get_work_order_by_id($id) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician_name FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE wo.id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function get_kpis($work_orders) {
    $total_jobs = count($work_orders);
    $completed_jobs = 0;
    foreach ($work_orders as $order) {
        if ($order['status'] === 'Completed') {
            $completed_jobs++;
        }
    }
    $completion_rate = $total_jobs > 0 ? ($completed_jobs / $total_jobs) * 100 : 0;

    return [
        'total_jobs' => $total_jobs,
        'completion_rate' => round($completion_rate),
        'revenue' => '12,345' // Static for now
    ];
}

function create_work_order($customer_name, $customer_email, $job_type, $status, $address, $technician_id) {
    $pdo = db();
    $sql = "INSERT INTO work_orders (customer_name, customer_email, job_type, status, address, assigned_technician_id, uuid) VALUES (?, ?, ?, ?, ?, ?, UUID())";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$customer_name, $customer_email, $job_type, $status, $address, $technician_id]);
}

function update_work_order($id, $customer_name, $customer_email, $job_type, $status, $address, $technician_id) {
    $pdo = db();
    $completed_at_sql = ($status === 'Completed') ? ", completed_at = CURRENT_TIMESTAMP" : "";
    $sql = "UPDATE work_orders SET customer_name = ?, customer_email = ?, job_type = ?, status = ?, address = ?, assigned_technician_id = ? {$completed_at_sql} WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$customer_name, $customer_email, $job_type, $status, $address, $technician_id, $id]);
}

function dispatch_work_order($id) {
    $pdo = db();
    $sql = "UPDATE work_orders SET status = 'In Progress', dispatched_at = CURRENT_TIMESTAMP WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$id]);
}

function get_work_order_by_uuid($uuid) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT wo.*, u.username as assigned_technician_name FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id WHERE wo.uuid = ?");
    $stmt->execute([$uuid]);
    return $stmt->fetch();
}

function search_work_orders($search_term = '', $status = '') {
    $pdo = db();
    $sql = "SELECT wo.*, u.username as assigned_technician FROM work_orders wo LEFT JOIN users u ON wo.assigned_technician_id = u.id";
    $params = [];
    $where_clauses = [];

    if (!empty($search_term)) {
        $where_clauses[] = "(wo.customer_name LIKE ? OR wo.job_type LIKE ? OR wo.address LIKE ? OR u.username LIKE ?)";
        $search_param = "%{$search_term}%";
        array_push($params, $search_param, $search_param, $search_param, $search_param);
    }

    if (!empty($status)) {
        $where_clauses[] = "wo.status = ?";
        $params[] = $status;
    }

    if (!empty($where_clauses)) {
        $sql .= " WHERE " . implode(' AND ', $where_clauses);
    }

    $sql .= " ORDER BY wo.created_at DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

// --- User & Auth Functions ---

function create_user($username, $email, $password, $role) {
    $pdo = db();
    $hash = password_hash($password, PASSWORD_DEFAULT);
    $sql = "INSERT INTO users (username, email, password, role) VALUES (?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$username, $email, $hash, $role]);
}

function get_user_by_username($username) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
    return $stmt->fetch();
}

function get_user_by_id($id) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function get_users_by_role($role) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT id, username FROM users WHERE role = ? ORDER BY username ASC");
    $stmt->execute([$role]);
    return $stmt->fetchAll();
}

function start_secure_session() {
    $session_name = 'sec_session_id';   // Set a custom session name
    $secure = true; // Set to true if using https.
    $httponly = true; // This stops javascript being able to access the session id. 
    
    ini_set('session.use_only_cookies', 1); // Forces sessions to only use cookies. 
    $cookieParams = session_get_cookie_params(); 
    session_set_cookie_params($cookieParams["lifetime"], $cookieParams["path"], $cookieParams["domain"], $secure, $httponly); 
    
    session_name($session_name);
    session_start();
    session_regenerate_id();
}

function is_logged_in() {
    return isset($_SESSION['user_id']);
}

function require_login() {
    if (!is_logged_in()) {
        header('Location: /login.php');
        exit();
    }
}

function get_user_role() {
    return isset($_SESSION['role']) ? $_SESSION['role'] : null;
}

// --- Reporting Functions ---

function get_work_order_counts_by_status() {
    $pdo = db();
    $sql = "SELECT status, COUNT(*) as count FROM work_orders GROUP BY status";
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
}

function get_work_order_counts_by_technician() {
    $pdo = db();
    $sql = "SELECT u.username, COUNT(wo.id) as count FROM work_orders wo JOIN users u ON wo.assigned_technician_id = u.id GROUP BY u.username";
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
}

function get_average_completion_time() {
    $pdo = db();
    $sql = "SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)) as avg_hours FROM work_orders WHERE completed_at IS NOT NULL";
    $stmt = $pdo->query($sql);
    $result = $stmt->fetch();
    return $result ? round($result['avg_hours'], 1) : 0;
}

// --- Inventory Functions ---

function get_inventory_items() {
    $pdo = db();
    $stmt = $pdo->query("SELECT * FROM inventory ORDER BY item_name ASC");
    return $stmt->fetchAll();
}

function get_inventory_item_by_id($id) {
    $pdo = db();
    $stmt = $pdo->prepare("SELECT * FROM inventory WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

function add_inventory_item($item_name, $quantity, $price) {
    $pdo = db();
    $sql = "INSERT INTO inventory (item_name, quantity, price) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$item_name, $quantity, $price]);
}

function update_inventory_item($id, $item_name, $quantity, $price) {
    $pdo = db();
    $sql = "UPDATE inventory SET item_name = ?, quantity = ?, price = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$item_name, $quantity, $price, $id]);
}

function delete_inventory_item($id) {
    $pdo = db();
    $sql = "DELETE FROM inventory WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$id]);
}

function get_work_order_parts($work_order_id) {
    $pdo = db();
    $sql = "SELECT i.item_name, wp.quantity_used, i.price, wp.id as part_id FROM work_order_parts wp JOIN inventory i ON wp.inventory_id = i.id WHERE wp.work_order_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$work_order_id]);
    return $stmt->fetchAll();
}

function add_part_to_work_order($work_order_id, $inventory_id, $quantity_used) {
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $sql = "INSERT INTO work_order_parts (work_order_id, inventory_id, quantity_used) VALUES (?, ?, ?)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$work_order_id, $inventory_id, $quantity_used]);

        $sql = "UPDATE inventory SET quantity = quantity - ? WHERE id = ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$quantity_used, $inventory_id]);

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        return false;
    }
}

function remove_part_from_work_order($part_id) {
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $sql = "SELECT inventory_id, quantity_used FROM work_order_parts WHERE id = ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$part_id]);
        $part = $stmt->fetch();

        if ($part) {
            $sql = "UPDATE inventory SET quantity = quantity + ? WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$part['quantity_used'], $part['inventory_id']]);

            $sql = "DELETE FROM work_order_parts WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$part_id]);
        }

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        return false;
    }
}