<?php

declare(strict_types=1);

namespace App\Modules\Developer;

/**
 * DdlGeneratorService — MySQL to PostgreSQL DDL Generator
 *
 * Generates PostgreSQL-compatible SQL DDL from MySQL schema metadata.
 * All output is SQL strings — nothing is executed here.
 *
 * Type mapping follows: docs/database/MYSQL_TO_POSTGRES_MAPPING.md
 *
 * @package Inveetaire
 * @ref     DX002B
 */
class DdlGeneratorService
{
    // ─────────────────────────────────────────────────────────────────────────
    // Public API
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Generate a full PostgreSQL CREATE TABLE statement from MySQL schema data.
     *
     * @param string $tableName
     * @param array  $tableSchema  From SchemaReaderService::getMysqlFullSchema()[$table]
     * @return string  PostgreSQL DDL
     */
    public function generateCreateTable(string $tableName, array $tableSchema): string
    {
        $columns     = $tableSchema['columns']      ?? [];
        $primaryKeys = $tableSchema['primary_keys'] ?? [];
        $foreignKeys = $tableSchema['foreign_keys'] ?? [];
        $indexes     = $tableSchema['indexes']       ?? [];

        $lines = [];

        foreach ($columns as $colName => $col) {
            $lines[] = '    ' . $this->buildColumnDef($colName, $col);
        }

        // Primary key constraint
        if (!empty($primaryKeys)) {
            // Skip PK for identity columns (already defined inline)
            $nonIdentityPks = array_filter($primaryKeys, function ($pk) use ($columns) {
                $extra = strtolower($columns[$pk]['extra'] ?? '');
                return strpos($extra, 'auto_increment') === false;
            });

            if (!empty($nonIdentityPks)) {
                $lines[] = '    CONSTRAINT pk_' . $tableName
                    . ' PRIMARY KEY (' . implode(', ', $nonIdentityPks) . ')';
            }
        }

        $ddl  = "-- Table: {$tableName}\n";
        $ddl .= "CREATE TABLE IF NOT EXISTS public.{$tableName} (\n";
        $ddl .= implode(",\n", $lines) . "\n";
        $ddl .= ");\n";

        // Indexes (after table)
        foreach ($indexes as $indexName => $index) {
            $unique  = $index['unique'] ? 'UNIQUE ' : '';
            $cols    = implode(', ', $index['columns']);
            $pgName  = 'idx_' . $tableName . '_' . implode('_', $index['columns']);
            $ddl .= "CREATE {$unique}INDEX IF NOT EXISTS {$pgName}\n";
            $ddl .= "    ON public.{$tableName} ({$cols});\n";
        }

        // Foreign keys (after table — deferred for circular refs)
        foreach ($foreignKeys as $constraintName => $fk) {
            $onDelete = $fk['on_delete'] !== 'RESTRICT' ? " ON DELETE {$fk['on_delete']}" : '';
            $onUpdate = $fk['on_update'] !== 'RESTRICT' ? " ON UPDATE {$fk['on_update']}" : '';
            $ddl .= "ALTER TABLE public.{$tableName}\n";
            $ddl .= "    ADD CONSTRAINT {$constraintName}\n";
            $ddl .= "    FOREIGN KEY ({$fk['column']})\n";
            $ddl .= "    REFERENCES public.{$fk['ref_table']} ({$fk['ref_column']}){$onDelete}{$onUpdate}";
            $ddl .= "\n    DEFERRABLE INITIALLY DEFERRED;\n";
        }

        return $ddl;
    }

    /**
     * Generate ALTER TABLE SQL to add a column that's missing in Supabase.
     */
    public function generateAddColumn(string $tableName, string $colName, array $col): string
    {
        $def = $this->buildColumnDef($colName, $col);
        return "ALTER TABLE public.{$tableName} ADD COLUMN IF NOT EXISTS {$def};\n";
    }

    /**
     * Generate ALTER TABLE SQL to drop a column that's extra in Supabase.
     */
    public function generateDropColumn(string $tableName, string $colName): string
    {
        return "ALTER TABLE public.{$tableName} DROP COLUMN IF EXISTS {$colName};\n";
    }

