import React, { KeyboardEvent, useEffect, useMemo, useRef, useState } from 'react';
import tw from 'twin.macro';
import GreyRowBox from '@/components/elements/GreyRowBox';
import { Button } from '@/components/elements/button';
import Input from '@/components/elements/Input';
import { FontAwesomeIcon } from '@fortawesome/react-fontawesome';
import { faBroom, faCode, faHistory, faPlay, faTerminal } from '@fortawesome/free-solid-svg-icons';
import { executeSQL, ExecuteSQLResponse } from '../api/tableDataOperations';
import { ServerContext } from '@/state/server';
import { httpErrorToHuman } from '@/api/http';
import useFlash from '@/plugins/useFlash';
import FlashMessageRender from '@/components/FlashMessageRender';

interface RawSqlConsoleProps {
    databaseId: string;
    engine?: string;
    selectedTable?: string | null;
    disabled?: boolean;
    onMutationExecuted?: () => void;
}

interface ConsoleEntry {
    sql: string;
    response: ExecuteSQLResponse;
    executedAt: number;
}

type ConsoleLineKind = 'prompt' | 'continuation' | 'table-border' | 'table-row' | 'success' | 'error' | 'muted';

const FLASH_KEY = 'database:content:raw-sql';
const HISTORY_LIMIT = 50;
const DEFAULT_SQL = 'SELECT 1;';

type SqlEngine = 'mysql' | 'postgresql' | 'mongodb';

const normalizeEngine = (engine?: string): SqlEngine => {
    const normalized = (engine || '').toLowerCase();

    if (normalized === 'postgres' || normalized === 'pgsql' || normalized === 'postgresql') {
        return 'postgresql';
    }

    if (normalized === 'mongodb' || normalized === 'mongo') {
        return 'mongodb';
    }

    return 'mysql';
};

const quoteIdentifier = (engine: SqlEngine, identifier: string): string => {
    const fallback = identifier || 'table_name';
    const quote = engine === 'postgresql' ? '"' : '`';

    return fallback
        .split('.')
        .map((part) => `${quote}${part.replace(new RegExp(quote, 'g'), quote + quote)}${quote}`)
        .join('.');
};

const mysqlMacros = [
    { key: '1', label: 'SELECT *', template: (table?: string | null) => `SELECT * FROM ${quoteIdentifier('mysql', table || 'table_name')} LIMIT 100;` },
    { key: '2', label: 'COUNT', template: (table?: string | null) => `SELECT COUNT(*) AS total FROM ${quoteIdentifier('mysql', table || 'table_name')};` },
    { key: '3', label: 'DESCRIBE', template: (table?: string | null) => `DESCRIBE ${quoteIdentifier('mysql', table || 'table_name')};` },
    { key: '4', label: 'SHOW CREATE', template: (table?: string | null) => `SHOW CREATE TABLE ${quoteIdentifier('mysql', table || 'table_name')};` },
    { key: '5', label: 'SHOW TABLES', template: () => 'SHOW TABLES;' },
] as const;

const postgresMacros = [
    { key: '1', label: 'SELECT *', template: (table?: string | null) => `SELECT * FROM ${quoteIdentifier('postgresql', table || 'table_name')} LIMIT 100;` },
    { key: '2', label: 'COUNT', template: (table?: string | null) => `SELECT COUNT(*) AS total FROM ${quoteIdentifier('postgresql', table || 'table_name')};` },
    {
        key: '3',
        label: 'COLUMNS',
        template: (table?: string | null) =>
            `SELECT column_name, data_type, is_nullable, column_default\nFROM information_schema.columns\nWHERE table_schema = 'public' AND table_name = '${(table || 'table_name').replace(/'/g, "''")}'\nORDER BY ordinal_position;`,
    },
    {
        key: '4',
        label: 'INDEXES',
        template: (table?: string | null) =>
            `SELECT indexname, indexdef\nFROM pg_indexes\nWHERE schemaname = 'public' AND tablename = '${(table || 'table_name').replace(/'/g, "''")}';`,
    },
    {
        key: '5',
        label: 'TABLES',
        template: () =>
            "SELECT table_name\nFROM information_schema.tables\nWHERE table_schema = 'public' AND table_type = 'BASE TABLE'\nORDER BY table_name;",
    },
] as const;

