<?php

namespace App\Models;

use CodeIgniter\Model;

class OpportunityModel extends Model
{
    protected $table         = 'opportunities';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';

    protected $allowedFields = [
        'name', 'company_id', 'contact_id', 'sales_stage_id', 'competitor_id',
        'expected_revenue', 'probability', 'expected_close_date', 'status',
        'description', 'created_by', 'updated_by',
    ];

    protected $validationRules = [
        'name'         => 'required|min_length[2]|max_length[200]',
        'probability'  => 'permit_empty|integer|greater_than_equal_to[0]|less_than_equal_to[100]',
        'status'       => 'permit_empty|in_list[open,won,lost,abandoned]',
    ];

    public function scopeForList(
        ?string $search = null,
        ?string $status = null,
        ?int $stageId = null
    ): self {
        $this->select('opportunities.*, companies.name as company_name, sales_stages.name as stage_name')
            ->join('companies', 'companies.id = opportunities.company_id', 'left')
            ->join('sales_stages', 'sales_stages.id = opportunities.sales_stage_id', 'left');

        if ($search) {
            $this->groupStart()
                ->like('opportunities.name', $search)
                ->orLike('companies.name', $search)
                ->orLike('sales_stages.name', $search)
                ->groupEnd();
        }

        if ($status) {
            $this->where('opportunities.status', $status);
        }

        if ($stageId) {
            $this->where('opportunities.sales_stage_id', $stageId);
        }

        return $this;
    }

    /**
     * Open opportunities with weighted revenue for expected-revenue reporting.
     */
    public function scopeForWeightedRevenue(?int $stageId = null, ?int $companyId = null): self
    {
        $this->select(
            'opportunities.*, companies.name as company_name, sales_stages.name as stage_name,'
            . ' (opportunities.expected_revenue * opportunities.probability / 100) AS weighted_revenue'
        )
            ->join('companies', 'companies.id = opportunities.company_id', 'left')
            ->join('sales_stages', 'sales_stages.id = opportunities.sales_stage_id', 'left')
            ->where('opportunities.status', 'open');

        if ($stageId) {
            $this->where('opportunities.sales_stage_id', $stageId);
        }

        if ($companyId) {
            $this->where('opportunities.company_id', $companyId);
        }

        return $this;
    }

    public function getOpen(): array
    {
        return $this->where('status', 'open')
            ->orderBy('expected_close_date', 'ASC')
            ->orderBy('name', 'ASC')
            ->findAll();
    }
}
