<?php
defined('BASEPATH') or exit('No direct script access allowed');

/**
 * DB utility: run migrations and seeders under /database/*.
 *
 * Endpoints:
 *   /admin/db                  — usage help
 *   /admin/db/migrate          — apply pending SQL migrations (database/migrations/*.sql)
 *   /admin/db/migration_status — list all migrations and their status
 *   /admin/db/seed             — run all seeders (database/seeders/*Seeder.php)
 *   /admin/db/seed/CustomerSeeder — run a single seeder by class name
 */
class Db extends AdminController
{
    private $migrationsPath;
    private $seedersPath;
    private $migrationsTable = 'schema_migrations';

    public function __construct()
    {
        parent::__construct();
        if (!is_admin()) {
            access_denied('Db utility');
        }
        $this->migrationsPath = FCPATH . 'database/migrations/';
        $this->seedersPath    = FCPATH . 'database/seeders/';
        $this->ensureMigrationsTable();
    }

    public function index()
    {
        $this->wrap(function () {
            echo "DB utility\n\n";
            echo "  /admin/db/migrate          — apply pending SQL migrations\n";
            echo "  /admin/db/migration_status — show which migrations ran\n";
            echo "  /admin/db/seed             — run all seeders\n";
            echo "  /admin/db/seed/<Class>     — run one seeder (e.g. CustomerSeeder)\n";
        });
    }

    // ============ Migrations ============
    public function migrate()
    {
        $this->wrap(function () {
            $files = $this->pendingMigrations();
            if (empty($files)) {
                echo "Nothing to migrate — schema is up to date.\n";
                return;
            }
            foreach ($files as $file) {
                $sql = file_get_contents($this->migrationsPath . $file);
                if (trim($sql) === '') {
                    echo "  · $file (empty, skipped)\n";
                    $this->recordMigration($file);
                    continue;
                }
                foreach (array_filter(array_map('trim', explode(';', $sql))) as $stmt) {
                    $this->db->query($stmt);
                }
                $this->recordMigration($file);
                echo "  + $file\n";
            }
            echo "\nMigration complete.\n";
        });
    }

    public function migration_status()
    {
        $this->wrap(function () {
            $applied = $this->appliedMigrations();
            $files   = $this->allMigrationFiles();
            if (empty($files)) {
                echo "No migration files in database/migrations/.\n";
                return;
            }
            foreach ($files as $file) {
                echo (in_array($file, $applied) ? "  ✓ " : "  · ") . $file . "\n";
            }
        });
    }

    // ============ Seeders ============
    public function seed($only = null)
    {
        $this->wrap(function () use ($only) {
            require_once FCPATH . 'database/seeders/BaseSeeder.php';

            if ($only) {
                $file = $this->seedersPath . $only . '.php';
                if (!file_exists($file)) {
                    echo "Seeder not found: $only\n";
                    return;
                }
                require_once $file;
                echo "Running $only...\n";
                (new $only())->run();
                echo "\nDone.\n";
                return;
            }

            // Explicit order — respects FK-ish dependencies.
            $order = ['DepartmentSeeder', 'StaffSeeder', 'CustomerSeeder', 'TicketSeeder'];
            foreach ($order as $cls) {
                $file = $this->seedersPath . $cls . '.php';
                if (!file_exists($file)) continue;
                require_once $file;
                echo "Running $cls...\n";
                (new $cls())->run();
                echo "\n";
            }
            echo "All seeders finished.\n";
        });
    }

    // ============ Helpers ============
    private function wrap(callable $fn)
    {
        echo '<pre style="padding:20px;font-family:monospace;line-height:1.5;">';
        $fn();
        echo '</pre>';
    }

    private function ensureMigrationsTable()
    {
        $this->db->query('CREATE TABLE IF NOT EXISTS ' . db_prefix() . $this->migrationsTable . ' (
            filename VARCHAR(191) NOT NULL PRIMARY KEY,
            applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        )');
    }

    private function allMigrationFiles()
    {
        if (!is_dir($this->migrationsPath)) return [];
        $files = array_diff(scandir($this->migrationsPath), ['.', '..']);
        $files = array_filter($files, function ($f) { return substr($f, -4) === '.sql'; });
        sort($files);
        return array_values($files);
    }

    private function appliedMigrations()
    {
        $rows = $this->db->get(db_prefix() . $this->migrationsTable)->result_array();
        return array_column($rows, 'filename');
    }

    private function pendingMigrations()
    {
        return array_values(array_diff($this->allMigrationFiles(), $this->appliedMigrations()));
    }

    private function recordMigration($filename)
    {
        $this->db->insert(db_prefix() . $this->migrationsTable, [
            'filename'   => $filename,
            'applied_at' => date('Y-m-d H:i:s'),
        ]);
    }
}