const SQL_KEYWORDS = [
    'SELECT',
    'FROM',
    'WHERE',
    'ORDER BY',
    'GROUP BY',
    'LIMIT',
    'INSERT INTO',
    'VALUES',
    'UPDATE',
    'SET',
    'DELETE FROM',
    'JOIN',
    'LEFT JOIN',
    'RIGHT JOIN',
    'INNER JOIN',
    'CREATE TABLE',
    'ALTER TABLE',
    'DROP TABLE',
    'SHOW TABLES',
    'SHOW COLUMNS',
    'DESCRIBE',
    'EXPLAIN',
] as const;

const POSTGRES_KEYWORDS = [
    'SELECT',
    'FROM',
    'WHERE',
    'ORDER BY',
    'GROUP BY',
    'LIMIT',
    'OFFSET',
    'INSERT INTO',
    'VALUES',
    'UPDATE',
    'SET',
    'DELETE FROM',
    'JOIN',
    'LEFT JOIN',
    'RIGHT JOIN',
    'INNER JOIN',
    'CREATE TABLE',
    'ALTER TABLE',
    'DROP TABLE',
    'RETURNING',
    'WITH',
    'EXPLAIN',
    'ANALYZE',
    'VACUUM',
    'CREATE INDEX',
    'DROP INDEX',
    'SERIAL',
    'BIGSERIAL',
    'JSONB',
    'ILIKE',
] as const;

const isReadOnlyQuery = (sql: string): boolean => {
    const normalized = sql.trim().toUpperCase();
    return (
        normalized.startsWith('SELECT') ||
        normalized.startsWith('SHOW') ||
        normalized.startsWith('DESCRIBE') ||
        normalized.startsWith('EXPLAIN') ||
        normalized.startsWith('WITH')
    );
};

const getHistoryKey = (databaseId: string) => `pterodactyl:db:sql-history:${databaseId}`;

const pushHistory = (current: string[], sql: string): string[] => {
    const trimmed = sql.trim();
    if (!trimmed) {
        return current;
    }

    const deduped = [trimmed, ...current.filter((item) => item !== trimmed)];
    return deduped.slice(0, HISTORY_LIMIT);
};

const renderValue = (value: unknown): string => {
    if (value === null || value === undefined) {
        return 'NULL';
    }

    if (typeof value === 'object') {
        try {
            return JSON.stringify(value);
        } catch {
            return '[object]';
        }
    }

    return String(value);
};

const formatDuration = (milliseconds?: number): string => ((milliseconds || 0) / 1000).toFixed(3);

const padRight = (value: string, width: number): string => {
    if (value.length >= width) {
        return value;
    }

    return `${value}${' '.repeat(width - value.length)}`;
};

const makeAsciiTable = (columns: string[], rows: Record<string, unknown>[]): string => {
    if (!columns.length) {
        return '';
    }

    const widths = columns.map((column) => column.length);

    rows.forEach((row) => {
        columns.forEach((column, index) => {
            widths[index] = Math.max(widths[index], renderValue(row[column]).length);
        });
    });

    const border = `+${widths.map((width) => '-'.repeat(width + 2)).join('+')}+`;
    const header = `| ${columns.map((column, index) => padRight(column, widths[index])).join(' | ')} |`;
    const body = rows.map((row) => {
        const values = columns.map((column, index) => padRight(renderValue(row[column]), widths[index]));
        return `| ${values.join(' | ')} |`;
    });

    return [border, header, border, ...body, border].join('\n');
};