    /**
     * Generate a complete DDL script for ALL MySQL tables.
     * Returns the full SQL string (preview-only, not executed here).
     *
     * @param array $fullSchema From SchemaReaderService::getMysqlFullSchema()
     * @return string
     */
    public function generateAllTables(array $fullSchema): string
    {
        $header  = "-- INVEETAIRE — Supabase Mirror DDL\n";
        $header .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $header .= "-- Sprint: DX002B\n";
        $header .= "-- Target Schema: public\n";
        $header .= "-- Source: MySQL → PostgreSQL\n";
        $header .= "-- IMPORTANT: Review before executing. This is auto-generated.\n\n";

        $header .= "SET search_path TO public;\n\n";

        // Tables without FKs first, then tables with FKs
        $withoutFk = [];
        $withFk    = [];
        foreach ($fullSchema as $table => $schema) {
            if (empty($schema['foreign_keys'])) {
                $withoutFk[$table] = $schema;
            } else {
                $withFk[$table] = $schema;
            }
        }

        $ddl = $header;
        $ddl .= "-- ── Tables without foreign keys ────────────────────────────────────────\n\n";
        foreach ($withoutFk as $table => $schema) {
            $ddl .= $this->generateCreateTable($table, $schema) . "\n";
        }

        $ddl .= "-- ── Tables with foreign keys (created after dependencies) ─────────────\n\n";
        // Generate tables first without FKs, then add constraints
        foreach ($withFk as $table => $schema) {
            $schemaNoFk             = $schema;
            $schemaNoFk['foreign_keys'] = [];
            $ddl .= $this->generateCreateTable($table, $schemaNoFk) . "\n";
        }

        $ddl .= "-- ── Foreign key constraints ────────────────────────────────────────────\n\n";
        foreach ($withFk as $table => $schema) {
            foreach ($schema['foreign_keys'] as $constraintName => $fk) {
                $onDelete = isset($fk['on_delete']) && $fk['on_delete'] !== 'RESTRICT'
                    ? " ON DELETE {$fk['on_delete']}" : '';
                $ddl .= "ALTER TABLE public.{$table}\n";
                $ddl .= "    ADD CONSTRAINT IF NOT EXISTS {$constraintName}\n";
                $ddl .= "    FOREIGN KEY ({$fk['column']})\n";
                $ddl .= "    REFERENCES public.{$fk['ref_table']} ({$fk['ref_column']}){$onDelete}\n";
                $ddl .= "    DEFERRABLE INITIALLY DEFERRED;\n\n";
            }
        }

        return $ddl;
    }

