<?php
declare(strict_types=1);

// Christo Lochenberg Developments | info@christodev.co.za | +27 61 523 9126

class Jobcard {
    private Database $db;

    public function __construct() {
        $this->db = new Database();
    }

    private function generateSystemJobcardNumber(): string {
        $prefix = 'JC-' . date('ym') . '-';
        $this->db->query('SELECT jobcard_number FROM jobcards WHERE jobcard_number LIKE :prefix ORDER BY id DESC LIMIT 1');
        $this->db->bind(':prefix', $prefix . '%');
        
        $lastRecord = $this->db->single();

        if ($lastRecord) {
            $lastSequence = (int)substr($lastRecord->jobcard_number, -4);
            $newSequence = str_pad((string)($lastSequence + 1), 4, '0', STR_PAD_LEFT);
        } else {
            $newSequence = '0001';
        }

        return $prefix . $newSequence;
    }

    public function createJobcard(array $data): string|bool {
        $jobNumber = '';

        if (!empty($data['partner_id'])) {
            $this->db->query('SELECT jobcard_prefix, jobcard_sequence FROM partners WHERE id = :id');
            $this->db->bind(':id', $data['partner_id']);
            $partner = $this->db->single();

            if ($partner) {
                $prefix = !empty($partner->jobcard_prefix) ? $partner->jobcard_prefix : 'PRT-';
                $sequence = (int)$partner->jobcard_sequence;
                
                $jobNumber = $prefix . str_pad((string)$sequence, 4, '0', STR_PAD_LEFT);

                $this->db->query('UPDATE partners SET jobcard_sequence = jobcard_sequence + 1 WHERE id = :id');
                $this->db->bind(':id', $data['partner_id']);
                $this->db->execute();
            } else {
                $jobNumber = $this->generateSystemJobcardNumber();
            }
        } else {
            $jobNumber = $this->generateSystemJobcardNumber();
        }

        $status = empty($data['technician_id']) ? 'Created' : 'Assigned';

        $this->db->query('INSERT INTO jobcards (jobcard_number, client_id, technician_id, partner_id, instructions, status) VALUES (:jobcard_number, :client_id, :technician_id, :partner_id, :instructions, :status)');
        
        $this->db->bind(':jobcard_number', $jobNumber);
        $this->db->bind(':client_id', $data['client_id']);
        $this->db->bind(':technician_id', empty($data['technician_id']) ? null : $data['technician_id']);
        $this->db->bind(':partner_id', empty($data['partner_id']) ? null : $data['partner_id']);
        $this->db->bind(':instructions', $data['instructions']);
        $this->db->bind(':status', $status);

        if ($this->db->execute()) {
            return $jobNumber;
        }

        return false;
    }

    public function getJobcards(string $role, int $identifier): array {
        $sql = "SELECT j.*, 
                       c.name as client_name, 
                       t.name as tech_name, 
                       p.name as partner_name 
                FROM jobcards j
                LEFT JOIN users c ON j.client_id = c.id
                LEFT JOIN users t ON j.technician_id = t.id
                LEFT JOIN partners p ON j.partner_id = p.id ";

        if ($role === 'staff') {
            $sql .= "WHERE j.technician_id = :identifier ";
        } elseif ($role === 'client') {
            $sql .= "WHERE j.client_id = :identifier ";
        } elseif ($role === 'partner') {
            $sql .= "WHERE j.partner_id = :identifier ";
        }

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

        $this->db->query($sql);

        if (in_array($role, ['staff', 'client', 'partner'])) {
            $this->db->bind(':identifier', $identifier);
        }

        return $this->db->resultSet();
    }