const getResultColumns = (response: ExecuteSQLResponse, rows: Record<string, unknown>[]): string[] => {
    if (response.columns?.length) {
        return response.columns;
    }

    return rows.reduce<string[]>((columns, row) => {
        Object.keys(row).forEach((key) => {
            if (!columns.includes(key)) {
                columns.push(key);
            }
        });

        return columns;
    }, []);
};

const promptName = (engine: SqlEngine): string => (engine === 'postgresql' ? 'PostgreSQL' : engine === 'mongodb' ? 'MongoDB' : 'MariaDB');

const formatPrompt = (engine: SqlEngine, databaseId: string, sql: string): string => {
    const lines = sql.split('\n');
    const [firstLine, ...remainingLines] = lines;

    return [`${promptName(engine)} [${databaseId}]> ${firstLine}`, ...remainingLines.map((line) => `    -> ${line}`)].join('\n');
};

const formatConsoleEntry = (engine: SqlEngine, databaseId: string, entry: ConsoleEntry): string => {
    const response = entry.response;
    const rows = (response.results || []) as Record<string, unknown>[];
    const columns = getResultColumns(response, rows);
    const tableText = rows.length && columns.length ? makeAsciiTable(columns, rows) : '';
    const timing = formatDuration(response.execution_time_ms);
    const structuredOutputAvailable =
        !!tableText || typeof response.affected_rows === 'number' || typeof response.row_count === 'number';
    const terminalOutput = structuredOutputAvailable ? '' : response.terminal_output?.trim() || '';

    if (!response.success) {
        return [formatPrompt(engine, databaseId, entry.sql), terminalOutput, `ERROR: ${response.error || 'Query failed.'}`]
            .filter(Boolean)
            .join('\n');
    }

    const rowCount = response.row_count ?? rows.length;
    const affectedRows = response.affected_rows ?? rowCount;
    const summary = tableText
        ? `${rowCount} ${rowCount === 1 ? 'row' : 'rows'} in set (${timing} sec)`
        : `Query OK, ${affectedRows} ${affectedRows === 1 ? 'row' : 'rows'} affected (${timing} sec)`;

    return [
        formatPrompt(engine, databaseId, entry.sql),
        terminalOutput,
        tableText,
        response.last_insert_id ? `Last insert id: ${response.last_insert_id}` : '',
        summary,
    ]
        .filter(Boolean)
        .join('\n');
};

