<?php
/**
 * Upgrades to version 6.0.0
 *
 * @package blesta
 * @subpackage components.upgrades.tasks
 * @copyright Copyright (c) 2026, Phillips Data, Inc.
 * @license http://www.blesta.com/license/ The Blesta License Agreement
 * @link http://www.blesta.com/ Blesta
 */
class Upgrade6_0_0 extends UpgradeUtil
{
    /**
     * @var array An array of all tasks completed
     */
    private $tasks = [];

    /**
     * Setup
     */
    public function __construct()
    {
        Loader::loadComponents($this, ['Record']);
    }

    /**
     * Returns a numerically indexed array of tasks to execute for the upgrade process
     *
     * @return array A numerically indexed array of tasks to execute for the upgrade process
     */
    public function tasks()
    {
        return [
            'addAnalyticsTables',
            'addAnalyticsSnapshotsCron',
            'addAnalyticsSettings',
            'addAnalyticsReportsPermissions',
        ];
    }

    /**
     * Processes the given task
     *
     * @param string $task The task to process
     */
    public function process($task)
    {
        $tasks = $this->tasks();

        // Ensure task exists
        if (!in_array($task, $tasks)) {
            return;
        }

        $this->tasks[] = $task;
        $this->{$task}();
    }

    /**
     * Rolls back all tasks completed for the upgrade process
     */
    public function rollback()
    {
        // Undo all tasks
        while (($task = array_pop($this->tasks))) {
            $this->{$task}(true);
        }
    }

    /**
     * Creates the seven analytics_* tables that back the Revenue Intelligence dashboard.
     * Fresh installs reach this via the full upgrade chain.
     *
     * @param bool $undo Whether to undo the upgrade
     */
    private function addAnalyticsTables($undo = false)
    {
        if ($undo) {
            $this->Record->drop('analytics_fx_rates');
            $this->Record->drop('analytics_renewal_forecast');
            $this->Record->drop('analytics_revenue_daily');
            $this->Record->drop('analytics_mrr_movements');
            $this->Record->drop('analytics_mrr_daily');
            $this->Record->drop('analytics_service_mrr');
            $this->Record->drop('analytics_snapshot_runs');

            return;
        }

        // Run state; every other table's readability is gated on a completed run here
        $this->Record
            ->setField('id', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'auto_increment' => true])
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('snapshot_date', ['type' => 'date'])
            ->setField('status', ['type' => 'enum', 'size' => "'running','completed','failed'", 'default' => 'running'])
            ->setField('started_at', ['type' => 'datetime'])
            ->setField('completed_at', ['type' => 'datetime', 'is_null' => true, 'default' => null])
            ->setField('error', ['type' => 'text', 'is_null' => true, 'default' => null])
            ->setKey(['id'], 'primary')
            ->setKey(['company_id', 'snapshot_date'], 'unique')
            ->create('analytics_snapshot_runs', true);