    public function getCompletedJobcardsByTechnician(int $userId): array {
        $this->db->query("
            SELECT j.*, 
                   c.name as client_name, 
                   t.name as tech_name, 
                   p.name as partner_name 
            FROM jobcards j
            LEFT JOIN users c ON j.client_id = c.id
            LEFT JOIN users t ON j.technician_id = t.id
            LEFT JOIN partners p ON j.partner_id = p.id 
            WHERE j.technician_id = :user_id AND j.status IN ('Completed', 'Signed Off')
            ORDER BY j.completed_at DESC
        ");
        $this->db->bind(':user_id', $userId);
        return $this->db->resultSet();
    }

    public function getJobcardById(int $id): object|bool {
        // ADDED: c.company_name, t.contact_number as tech_contact, p.address_street as partner_street, etc.
        $this->db->query("
            SELECT j.*, 
                   c.name as client_name, c.company_name, c.email as client_email, c.contact_number as client_contact, c.address_street, c.address_suburb, c.address_city, c.address_country,
                   t.name as tech_name, t.email as tech_email, t.contact_number as tech_contact,
                   p.name as partner_name, p.email as partner_email, p.logo as partner_logo, p.contact_number as partner_contact, p.address_street as partner_street, p.address_suburb as partner_suburb, p.address_city as partner_city
            FROM jobcards j
            LEFT JOIN users c ON j.client_id = c.id
            LEFT JOIN users t ON j.technician_id = t.id
            LEFT JOIN partners p ON j.partner_id = p.id
            WHERE j.id = :id
        ");
        $this->db->bind(':id', $id);
        return $this->db->single();
    }

    public function assignTechnician(int $jobcardId, int $techId): bool {
        $this->db->query('UPDATE jobcards SET technician_id = :tech_id, status = :status WHERE id = :id');
        $this->db->bind(':tech_id', $techId);
        $this->db->bind(':status', 'Assigned');
        $this->db->bind(':id', $jobcardId);
        return $this->db->execute();
    }

    public function startJobcard(int $id): bool {
        $this->db->query('UPDATE jobcards SET status = :status, started_at = NOW() WHERE id = :id AND started_at IS NULL');
        $this->db->bind(':status', 'Started');
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function completeJobcard(int $id, string $workDone): bool {
        $this->db->query('UPDATE jobcards SET status = :status, work_done = :work_done, completed_at = NOW() WHERE id = :id');
        $this->db->bind(':status', 'Completed');
        $this->db->bind(':work_done', $workDone);
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function signOffJobcard(int $id, string $clientSignature, string $techSignature, string $clientSignName, string $techSignName): bool {
        try {
            $this->db->query("START TRANSACTION");
            $this->db->execute();

            $this->db->query('UPDATE jobcards SET status = :status, client_signature = :client_sig, tech_signature = :tech_sig, client_sign_name = :client_sign_name, tech_sign_name = :tech_sign_name WHERE id = :id');
            $this->db->bind(':status', 'Signed Off');
            $this->db->bind(':client_sig', $clientSignature);
            $this->db->bind(':tech_sig', $techSignature);
            $this->db->bind(':client_sign_name', $clientSignName);
            $this->db->bind(':tech_sign_name', $techSignName);
            $this->db->bind(':id', $id);
            $this->db->execute();

            $this->db->query("COMMIT");
            $this->db->execute();

            return true;
        } catch (Exception $e) {
            $this->db->query("ROLLBACK");
            $this->db->execute();
            error_log("Failed to sign off Jobcard ID {$id}: " . $e->getMessage());
            return false;
        }
    }

    public function addMaterial(array $data): bool {
        $this->db->query('INSERT INTO jobcard_materials (jobcard_id, material_name, quantity) VALUES (:jobcard_id, :material_name, :quantity)');
        $this->db->bind(':jobcard_id', $data['jobcard_id']);
        $this->db->bind(':material_name', $data['material_name']);
        $this->db->bind(':quantity', $data['quantity']);
        return $this->db->execute();
    }

    public function getMaterialsByJobcard(int $jobcardId): array {
        $this->db->query('SELECT * FROM jobcard_materials WHERE jobcard_id = :jobcard_id ORDER BY created_at ASC');
        $this->db->bind(':jobcard_id', $jobcardId);
        return $this->db->resultSet();
    }

    public function deleteMaterial(int $id): bool {
        $this->db->query('DELETE FROM jobcard_materials WHERE id = :id');
        $this->db->bind(':id', $id);
        return $this->db->execute();
    }

    public function getTotalStats(): array {
        $this->db->query("SELECT 
            COUNT(*) as total_jobs,
            SUM(CASE WHEN status = 'Signed Off' THEN 1 ELSE 0 END) as completed_jobs
            FROM jobcards");
        $result = $this->db->single();
        return [
            'total' => $result->total_jobs ?? 0,
            'completed' => $result->completed_jobs ?? 0
        ];
    }

    public function getDashboardAnalytics(): array {
        $this->db->query("SELECT status, COUNT(*) as count FROM jobcards GROUP BY status");
        $statusRaw = $this->db->resultSet();
        $statusData = ['Created' => 0, 'Assigned' => 0, 'Started' => 0, 'Completed' => 0, 'Signed Off' => 0];
        
        foreach($statusRaw as $s) {
            $statusData[$s->status] = (int)$s->count;
        }

        $this->db->query("
            SELECT DATE(created_at) as date_created, COUNT(*) as count 
            FROM jobcards 
            WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
            GROUP BY DATE(created_at) 
            ORDER BY DATE(created_at) ASC
        ");
        $volumeRaw = $this->db->resultSet();
        $volumeData = ['labels' => [], 'data' => []];
        
        foreach($volumeRaw as $v) {
            $volumeData['labels'][] = date('D', strtotime($v->date_created)); 
            $volumeData['data'][] = (int)$v->count;
        }

        $this->db->query("
            SELECT u.name, COUNT(j.id) as job_count 
            FROM jobcards j 
            JOIN users u ON j.technician_id = u.id 
            WHERE j.status IN ('Completed', 'Signed Off') 
            GROUP BY j.technician_id 
            ORDER BY job_count DESC 
            LIMIT 5
        ");
        $techData = $this->db->resultSet();

        return [
            'statuses' => $statusData,
            'volume' => $volumeData,
            'top_techs' => $techData
        ];
    }
}