const getConsoleLineKind = (line: string): ConsoleLineKind => {
    if (/^(MariaDB|PostgreSQL|MongoDB) \[/.test(line)) {
        return 'prompt';
    }

    if (line.startsWith('    ->')) {
        return 'continuation';
    }

    if (line.startsWith('ERROR')) {
        return 'error';
    }

    if (/^(Query OK|Last insert id:|\d+ rows? in set)/.test(line)) {
        return 'success';
    }

    if (/^\+-[-+]+\+$/.test(line)) {
        return 'table-border';
    }

    if (line.startsWith('|')) {
        return 'table-row';
    }

    return 'muted';
};

const getConsoleLineStyle = (line: string) => {
    switch (getConsoleLineKind(line)) {
        case 'prompt':
            return tw`text-primary-300`;
        case 'continuation':
            return tw`text-neutral-400`;
        case 'error':
            return tw`text-red-300`;
        case 'success':
            return tw`text-green-300`;
        case 'table-border':
            return tw`text-neutral-500`;
        case 'table-row':
            return tw`text-neutral-100`;
        default:
            return tw`text-neutral-300`;
    }
};

const RawSqlConsole = ({ databaseId, engine: rawEngine, selectedTable, disabled, onMutationExecuted }: RawSqlConsoleProps) => {
    const uuid = ServerContext.useStoreState((state) => state.server.data!.uuid);
    const { clearFlashes, addError } = useFlash();
    const textareaRef = useRef<HTMLTextAreaElement>(null);
    const outputRef = useRef<HTMLDivElement>(null);
    const localHistoryKey = useMemo(() => getHistoryKey(databaseId), [databaseId]);
    const engine = useMemo(() => normalizeEngine(rawEngine), [rawEngine]);
    const macros = engine === 'postgresql' ? postgresMacros : mysqlMacros;
    const keywords = useMemo<readonly string[]>(
        () => (engine === 'postgresql' ? POSTGRES_KEYWORDS : SQL_KEYWORDS),
        [engine]
    );

    const [sql, setSql] = useState(DEFAULT_SQL);
    const [confirmRisky, setConfirmRisky] = useState(false);
    const [submitting, setSubmitting] = useState(false);
    const [entries, setEntries] = useState<ConsoleEntry[]>([]);
    const [history, setHistory] = useState<string[]>([]);
    const [historyIndex, setHistoryIndex] = useState<number | null>(null);
    const [cursorIndex, setCursorIndex] = useState(0);

    useEffect(() => {
        clearFlashes(FLASH_KEY);
        setHistoryIndex(null);
        setConfirmRisky(false);
        setSql(DEFAULT_SQL);
        setEntries([]);
        try {
            const raw = window.localStorage.getItem(localHistoryKey);
            if (!raw) {
                setHistory([]);
                return;
            }

            const parsed = JSON.parse(raw);
            setHistory(
                Array.isArray(parsed) ? parsed.filter((item) => typeof item === 'string').slice(0, HISTORY_LIMIT) : []
            );
        } catch {
            setHistory([]);
        }
    }, [localHistoryKey]);

    const commitHistory = (nextHistory: string[]) => {
        setHistory(nextHistory);
        window.localStorage.setItem(localHistoryKey, JSON.stringify(nextHistory));
    };

    const submit = async () => {
        clearFlashes(FLASH_KEY);
        const trimmedSql = sql.trim();

        if (!trimmedSql) {
            addError({ key: FLASH_KEY, message: 'SQL query is required.' });
            return;
        }

        setSubmitting(true);
        setHistoryIndex(null);

        try {
            const queryIsReadOnly = isReadOnlyQuery(trimmedSql);
            const result = await executeSQL(uuid, databaseId, {
                sql: trimmedSql,
                is_select: queryIsReadOnly,
                confirm_risky: confirmRisky,
            });

            setEntries((current) => [
                ...current,
                {
                    sql: trimmedSql,
                    response: result,
                    executedAt: Date.now(),
                },
            ]);
            commitHistory(pushHistory(history, trimmedSql));

            if (!queryIsReadOnly && result.success) {
                onMutationExecuted && onMutationExecuted();
            }
        } catch (error) {
            addError({ key: FLASH_KEY, message: httpErrorToHuman(error) });
        } finally {
            setSubmitting(false);
        }
    };

    const applyMacro = (index: number) => {
        const macro = macros[index];
        if (!macro) {
            return;
        }

        setSql(macro.template(selectedTable));
        setHistoryIndex(null);
        textareaRef.current?.focus();
    };

    const clearOutput = () => {
        setEntries([]);
        clearFlashes(FLASH_KEY);
    };

    const applyHistoryStep = (step: number) => {
        if (!history.length) {
            return;
        }

        const nextIndex =
            historyIndex === null
                ? step > 0
                    ? 0
                    : history.length - 1
                : Math.min(Math.max(historyIndex + step, 0), history.length - 1);
        setHistoryIndex(nextIndex);
        setSql(history[nextIndex]);
    };

    const currentToken = useMemo(() => {
        const before = sql.slice(0, cursorIndex);
        const match = before.match(/([a-zA-Z_]+)$/);
        return match ? match[1] : '';
    }, [sql, cursorIndex]);

    const suggestions = useMemo(() => {
        const upperToken = currentToken.toUpperCase();
        if (!upperToken) {
            return [];
        }

        return keywords.filter((keyword) => keyword.startsWith(upperToken)).slice(0, 6);
    }, [currentToken, keywords]);

    const applySuggestion = (suggestion: string) => {
        const before = sql.slice(0, cursorIndex);
        const tokenMatch = before.match(/([a-zA-Z_]+)$/);
        const token = tokenMatch ? tokenMatch[1] : '';
        if (!token) {
            return;
        }

        const tokenStart = cursorIndex - token.length;
        const prefix = sql.slice(0, tokenStart);
        const suffix = sql.slice(cursorIndex);
        const nextSql = `${prefix}${suggestion}${suffix}`;
        const nextCursor = tokenStart + suggestion.length;

        setSql(nextSql);
        setCursorIndex(nextCursor);
        requestAnimationFrame(() => {
            if (textareaRef.current) {
                textareaRef.current.selectionStart = nextCursor;
                textareaRef.current.selectionEnd = nextCursor;
                textareaRef.current.focus();
            }
        });
    };

    const onEditorKeyDown = (event: KeyboardEvent<HTMLTextAreaElement>) => {
        const target = event.currentTarget;
        const hasModifier = event.metaKey || event.ctrlKey;

        if (event.key === 'Enter' && !event.shiftKey) {
            event.preventDefault();
            if (!submitting && !disabled) {
                submit();
            }
            return;
        }

        if (hasModifier && event.key.toLowerCase() === 'l') {
            event.preventDefault();
            clearOutput();
            return;
        }

        if (event.altKey) {
            const macroIndex = Number(event.key);
            if (Number.isInteger(macroIndex) && macroIndex >= 1 && macroIndex <= macros.length) {
                event.preventDefault();
                applyMacro(macroIndex - 1);
                return;
            }
        }

        if (event.key === 'Tab') {
            if (suggestions.length) {
                event.preventDefault();
                applySuggestion(suggestions[0]);
                return;
            }

            event.preventDefault();
            const start = target.selectionStart;
            const end = target.selectionEnd;
            const nextValue = `${sql.slice(0, start)}    ${sql.slice(end)}`;
            setSql(nextValue);
            requestAnimationFrame(() => {
                if (textareaRef.current) {
                    textareaRef.current.selectionStart = start + 4;
                    textareaRef.current.selectionEnd = start + 4;
                }
            });
            return;
        }

        if (event.key === 'ArrowUp' && target.selectionStart === 0 && target.selectionEnd === 0) {
            event.preventDefault();
            applyHistoryStep(1);
            return;
        }

        if (
            event.key === 'ArrowDown' &&
            target.selectionStart === target.value.length &&
            target.selectionEnd === target.value.length
        ) {
            event.preventDefault();
            applyHistoryStep(-1);
        }
    };

    useEffect(() => {
        if (!outputRef.current) {
            return;
        }
        outputRef.current.scrollTop = outputRef.current.scrollHeight;
    }, [entries, submitting]);

    const outputText = useMemo(() => {
        if (!entries.length) {
            return `${promptName(engine)} [${databaseId}]>`;
        }

        return entries.map((entry) => formatConsoleEntry(engine, databaseId, entry)).join('\n\n');
    }, [entries, engine, databaseId]);

    const outputLines = useMemo(() => outputText.split('\n'), [outputText]);

    return (
        <GreyRowBox $hoverable={false} css={tw`block p-4`}>
            <div css={tw`flex items-center justify-between mb-2`}>
                <p css={tw`text-sm text-neutral-100 flex items-center`}>
                    <FontAwesomeIcon icon={faTerminal} fixedWidth css={tw`text-neutral-400`} />
                    <span css={tw`ml-2`}>SQL console</span>
                </p>
            </div>

            <FlashMessageRender byKey={FLASH_KEY} css={tw`mb-2`} />

            <div css={tw`flex flex-wrap items-center gap-2 mb-2`}>
                {macros.map((macro, index) => (
                    <Button
                        key={macro.label}
                        type={'button'}
                        size={Button.Sizes.Small}
                        variant={Button.Variants.Secondary}
                        disabled={!!disabled || submitting}
                        onClick={() => applyMacro(index)}
                    >
                        <FontAwesomeIcon icon={faCode} fixedWidth />
                        <span css={tw`ml-2`}>
                            {macro.label} ({macro.key})
                        </span>
                    </Button>
                ))}
            </div>

            <div css={tw`border border-neutral-600 rounded bg-neutral-900`}>
                <div ref={outputRef} css={tw`h-[18rem] overflow-auto p-3`}>
                    <pre css={tw`text-xs text-neutral-100 whitespace-pre font-mono leading-relaxed min-w-max`}>
                        {outputLines.map((line, index) => (
                            <span key={`${index}-${line}`} css={getConsoleLineStyle(line)}>
                                {line || ' '}
                                {'\n'}
                            </span>
                        ))}
                    </pre>
                </div>

                <div css={tw`border-t border-neutral-600 p-3`}>
                    <textarea
                        ref={textareaRef}
                        value={sql}
                        onChange={(event) => setSql(event.currentTarget.value)}
                        onSelect={(event) => setCursorIndex(event.currentTarget.selectionStart)}
                        onKeyDown={onEditorKeyDown}
                        rows={2}
                        spellCheck={false}
                        disabled={!!disabled || submitting}
                        className={
                            'w-full text-sm text-neutral-100 bg-neutral-700 border border-neutral-600 rounded px-3 py-2 focus:outline-none focus:border-primary-500 resize-y min-h-[70px] font-mono'
                        }
                        placeholder={
                            engine === 'postgresql'
                                ? 'SELECT * FROM "table_name" LIMIT 100;'
                                : 'SELECT * FROM `table_name` LIMIT 100;'
                        }
                    />

                    {!!suggestions.length && (
                        <div css={tw`mt-2 flex flex-wrap gap-2`}>
                            {suggestions.map((suggestion) => (
                                <button
                                    key={suggestion}
                                    type={'button'}
                                    css={tw`text-xs px-2 py-1 rounded border border-neutral-600 text-neutral-200 bg-neutral-700 hover:border-primary-500`}
                                    onClick={() => applySuggestion(suggestion)}
                                >
                                    {suggestion}
                                </button>
                            ))}
                        </div>
                    )}

                    <div css={tw`mt-2 flex flex-wrap items-center justify-between gap-2`}>
                        <div css={tw`flex items-center gap-2`}>
                            <Button
                                type={'button'}
                                size={Button.Sizes.Small}
                                disabled={!!disabled || submitting}
                                onClick={submit}
                            >
                                <FontAwesomeIcon icon={faPlay} fixedWidth />
                                <span css={tw`ml-2`}>Execute</span>
                            </Button>
                            <Button
                                type={'button'}
                                size={Button.Sizes.Small}
                                variant={Button.Variants.Secondary}
                                disabled={submitting}
                                onClick={clearOutput}
                            >
                                <FontAwesomeIcon icon={faBroom} fixedWidth />
                                <span css={tw`ml-2`}>Clear</span>
                            </Button>
                        </div>
                        <label css={tw`flex items-center gap-2 text-xs text-neutral-300`}>
                            <Input
                                type={'checkbox'}
                                checked={confirmRisky}
                                onChange={(event) => setConfirmRisky(event.currentTarget.checked)}
                                disabled={!!disabled || submitting}
                            />
                            Confirm risky statements
                        </label>
                    </div>
                    <div css={tw`mt-2 text-xs text-neutral-400 flex items-center`}>
                        <FontAwesomeIcon icon={faHistory} fixedWidth />
                        <span css={tw`ml-2`}>History: {history.length} command(s)</span>
                    </div>
                </div>
            </div>
        </GreyRowBox>
    );
};

export default RawSqlConsole;