<?php

namespace Tempo\Purchasing\Delivery;

/** Proposal only. Installation never executes these statements automatically. */
final class SqlPrivileges
{
    public const TABLES = [
        'cb_centres' => 'SELECT, INSERT', 'cb_origins' => 'SELECT, INSERT', 'cb_budgets' => 'SELECT, INSERT, UPDATE',
        'cb_budget_entries' => 'SELECT, INSERT', 'cb_audit' => 'SELECT, INSERT', 'cb_ui_commands' => 'SELECT, INSERT',
        'cb_budget_consumptions' => 'SELECT', 'cb_budget_reinforcements' => 'SELECT', 'cb_consumption_sources' => 'SELECT',
        'ir_requests' => 'SELECT, INSERT, UPDATE', 'ir_steps' => 'SELECT, INSERT, UPDATE', 'ir_policies' => 'SELECT, INSERT, UPDATE',
        'ir_events' => 'SELECT, INSERT', 'ir_commands' => 'SELECT, INSERT', 'ir_policy_versions' => 'SELECT, INSERT', 'sgc_installations' => 'SELECT',
    ];

    public static function plan(string $runtime): array
    {
        if (! preg_match('/^[a-z][a-z0-9_]{0,62}$/D', $runtime)) {
            throw new \RuntimeException('Role runtime explícita inválida.');
        }
        $sql = [];
        foreach (self::TABLES as $table => $rights) {
            // New objects can inherit wider ACLs from the destination owner's
            // defaults. Scope the correction to these new objects only; never
            // change existing tables or ALTER DEFAULT PRIVILEGES globally.
            $sql[] = 'REVOKE ALL PRIVILEGES ON TABLE public."'.$table.'" FROM "'.$runtime.'"';
            $sql[] = 'REVOKE ALL PRIVILEGES ON TABLE public."'.$table.'" FROM PUBLIC';
            $sql[] = 'GRANT '.$rights.' ON TABLE public."'.$table.'" TO "'.$runtime.'"';
            if (str_contains($rights, 'INSERT')) {
                $sql[] = 'REVOKE ALL PRIVILEGES ON SEQUENCE public."'.$table.'_id_seq" FROM "'.$runtime.'"';
                $sql[] = 'REVOKE ALL PRIVILEGES ON SEQUENCE public."'.$table.'_id_seq" FROM PUBLIC';
                $sql[] = 'GRANT USAGE ON SEQUENCE public."'.$table.'_id_seq" TO "'.$runtime.'"';
            } elseif ($table !== 'sgc_installations') {
                $sql[] = 'REVOKE ALL PRIVILEGES ON SEQUENCE public."'.$table.'_id_seq" FROM "'.$runtime.'"';
                $sql[] = 'REVOKE ALL PRIVILEGES ON SEQUENCE public."'.$table.'_id_seq" FROM PUBLIC';
            }
        }

        return ['runtime_role' => $runtime, 'confirmation_required' => true, 'automatically_applied' => false, 'statements' => $sql, 'excluded' => ['CREATE', 'ownership', 'DELETE', 'UPDATE on append-only history', 'all-tables/default-privileges grants']];
    }
}