<?php

namespace App\Services;

use App\Models\SourceDatabase;
use App\Models\Table;
use App\Models\Column;
use App\Models\Index;
use App\Models\ForeignKey;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;

class SchemaExtractionService
{
    public function __construct(
        private DatabaseConnectionService $connectionService
    ) {}

    /**
     * Extract all tables from source database
     */
    public function extractTables(SourceDatabase $database): Collection
    {
        $config = $database->getConnectionConfig();
        $connection = $this->connectionService->getConnection($config);

        try {
            if ($database->driver === 'mssql') {
                return $this->extractTablesMssql($connection);
            }
            
            return $this->extractTablesPgsql($connection);
        } finally {
            $this->connectionService->purgeConnection($connection->getName());
        }
    }

    /**
     * Extract tables from PostgreSQL
     */
    private function extractTablesPgsql($connection): Collection
    {
        $tables = $connection->select("
            SELECT
                table_schema,
                table_name,
                obj_description(
                    (quote_ident(table_schema) || '.' || quote_ident(table_name))::regclass::oid
                ) as comment
            FROM information_schema.tables
            WHERE table_schema NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            AND table_type = 'BASE TABLE'
            ORDER BY table_schema, table_name
        ");

        return collect($tables);
    }

    /**
     * Extract tables from MSSQL
     */
    private function extractTablesMssql($connection): Collection
    {
        $tables = $connection->select("
            SELECT 
                TABLE_SCHEMA as table_schema,
                TABLE_NAME as table_name,
                CAST(ep.value AS NVARCHAR(MAX)) as comment
            FROM INFORMATION_SCHEMA.TABLES t
            LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
            LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
            WHERE t.TABLE_TYPE = 'BASE TABLE'
            AND t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'sys')
            ORDER BY TABLE_SCHEMA, TABLE_NAME
        ");

        return collect($tables);
    }

    /**
     * Extract columns for a specific table
     */
    public function extractColumns(SourceDatabase $database, string $schema, string $table): Collection
    {
        $config = $database->getConnectionConfig();
        $connection = $this->connectionService->getConnection($config);

        try {
            if ($database->driver === 'mssql') {
                return $this->extractColumnsMssql($connection, $schema, $table);
            }
            
            return $this->extractColumnsPgsql($connection, $schema, $table);
        } finally {
            $this->connectionService->purgeConnection($connection->getName());
        }
    }

    /**
     * Extract columns from PostgreSQL
     */
    private function extractColumnsPgsql($connection, string $schema, string $table): Collection
    {
        $columns = $connection->select("
            SELECT
                c.column_name,
                c.data_type,
                c.udt_name,
                c.ordinal_position,
                CASE WHEN c.is_nullable = 'YES' THEN true ELSE false END as is_nullable,
                c.column_default,
                c.character_maximum_length,
                c.numeric_precision,
                c.numeric_scale,
                c.datetime_precision,
                pgd.description as comment,
                CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_key,
                CASE WHEN fk.column_name IS NOT NULL THEN true ELSE false END as is_foreign_key,
                CASE WHEN idx.column_name IS NOT NULL THEN true ELSE false END as is_indexed
            FROM information_schema.columns c
            LEFT JOIN pg_catalog.pg_statio_all_tables AS st ON c.table_schema = st.schemaname AND c.table_name = st.relname
            LEFT JOIN pg_catalog.pg_description pgd ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
            LEFT JOIN (
                SELECT kcu.column_name
                FROM information_schema.table_constraints tc
                JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
                WHERE tc.constraint_type = 'PRIMARY KEY'
                AND tc.table_schema = ?
                AND tc.table_name = ?
            ) pk ON c.column_name = pk.column_name
            LEFT JOIN (
                SELECT DISTINCT kcu.column_name
                FROM information_schema.table_constraints tc
                JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
                WHERE tc.constraint_type = 'FOREIGN KEY'
                AND tc.table_schema = ?
                AND tc.table_name = ?
            ) fk ON c.column_name = fk.column_name
            LEFT JOIN (
                SELECT DISTINCT kcu.column_name
                FROM pg_indexes i
                JOIN information_schema.key_column_usage kcu ON i.indexname = kcu.constraint_name
                WHERE i.schemaname = ?
                AND i.tablename = ?
            ) idx ON c.column_name = idx.column_name
            WHERE c.table_schema = ?
            AND c.table_name = ?
            ORDER BY c.ordinal_position
        ", [$schema, $table, $schema, $table, $schema, $table, $schema, $table]);

        return collect($columns);
    }

    /**
     * Extract columns from MSSQL
     */
    private function extractColumnsMssql($connection, string $schema, string $table): Collection
    {
        $columns = $connection->select("
            SELECT
                c.COLUMN_NAME as column_name,
                c.DATA_TYPE as data_type,
                c.DATA_TYPE as udt_name,
                c.ORDINAL_POSITION as ordinal_position,
                CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as is_nullable,
                c.COLUMN_DEFAULT as column_default,
                c.CHARACTER_MAXIMUM_LENGTH as character_maximum_length,
                c.NUMERIC_PRECISION as numeric_precision,
                c.NUMERIC_SCALE as numeric_scale,
                NULL as datetime_precision,
                CAST(ep.value AS NVARCHAR(MAX)) as comment,
                CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_primary_key,
                CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_foreign_key,
                CASE WHEN idx.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_indexed
            FROM INFORMATION_SCHEMA.COLUMNS c
            LEFT JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.name
            LEFT JOIN sys.extended_properties ep ON sc.object_id = ep.major_id AND sc.column_id = ep.minor_id AND ep.name = 'MS_Description'
            LEFT JOIN (
                SELECT kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
                WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                AND tc.TABLE_SCHEMA = ?
                AND tc.TABLE_NAME = ?
            ) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
            LEFT JOIN (
                SELECT DISTINCT kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
                WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
                AND tc.TABLE_SCHEMA = ?
                AND tc.TABLE_NAME = ?
            ) fk ON c.COLUMN_NAME = fk.COLUMN_NAME
            LEFT JOIN (
                SELECT DISTINCT ic.name as COLUMN_NAME
                FROM sys.indexes i
                JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE i.object_id = OBJECT_ID(?)
            ) idx ON c.COLUMN_NAME = idx.COLUMN_NAME
            WHERE c.TABLE_SCHEMA = ?
            AND c.TABLE_NAME = ?
            ORDER BY c.ORDINAL_POSITION
        ", [$schema, $table, $schema, $table, $schema . '.' . $table, $schema, $table]);

        return collect($columns);
    }

    /**
     * Extract indexes for a specific table
     */
    public function extractIndexes(SourceDatabase $database, string $schema, string $table): Collection
    {
        $config = $database->getConnectionConfig();
        $connection = $this->connectionService->getConnection($config);

        try {
            if ($database->driver === 'mssql') {
                return $this->extractIndexesMssql($connection, $schema, $table);
            }
            
            return $this->extractIndexesPgsql($connection, $schema, $table);
        } finally {
            $this->connectionService->purgeConnection($connection->getName());
        }
    }

    /**
     * Extract indexes from PostgreSQL
     */
    private function extractIndexesPgsql($connection, string $schema, string $table): Collection
    {
        $indexes = $connection->select("
            SELECT
                i.indexname as index_name,
                i.indexdef as definition,
                CASE WHEN i.indexdef LIKE '%UNIQUE%' THEN true ELSE false END as is_unique,
                CASE WHEN con.contype = 'p' THEN true ELSE false END as is_primary,
                'btree' as index_type,
                ARRAY(
                    SELECT kcu.column_name
                    FROM information_schema.key_column_usage kcu
                    WHERE kcu.constraint_name = i.indexname
                    ORDER BY kcu.ordinal_position
                ) as columns
            FROM pg_indexes i
            LEFT JOIN pg_catalog.pg_constraint con ON con.conname = i.indexname
            WHERE i.schemaname = ?
            AND i.tablename = ?
        ", [$schema, $table]);

        return collect($indexes);
    }

    /**
     * Extract indexes from MSSQL
     */
    private function extractIndexesMssql($connection, string $schema, string $table): Collection
    {
        $indexes = $connection->select("
            SELECT
                i.name as index_name,
                'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 'INDEX ' + i.name + ' ON ' + QUOTENAME(?) + '.' + QUOTENAME(?) + ' (...)' as definition,
                CASE WHEN i.is_unique = 1 THEN 1 ELSE 0 END as is_unique,
                CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END as is_primary,
                i.type_desc as index_type,
                (
                    SELECT STRING_AGG(c.name, ',')
                    FROM sys.index_columns ic
                    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                    ORDER BY ic.key_ordinal
                ) as columns
            FROM sys.indexes i
            WHERE i.object_id = OBJECT_ID(?)
            AND i.type > 0
        ", [$schema, $table, $schema . '.' . $table]);

        return collect($indexes);
    }

    /**
     * Extract foreign keys for a specific table
     */
    public function extractForeignKeys(SourceDatabase $database, string $schema, string $table): Collection
    {
        $config = $database->getConnectionConfig();
        $connection = $this->connectionService->getConnection($config);

        try {
            if ($database->driver === 'mssql') {
                return $this->extractForeignKeysMssql($connection, $schema, $table);
            }
            
            return $this->extractForeignKeysPgsql($connection, $schema, $table);
        } finally {
            $this->connectionService->purgeConnection($connection->getName());
        }
    }

    /**
     * Extract foreign keys from PostgreSQL
     */
    private function extractForeignKeysPgsql($connection, string $schema, string $table): Collection
    {
        $foreignKeys = $connection->select("
            SELECT
                tc.constraint_name,
                kcu.column_name,
                ccu.table_schema as referenced_schema,
                ccu.table_name as referenced_table,
                ccu.column_name as referenced_column,
                rc.update_rule as on_update,
                rc.delete_rule as on_delete
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
            JOIN information_schema.referential_constraints rc ON tc.constraint_name = rc.constraint_name
            WHERE tc.constraint_type = 'FOREIGN KEY'
            AND tc.table_schema = ?
            AND tc.table_name = ?
        ", [$schema, $table]);

        return collect($foreignKeys);
    }

    /**
     * Extract foreign keys from MSSQL
     */
    private function extractForeignKeysMssql($connection, string $schema, string $table): Collection
    {
        $foreignKeys = $connection->select("
            SELECT
                fk.name as constraint_name,
                c.name as column_name,
                OBJECT_SCHEMA_NAME(fk.referenced_object_id) as referenced_schema,
                OBJECT_NAME(fk.referenced_object_id) as referenced_table,
                rc.name as referenced_column,
                CASE WHEN fk.update_referential_action = 1 THEN 'CASCADE' WHEN fk.update_referential_action = 2 THEN 'SET_NULL' WHEN fk.update_referential_action = 3 THEN 'SET_DEFAULT' ELSE 'NO_ACTION' END as on_update,
                CASE WHEN fk.delete_referential_action = 1 THEN 'CASCADE' WHEN fk.delete_referential_action = 2 THEN 'SET_NULL' WHEN fk.delete_referential_action = 3 THEN 'SET_DEFAULT' ELSE 'NO_ACTION' END as on_delete
            FROM sys.foreign_keys fk
            JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
            JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
            JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
            WHERE fk.parent_object_id = OBJECT_ID(?)
        ", [$schema . '.' . $table]);

        return collect($foreignKeys);
    }

    /**
     * Save extracted schema to database
     */
    public function saveSchema(SourceDatabase $database): void
    {
        $tables = $this->extractTables($database);

        foreach ($tables as $tableData) {
            $table = Table::firstOrCreate(
                [
                    'source_database_id' => $database->id,
                    'schema_name' => $tableData->table_schema,
                    'table_name' => $tableData->table_name,
                ],
                ['comment' => $tableData->comment]
            );

            $this->saveColumns($table, $this->extractColumns($database, $tableData->table_schema, $tableData->table_name));
            $this->saveIndexes($table, $this->extractIndexes($database, $tableData->table_schema, $tableData->table_name));
            $this->saveForeignKeys($table, $this->extractForeignKeys($database, $tableData->table_schema, $tableData->table_name));

            $table->update(['last_checked_at' => now()]);
        }

        $database->update(['last_synced_at' => now()]);
    }

    /**
     * Save columns to database
     */
    private function saveColumns(Table $table, Collection $columns): void
    {
        foreach ($columns as $columnData) {
            Column::updateOrCreate(
                [
                    'table_id' => $table->id,
                    'column_name' => $columnData->column_name,
                ],
                (array) $columnData
            );
        }

        // Remove deleted columns
        $columnNames = $columns->pluck('column_name');
        $table->columns()->whereNotIn('column_name', $columnNames)->delete();
    }

    /**
     * Save indexes to database
     */
    private function saveIndexes(Table $table, Collection $indexes): void
    {
        foreach ($indexes as $indexData) {
            Index::updateOrCreate(
                [
                    'table_id' => $table->id,
                    'index_name' => $indexData->index_name,
                ],
                [
                    'columns' => is_array($indexData->columns) ? $indexData->columns : json_decode($indexData->columns, true) ?? [],
                    'is_unique' => $indexData->is_unique,
                    'is_primary' => $indexData->is_primary,
                    'index_type' => $indexData->index_type,
                    'definition' => $indexData->definition,
                ]
            );
        }
    }

    /**
     * Save foreign keys to database
     */
    private function saveForeignKeys(Table $table, Collection $foreignKeys): void
    {
        foreach ($foreignKeys as $fkData) {
            ForeignKey::updateOrCreate(
                [
                    'table_id' => $table->id,
                    'constraint_name' => $fkData->constraint_name,
                    'column_name' => $fkData->column_name,
                ],
                [
                    'referenced_table' => $fkData->referenced_table,
                    'referenced_column' => $fkData->referenced_column,
                    'on_update' => $fkData->on_update,
                    'on_delete' => $fkData->on_delete,
                ]
            );
        }
    }
}