        // Per-service daily MRR state (the diffable substrate for movements)
        $this->Record
            ->setField('service_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('snapshot_date', ['type' => 'date'])
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField('mrr', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('status', ['type' => 'enum', 'size' => "'active','suspended'"])
            ->setField('client_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('package_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('module_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setKey(['service_id', 'snapshot_date'], 'primary')
            ->setKey(['company_id', 'snapshot_date'], 'index')
            ->setKey(['package_id', 'snapshot_date'], 'index')
            ->create('analytics_service_mrr', true);

        // Company MRR rollup (what the charts read)
        $this->Record
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('snapshot_date', ['type' => 'date'])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField('mrr', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('suspended_mrr', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('active_services', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0])
            ->setField('suspended_services', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0])
            ->setField('active_clients', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0])
            ->setKey(['company_id', 'snapshot_date', 'currency'], 'primary')
            ->create('analytics_mrr_daily', true);

        // Movement decomposition (amounts stored as positive magnitudes)
        $this->Record
            ->setField('id', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'auto_increment' => true])
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('date', ['type' => 'date'])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField(
                'type',
                ['type' => 'enum', 'size' => "'new','expansion','contraction','churn','reactivation'"]
            )
            ->setField('amount', ['type' => 'decimal', 'size' => '19,4'])
            ->setField('service_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('client_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('package_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setKey(['id'], 'primary')
            ->setKey(['service_id', 'date', 'type'], 'unique')
            ->setKey(['company_id', 'date', 'type'], 'index')
            ->create('analytics_mrr_movements', true);

        // Dimensional cash/invoice facts
        $this->Record
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('date', ['type' => 'date'])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField(
                'dimension',
                ['type' => 'enum', 'size' => "'total','gateway','package','package_group'", 'default' => 'total']
            )
            ->setField('dimension_id', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0])
            ->setField('collected', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('refunded', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('invoiced', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('invoiced_tax', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('credited', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setKey(['company_id', 'date', 'currency', 'dimension', 'dimension_id'], 'primary')
            ->create('analytics_revenue_daily', true);

        // Forward-looking renewal forecast rollup
        $this->Record
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('computed_date', ['type' => 'date'])
            ->setField('month', ['type' => 'date'])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField('renewal_amount', ['type' => 'decimal', 'size' => '19,4', 'default' => 0])
            ->setField('renewal_count', ['type' => 'int', 'size' => 10, 'unsigned' => true, 'default' => 0])
            ->setKey(['company_id', 'computed_date', 'month', 'currency'], 'primary')
            ->create('analytics_renewal_forecast', true);

        // Snapshot-date exchange rates for consolidated restatement
        $this->Record
            ->setField('company_id', ['type' => 'int', 'size' => 10, 'unsigned' => true])
            ->setField('snapshot_date', ['type' => 'date'])
            ->setField('currency', ['type' => 'char', 'size' => 3])
            ->setField('rate', ['type' => 'decimal', 'size' => '19,8'])
            ->setKey(['company_id', 'snapshot_date', 'currency'], 'primary')
            ->create('analytics_fx_rates', true);
    }

    /**
     * Adds the "analytics_snapshots" system cron task and enables it (daily) for every company.
     * Mirrors the registration of other per-company system tasks.
     *
     * @param bool $undo Whether to undo the upgrade
     */
    private function addAnalyticsSnapshotsCron($undo = false)
    {
        Loader::loadModels($this, ['Companies', 'CronTasks']);

        if ($undo) {
            $cron = $this->CronTasks->getByKey('analytics_snapshots', null, 'system');

            if ($cron) {
                $this->Record->from('cron_task_runs')
                    ->innerJoin('cron_tasks', 'cron_tasks.id', '=', 'cron_task_runs.task_id', false)
                    ->where('cron_tasks.task_type', '=', 'system')
                    ->where('cron_task_runs.task_id', '=', $cron->id)
                    ->delete(['cron_task_runs.*']);

                $this->Record->from('cron_tasks')
                    ->where('id', '=', $cron->id)
                    ->delete();
            }

            return;
        }

        // Add the cron task, run once daily at a set time (shortly after midnight, so
        // each snapshot lands at the start of the company-local day with the previous
        // day's revenue complete)
        $task_id = $this->CronTasks->add([
            'key' => 'analytics_snapshots',
            'task_type' => 'system',
            'name' => 'CronTasks.crontask.name.analytics_snapshots',
            'description' => 'CronTasks.crontask.description.analytics_snapshots',
            'is_lang' => 1,
            'type' => 'time'
        ]);

        if ($task_id) {
            $companies = $this->Companies->getAll();
            foreach ($companies as $company) {
                // Enable the task daily for the company
                $vars = [
                    'task_id' => $task_id,
                    'company_id' => $company->id,
                    'time' => '00:00',
                    'interval' => null,
                    'enabled' => 1,
                    'date_enabled' => $this->Companies->dateToUtc(date('c'))
                ];

                $this->Record->insert('cron_task_runs', $vars);
            }
        }
    }

    /**
     * Seeds the Revenue Intelligence company settings (retention window and the minimum
     * snapshot history required before LTV is shown) for every company.
     *
     * @param bool $undo Whether to undo the upgrade
     */
    private function addAnalyticsSettings($undo = false)
    {
        Loader::loadModels($this, ['Companies']);
        $companies = $this->Companies->getAll();

        if ($undo) {
            $this->Record->query("DELETE FROM `company_settings` WHERE `key` = 'analytics_retention_days';");
            $this->Record->query("DELETE FROM `company_settings` WHERE `key` = 'analytics_ltv_min_days';");

            return;
        }

        foreach ($companies as $company) {
            $this->Companies->setSetting($company->id, 'analytics_retention_days', 90);
            $this->Companies->setSetting($company->id, 'analytics_ltv_min_days', 90);
        }
    }

    /**
     * Registers the new admin_reports actions (overviewdata, revenuedata, retentiondata,
     * renewalsdata, refresh) as permissions and seeds each staff group's ACL for them,
     * mirroring that group's current access to the Reports page (admin_reports::index).
     * This preserves today's effective access exactly. The new permissions are attached
     * to the same permission group that already owns the admin_reports permission.
     * Fresh installs reach this via the full chain.
     *
     * @param bool $undo Whether to undo the upgrade
     */
    private function addAnalyticsReportsPermissions($undo = false)
    {
        Loader::loadModels($this, ['Permissions']);
        Loader::loadComponents($this, ['Acl']);

        $actions = ['overviewdata', 'revenuedata', 'retentiondata', 'renewalsdata', 'refresh'];

        if ($undo) {
            // Removing the permission also removes its ACL entries for all AROs
            foreach ($actions as $action) {
                $permission = $this->Permissions->getByAlias('admin_reports', null, $action);
                if ($permission) {
                    $this->Permissions->delete($permission->id);
                }
            }

            return;
        }

        // Fetch all staff groups across every company
        $staff_groups = $this->Record->select(['staff_groups.*'])
            ->from('staff_groups')
            ->group(['staff_groups.id'])
            ->fetchAll();

        // Snapshot each staff group's current access to the Reports page so the new
        // permissions mirror it
        $staff_group_access = [];
        foreach ($staff_groups as $staff_group) {
            $staff_group_access[$staff_group->id] = $this->Permissions->authorized(
                'staff_group_' . $staff_group->id,
                'admin_reports',
                'index',
                'staff',
                $staff_group->company_id
            );
        }

        // Attach the new actions to the same permission group that owns the admin_reports
        // permission (currently the Billing group)
        $existing = $this->Permissions->getByAlias('admin_reports');
        $group_id = ($existing && isset($existing->group_id)) ? $existing->group_id : null;

        if (!$group_id) {
            $group = $this->Permissions->getGroupByAlias('admin_billing');
            $group_id = $group ? $group->id : null;
        }

        if ($group_id) {
            foreach ($actions as $action) {
                $this->Permissions->add([
                    'group_id' => $group_id,
                    'name' => 'StaffGroups.permissions.admin_reports_' . $action,
                    'alias' => 'admin_reports',
                    'action' => $action
                ]);

                foreach ($staff_groups as $staff_group) {
                    // Grant access to the new action if the group can already reach Reports
                    if (!empty($staff_group_access[$staff_group->id])) {
                        $this->Acl->allow('staff_group_' . $staff_group->id, 'admin_reports', $action);
                    } else {
                        $this->Acl->deny('staff_group_' . $staff_group->id, 'admin_reports', $action);
                    }
                }
            }
        }
    }
}