    /**
     * Generate incremental ALTER TABLE statements from a schema comparison diff.
     *
     * @param array $comparison From SchemaReaderService::compareFullSchema()
     * @param array $fullMysqlSchema From SchemaReaderService::getMysqlFullSchema()
     * @return string
     */
    public function generateIncrementalDdl(array $comparison, array $fullMysqlSchema): string
    {
        $ddl  = "-- INVEETAIRE — Incremental Supabase Sync DDL\n";
        $ddl .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $ddl .= "-- Sprint: DX002B\n\n";

        // 1. Create missing tables
        foreach ($comparison['missing_in_supabase'] as $table) {
            if (isset($fullMysqlSchema[$table])) {
                $ddl .= "-- Creating missing table: {$table}\n";
                $ddl .= $this->generateCreateTable($table, $fullMysqlSchema[$table]) . "\n";
            }
        }

        // 2. Add missing columns to existing tables
        foreach ($comparison['modified'] as $table => $diff) {
            foreach ($diff['column_diffs'] as $colDiff) {
                if ($colDiff['status'] === 'missing_in_supabase') {
                    $colName = $colDiff['column'];
                    if (isset($fullMysqlSchema[$table]['columns'][$colName])) {
                        $ddl .= "-- Adding column {$table}.{$colName}\n";
                        $ddl .= $this->generateAddColumn($table, $colName, $fullMysqlSchema[$table]['columns'][$colName]);
                    }
                }
            }
        }

        if ($ddl === "-- INVEETAIRE — Incremental Supabase Sync DDL\n-- Generated: " . date('Y-m-d H:i:s') . "\n-- Sprint: DX002B\n\n") {
            $ddl .= "-- No changes detected. Schema is in sync.\n";
        }

        return $ddl;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Type Mapping (public — used by SchemaReaderService comparison)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Map a MySQL column type to a PostgreSQL type string.
     *
     * @param string $columnType  Full type e.g. "varchar(191)", "tinyint(1)"
     * @param string $dataType    Base type e.g. "varchar", "tinyint"
     * @param string $extra       e.g. "auto_increment"
     * @return string  PostgreSQL type
     */
    public function mapType(string $columnType, string $dataType, string $extra = ''): string
    {
        $lower         = strtolower(trim($columnType));
        $isAutoIncr    = stripos($extra, 'auto_increment') !== false;

        // Boolean
        if ($lower === 'tinyint(1)') {
            return 'BOOLEAN';
        }

        // Auto-increment → IDENTITY
        if ($isAutoIncr) {
            if (strpos($lower, 'bigint') === 0)   return 'BIGINT GENERATED ALWAYS AS IDENTITY';
            if (strpos($lower, 'int') === 0)      return 'INTEGER GENERATED ALWAYS AS IDENTITY';
            if (strpos($lower, 'smallint') === 0) return 'SMALLINT GENERATED ALWAYS AS IDENTITY';
            if (strpos($lower, 'tinyint') === 0)  return 'SMALLINT GENERATED ALWAYS AS IDENTITY';
        }

        // Remove UNSIGNED qualifier
        $lower = str_replace(' unsigned', '', $lower);

        // Integer types
        if (strpos($lower, 'bigint') === 0)   return 'BIGINT';
        if ($lower === 'int' || strpos($lower, 'int(') === 0) return 'INTEGER';
        if (strpos($lower, 'integer') === 0)  return 'INTEGER';
        if (strpos($lower, 'mediumint') === 0) return 'INTEGER';
        if (strpos($lower, 'smallint') === 0) return 'SMALLINT';
        if (strpos($lower, 'tinyint') === 0)  return 'SMALLINT';

        // Floating point
        if ($lower === 'float')  return 'REAL';
        if ($lower === 'double') return 'DOUBLE PRECISION';
        if ($lower === 'double precision') return 'DOUBLE PRECISION';

        // Decimal
        if (preg_match('/^decimal\((\d+),(\d+)\)/', $lower, $m)) {
            return "NUMERIC({$m[1]},{$m[2]})";
        }
        if (preg_match('/^numeric\((\d+),(\d+)\)/', $lower, $m)) {
            return "NUMERIC({$m[1]},{$m[2]})";
        }

        // Date/time
        if ($lower === 'datetime')  return 'TIMESTAMP WITHOUT TIME ZONE';
        if ($lower === 'timestamp') return 'TIMESTAMP WITH TIME ZONE';
        if ($lower === 'date')      return 'DATE';
        if ($lower === 'time')      return 'TIME WITHOUT TIME ZONE';
        if ($lower === 'year')      return 'SMALLINT';

        // String
        if (preg_match('/^varchar\((\d+)\)/', $lower, $m)) {
            return "VARCHAR({$m[1]})";
        }
        if (preg_match('/^char\((\d+)\)/', $lower, $m)) {
            return "CHAR({$m[1]})";
        }
        if (in_array($lower, ['text', 'mediumtext', 'longtext', 'tinytext'], true)) {
            return 'TEXT';
        }

        // JSON
        if ($lower === 'json') return 'JSONB';

        // Binary
        if (in_array($lower, ['blob', 'mediumblob', 'longblob', 'tinyblob'], true)) {
            return 'BYTEA';
        }

        // ENUM → TEXT with note
        if (strpos($lower, 'enum') === 0) {
            return 'TEXT'; // CHECK constraint recommended — see mapping docs
        }
        if (strpos($lower, 'set') === 0) {
            return 'TEXT[]';
        }

        // Fallback to uppercase data_type
        return strtoupper($dataType ?: $columnType);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Private — Column Definition Builder
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Build a single column definition line for CREATE TABLE.
     */
    private function buildColumnDef(string $colName, array $col): string
    {
        $pgType    = $this->mapType($col['column_type'] ?? '', $col['data_type'] ?? '', $col['extra'] ?? '');
        $isIdentity = stripos($pgType, 'GENERATED ALWAYS AS IDENTITY') !== false;

        $def = "\"{$colName}\" {$pgType}";

        // NOT NULL
        if (strtoupper($col['is_nullable'] ?? 'YES') === 'NO' && !$isIdentity) {
            $def .= ' NOT NULL';
        }

        // DEFAULT value (skip for IDENTITY, boolean remapping, sequences)
        if (!$isIdentity && $col['column_default'] !== null && $col['column_default'] !== '') {
            $default = $col['column_default'];

            // Remap common MySQL defaults
            if (strtoupper($default) === 'CURRENT_TIMESTAMP') {
                $default = 'NOW()';
            } elseif ($col['column_type'] === 'tinyint(1)') {
                $default = $default === '1' ? 'TRUE' : 'FALSE';
            } elseif (is_numeric($default)) {
                // keep as-is
            } else {
                $default = "'" . addslashes($default) . "'";
            }

            $def .= " DEFAULT {$default}";
        }

        return $def;
    }
}
