<?php

namespace App\Models\Control;

use CodeIgniter\Model;
use Config\Lookups;

/**
 * Control DB. Tenant ↔ plan assignment history — see
 * CreateSubscriptionsTable for why rows accumulate rather than update in
 * place. currentFor() is the only place "which row counts right now"
 * should be decided (most recent by id).
 */
class SubscriptionModel extends Model
{
    protected $table            = 'subscriptions';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';
    protected $useTimestamps    = true;

    protected $allowedFields = [
        'tenant_id',
        'plan_id',
        'status',
        'trial_ends_at',
        'current_period_start',
        'current_period_end',
        'cancelled_at',
    ];

    public function __construct()
    {
        parent::__construct();

        $statuses = array_keys((new Lookups())->subscriptionStatuses);

        $this->validationRules = [
            'tenant_id' => 'required|integer',
            'plan_id'   => 'required|integer',
            'status'    => 'required|in_list[' . implode(',', $statuses) . ']',
        ];
    }

    public function currentFor(int $tenantId): ?object
    {
        return $this->where('tenant_id', $tenantId)->orderBy('id', 'desc')->first();
    }

    /**
     * Joined with plans so list screens (tenant detail, dashboard) get
     * plan name/price in one query instead of N+1.
     */
    public function currentWithPlanFor(int $tenantId): ?object
    {
        return $this->select('subscriptions.*, plans.name as plan_name, plans.price as plan_price, plans.billing_interval as plan_interval')
            ->join('plans', 'plans.id = subscriptions.plan_id')
            ->where('subscriptions.tenant_id', $tenantId)
            ->orderBy('subscriptions.id', 'desc')
            ->first();
    }

    /**
     * The id of each tenant's most recent subscription row — "which row
     * counts right now" for aggregate purposes (countByStatus, MRR).
     * Built via $this->builder() directly rather than
     * $this->select(...)->findColumn(...): Model::findColumn() calls
     * select() again internally, so chaining it after our own select()
     * doesn't replace it, it appends — producing
     * `SELECT MAX(id) as id, id FROM ... GROUP BY tenant_id`, which MySQL's
     * default sql_mode (ONLY_FULL_GROUP_BY) rejects because that second,
     * unaggregated `id` isn't in the GROUP BY. Found by this 500ing on the
     * very first dashboard load.
     *
     * @return list<int>
     */
    private function latestSubscriptionIds(): array
    {
        $rows = $this->builder()
            ->select('MAX(id) as latest_id')
            ->groupBy('tenant_id')
            ->get()
            ->getResultArray();

        return array_map('intval', array_column($rows, 'latest_id'));
    }

    public function countByStatus(string $status): int
    {
        // Counts distinct tenants whose most recent subscription row has
        // this status — not every historical row, or an upgraded tenant
        // would be double-counted (once as their old plan, once as new).
        $latestIds = $this->latestSubscriptionIds();

        if ($latestIds === []) {
            return 0;
        }

        return $this->whereIn('id', $latestIds)->where('status', $status)->countAllResults();
    }

    /**
     * Monthly recurring revenue estimate: sum of each active subscription's
     * plan price, with yearly plans divided by 12 so they're comparable.
     * Dashboard-only figure — never used for actual billing math (that's
     * always the plan's real price/interval, see InvoiceModel).
     */
    public function estimatedMrr(): string
    {
        $latestIds = $this->latestSubscriptionIds();

        if ($latestIds === []) {
            return '0.00';
        }

        $rows = $this->select('subscriptions.id, plans.price, plans.billing_interval')
            ->join('plans', 'plans.id = subscriptions.plan_id')
            ->whereIn('subscriptions.id', $latestIds)
            ->where('subscriptions.status', 'active')
            ->findAll();

        $total = '0.00';

        foreach ($rows as $row) {
            $monthly = $row->billing_interval === 'yearly'
                ? number_format((float) $row->price / 12, 2, '.', '')
                : $row->price;

            $total = function_exists('bcadd') ? bcadd($total, $monthly, 2) : number_format((float) $total + (float) $monthly, 2, '.', '');
        }

        return $total;
    }
}
