<?php
class CustomerPortalUser extends Model
{
    public function createTableIfNotExists(): void
    {
        $sql = "CREATE TABLE IF NOT EXISTS customer_portal_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            username VARCHAR(100) NOT NULL UNIQUE,
            email VARCHAR(255) NULL,
            password_hash VARCHAR(255) NOT NULL,
            status ENUM('Active','Inactive') DEFAULT 'Active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        $this->execute($sql);
    }

    public function all(): array
    {
        return $this->fetchAll('SELECT cpu.*, c.name AS customer_name FROM customer_portal_users cpu LEFT JOIN customers c ON cpu.customer_id = c.id ORDER BY cpu.created_at DESC');
    }

    public function findByCustomerId(int $customerId): ?array
    {
        return $this->fetch('SELECT * FROM customer_portal_users WHERE customer_id = :customer_id LIMIT 1', ['customer_id' => $customerId]);
    }

    public function findByEmail(string $email): ?array
    {
        $sql = 'SELECT cpu.*, c.id AS customer_id, c.name AS customer_name, c.email AS customer_email, c.phone AS customer_phone FROM customer_portal_users cpu LEFT JOIN customers c ON cpu.customer_id = c.id WHERE cpu.email = :email LIMIT 1';
        return $this->fetch($sql, ['email' => $email]);
    }

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO customer_portal_users (customer_id, username, email, password_hash, status) VALUES (:customer_id, :username, :email, :password_hash, :status)';
        $this->execute($sql, [
            'customer_id' => $data['customer_id'],
            'username' => $data['username'],
            'email' => $data['email'] ?? null,
            'password_hash' => $data['password_hash'],
            'status' => $data['status'] ?? 'Active',
        ]);
        return $this->lastInsertId();
    }

    public function getQuotations(int $customerId): array
    {
        return $this->fetchAll('SELECT * FROM quotations WHERE customer_id = :customer_id ORDER BY created_at DESC', ['customer_id' => $customerId]);
    }

    public function getProjects(int $customerId): array
    {
        return $this->fetchAll('SELECT * FROM projects WHERE customer_id = :customer_id ORDER BY start_date DESC', ['customer_id' => $customerId]);
    }

    public function getInvoices(int $customerId): array
    {
        return $this->fetchAll('SELECT i.*, c.name AS customer_name, COALESCE(i.total_amount - i.paid_amount, 0.00) AS outstanding_amount FROM invoices i LEFT JOIN customers c ON i.customer_id = c.id WHERE i.customer_id = :customer_id ORDER BY i.created_at DESC', ['customer_id' => $customerId]);
    }

    public function getInvoiceById(int $invoiceId, int $customerId): ?array
    {
        return $this->fetch('SELECT i.*, c.name AS customer_name FROM invoices i LEFT JOIN customers c ON i.customer_id = c.id WHERE i.id = :invoice_id AND i.customer_id = :customer_id LIMIT 1', [
            'invoice_id' => $invoiceId,
            'customer_id' => $customerId,
        ]);
    }

    public function getInvoiceItems(int $invoiceId): array
    {
        return $this->fetchAll('SELECT * FROM invoice_items WHERE invoice_id = :invoice_id', ['invoice_id' => $invoiceId]);
    }

    public function findInvoiceDocument(int $invoiceId, int $customerId): ?array
    {
        return $this->fetch('SELECT * FROM documents WHERE invoice_id = :invoice_id AND customer_id = :customer_id LIMIT 1', [
            'invoice_id' => $invoiceId,
            'customer_id' => $customerId,
        ]);
    }

    public function getDocuments(int $customerId): array
    {
        return $this->fetchAll('SELECT * FROM documents WHERE customer_id = :customer_id ORDER BY created_at DESC', ['customer_id' => $customerId]);
    }

    public function findDocumentById(int $documentId, int $customerId): ?array
    {
        return $this->fetch('SELECT * FROM documents WHERE id = :id AND customer_id = :customer_id LIMIT 1', [
            'id' => $documentId,
            'customer_id' => $customerId,
        ]);
    }

    public function getProjectPhotos(int $customerId, ?int $projectId = null): array
    {
        $sql = 'SELECT pp.*, p.name AS project_name FROM project_photos pp JOIN projects p ON pp.project_id = p.id WHERE p.customer_id = :customer_id';
        $params = ['customer_id' => $customerId];

        if ($projectId) {
            $sql .= ' AND pp.project_id = :project_id';
            $params['project_id'] = $projectId;
        }

        $sql .= ' ORDER BY pp.uploaded_at DESC';

        return $this->fetchAll($sql, $params);
    }
}
