import React, { useEffect, useMemo, useState } from 'react';
import tw from 'twin.macro';
import { Dialog } from '@/components/elements/dialog';
import { Button } from '@/components/elements/button';
import Input from '@/components/elements/Input';
import Label from '@/components/elements/Label';
import FlashMessageRender from '@/components/FlashMessageRender';
import useFlash from '@/plugins/useFlash';
import { ServerContext } from '@/state/server';
import { httpErrorToHuman } from '@/api/http';
import { createTable, OperationResponse, TableColumn } from '../api/tableDataOperations';

type ColumnKey = 'NONE' | 'PRIMARY';

interface ColumnForm {
    id: number;
    name: string;
    type: string;
    length: string;
    precision: string;
    scale: string;
    nullable: boolean;
    defaultValue: string;
    key: ColumnKey;
    autoIncrement: boolean;
    enumValues: string;
    setValues: string;
}

interface CreateTableDialogContext {
    databaseId: string;
    engine?: string;
    existingTables?: string[];
    initialTableName?: string;
}

interface CreateTableDialogProps {
    open: boolean;
    onClose: () => void;
    context: CreateTableDialogContext;
    onSuccess?: (response: OperationResponse) => void;
}

const FLASH_KEY = 'database:content:create-table-dialog';
const MYSQL_INTEGER_TYPES = ['INT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT'];
const POSTGRES_INTEGER_TYPES = ['SMALLINT', 'INT', 'INTEGER', 'BIGINT'];
const LENGTH_TYPES = ['VARCHAR', 'CHAR'];
const PRECISION_TYPES = ['DECIMAL', 'FLOAT', 'DOUBLE'];
const MYSQL_TYPES = [
    'INT',
    'TINYINT',
    'SMALLINT',
    'MEDIUMINT',
    'BIGINT',
    'VARCHAR',
    'CHAR',
    'TEXT',
    'MEDIUMTEXT',
    'LONGTEXT',
    'BLOB',
    'MEDIUMBLOB',
    'LONGBLOB',
    'FLOAT',
    'DOUBLE',
    'DECIMAL',
    'DATE',
    'DATETIME',
    'TIMESTAMP',
    'TIME',
    'YEAR',
    'BOOLEAN',
    'JSON',
    'ENUM',
    'SET',
] as const;
const POSTGRES_TYPES = [
    'SMALLINT',
    'INT',
    'INTEGER',
    'BIGINT',
    'VARCHAR',
    'CHAR',
    'TEXT',
    'REAL',
    'DOUBLE',
    'DECIMAL',
    'NUMERIC',
    'DATE',
    'TIMESTAMP',
    'TIME',
    'BOOLEAN',
    'JSON',
    'JSONB',
    'BYTEA',
] as const;

const createDefaultColumn = (id: number): ColumnForm => ({
    id,
    name: id === 1 ? 'id' : '',
    type: id === 1 ? 'INT' : 'VARCHAR',
    length: id === 1 ? '11' : '255',
    precision: '',
    scale: '',
    nullable: id !== 1,
    defaultValue: '',
    key: id === 1 ? 'PRIMARY' : 'NONE',
    autoIncrement: id === 1,
    enumValues: '',
    setValues: '',
});

const parsePositiveInt = (value: string): number | undefined => {
    const parsed = Number(value);
    return Number.isInteger(parsed) && parsed > 0 ? parsed : undefined;
};

const parseNonNegativeInt = (value: string): number | undefined => {
    const parsed = Number(value);
    return Number.isInteger(parsed) && parsed >= 0 ? parsed : undefined;
};

const parseCsvValues = (value: string): string[] =>
    value
        .split(',')
        .map((item) => item.trim())
        .filter(Boolean);

const isValidIdentifier = (value: string): boolean => /^[a-zA-Z_][a-zA-Z0-9_]*$/.test(value);

const CreateTableDialog = ({ open, onClose, context, onSuccess }: CreateTableDialogProps) => {
    const uuid = ServerContext.useStoreState((state) => state.server.data!.uuid);
    const { clearFlashes, addError } = useFlash();
    const normalizedEngine = (context.engine || 'mysql').toLowerCase();
    const isPostgres = normalizedEngine === 'postgresql' || normalizedEngine === 'postgres';
    const supportedTypes = isPostgres ? POSTGRES_TYPES : MYSQL_TYPES;
    const integerTypes = isPostgres ? POSTGRES_INTEGER_TYPES : MYSQL_INTEGER_TYPES;
    const precisionTypes = isPostgres ? ['DECIMAL', 'NUMERIC'] : PRECISION_TYPES;

    const [isSubmitting, setIsSubmitting] = useState(false);
    const [tableName, setTableName] = useState('');
    const [columns, setColumns] = useState<ColumnForm[]>([createDefaultColumn(1)]);
    const [nextColumnId, setNextColumnId] = useState(2);

    useEffect(() => {
        if (!open) {
            clearFlashes(FLASH_KEY);
            setIsSubmitting(false);
            setTableName('');
            setColumns([createDefaultColumn(1)]);
            setNextColumnId(2);
            return;
        }

        clearFlashes(FLASH_KEY);
        setTableName(context.initialTableName?.trim() || '');
        setColumns([createDefaultColumn(1)]);
        setNextColumnId(2);
    }, [open]);

    const normalizedExistingTableNames = useMemo(
        () => new Set((context.existingTables || []).map((name) => name.toLowerCase())),
        [context.existingTables]
    );

    const setColumnsState = (updater: (current: ColumnForm[]) => ColumnForm[]) => {
        setColumns((current) => updater(current));
    };

    const updateColumn = (id: number, updater: (column: ColumnForm) => ColumnForm) => {
        setColumnsState((current) => current.map((column) => (column.id === id ? updater(column) : column)));
    };

    const addColumnRow = () => {
        setColumnsState((current) => [...current, createDefaultColumn(nextColumnId)]);
        setNextColumnId((current) => current + 1);
    };

    const removeColumnRow = (id: number) => {
        setColumnsState((current) => (current.length > 1 ? current.filter((column) => column.id !== id) : current));
    };

    const validate = (): string | null => {
        if (!context.databaseId.trim()) {
            return 'Database context is missing. Close and reopen the dialog.';
        }

        const trimmedTableName = tableName.trim();
        if (!trimmedTableName) {
            return 'Table name is required.';
        }

        if (!isValidIdentifier(trimmedTableName)) {
            return 'Table name must start with a letter/underscore and contain only letters, numbers, and underscores.';
        }

        if (normalizedExistingTableNames.has(trimmedTableName.toLowerCase())) {
            return `Table "${trimmedTableName}" already exists.`;
        }

        const seenColumns = new Set<string>();

        if (!Array.isArray(columns) || columns.length < 1) {
            return 'At least one column is required.';
        }

        let autoIncrementCount = 0;

        for (let index = 0; index < columns.length; index++) {
            const column = columns[index];
            const columnName = column.name.trim();
            const columnType = column.type.trim().toUpperCase();

            if (!columnName) {
                return `Column ${index + 1} name is required.`;
            }

            if (!isValidIdentifier(columnName)) {
                return `Column "${columnName}" has an invalid name format.`;
            }

            const normalizedColumnName = columnName.toLowerCase();
            if (seenColumns.has(normalizedColumnName)) {
                return `Column "${columnName}" is duplicated.`;
            }
            seenColumns.add(normalizedColumnName);

            if (!(supportedTypes as readonly string[]).includes(columnType)) {
                return `Column "${columnName}" has an unsupported data type.`;
            }

            if (column.autoIncrement) {
                autoIncrementCount += 1;

                if (!integerTypes.includes(columnType)) {
                    return `${isPostgres ? 'Generated identity' : 'AUTO_INCREMENT'} can only be used with integer types. Column "${columnName}" is ${columnType}.`;
                }

                if (column.key !== 'PRIMARY') {
                    return `${isPostgres ? 'Generated identity' : 'AUTO_INCREMENT'} column "${columnName}" must use PRIMARY key.`;
                }

                if (column.defaultValue.trim()) {
                    return `${isPostgres ? 'Generated identity' : 'AUTO_INCREMENT'} column "${columnName}" cannot have a default value.`;
                }
            }

            if (column.key === 'PRIMARY' && column.nullable) {
                return `Primary key column "${columnName}" cannot be nullable.`;
            }

            if (LENGTH_TYPES.includes(columnType)) {
                if (!parsePositiveInt(column.length)) {
                    return `Column "${columnName}" requires a valid length greater than 0.`;
                }
            }

            if (precisionTypes.includes(columnType)) {
                if (column.precision && !parsePositiveInt(column.precision)) {
                    return `Column "${columnName}" precision must be a positive integer.`;
                }

                if (column.scale && parseNonNegativeInt(column.scale) === undefined) {
                    return `Column "${columnName}" scale must be a non-negative integer.`;
                }

                const precision = parsePositiveInt(column.precision) || parsePositiveInt(column.length);
                const scale = parseNonNegativeInt(column.scale);

                if (scale !== undefined && precision !== undefined && scale > precision) {
                    return `Column "${columnName}" scale cannot be larger than precision.`;
                }
            }

            if (!isPostgres && columnType === 'ENUM' && parseCsvValues(column.enumValues).length < 1) {
                return `Column "${columnName}" must define at least one ENUM value.`;
            }

            if (!isPostgres && columnType === 'SET' && parseCsvValues(column.setValues).length < 1) {
                return `Column "${columnName}" must define at least one SET value.`;
            }
        }

        if (autoIncrementCount > 1) {
            return 'Only one AUTO_INCREMENT column is allowed.';
        }

        return null;
    };

    const buildColumnPayload = (column: ColumnForm): TableColumn => {
        const type = column.type.trim().toUpperCase();
        const length = parsePositiveInt(column.length);
        const precision = parsePositiveInt(column.precision);
        const scale = parseNonNegativeInt(column.scale);

        const payload: TableColumn = {
            name: column.name.trim(),
            type,
            nullable: column.nullable,
            auto_increment: column.autoIncrement,
            primary_key: column.key === 'PRIMARY',
        };

        if (LENGTH_TYPES.includes(type) && length) {
            payload.length = length;
        }

        if (precisionTypes.includes(type)) {
            const effectiveLength = precision || length;
            if (effectiveLength) {
                payload.length = effectiveLength;
            }

            if (scale !== undefined) {
                payload.precision = scale;
                payload.scale = scale;
            }
        }

        if (!isPostgres && type === 'ENUM') {
            payload.enum_values = parseCsvValues(column.enumValues);
        }

        if (!isPostgres && type === 'SET') {
            payload.set_values = parseCsvValues(column.setValues);
        }

        const defaultValue = column.defaultValue.trim();
        if (!column.autoIncrement && defaultValue) {
            payload.default = defaultValue;
        }

        return payload;
    };

    const submit = async () => {
        clearFlashes(FLASH_KEY);

        const validationError = validate();
        if (validationError) {
            addError({ key: FLASH_KEY, message: validationError });
            return;
        }

        setIsSubmitting(true);

        try {
            const response = await createTable(uuid, context.databaseId, {
                table_name: tableName.trim(),
                columns: columns.map(buildColumnPayload),
            });

            if (!response.success) {
                throw new Error(response.error || 'Failed to create table.');
            }

            onSuccess?.(response);
            onClose();
        } catch (error) {
            addError({ key: FLASH_KEY, message: httpErrorToHuman(error) });
        }

        setIsSubmitting(false);
    };

    return (
        <Dialog
            open={open}
            onClose={onClose}
            title={'Create table'}
            description={'Create a new table with one or more columns.'}
            preventExternalClose={isSubmitting}
        >
            <div css={tw`mt-6 space-y-4 max-h-[65vh] overflow-y-auto pr-1`}>
                <FlashMessageRender byKey={FLASH_KEY} css={tw`mb-2`} />

                <div>
                    <Label htmlFor={'create-table-name'}>Table name</Label>
                    <Input
                        id={'create-table-name'}
                        value={tableName}
                        onChange={(event) => setTableName(event.currentTarget.value)}
                        disabled={isSubmitting}
                    />
                </div>

                <div css={tw`space-y-3`}>
                    <div css={tw`flex items-center justify-between`}>
                        <Label css={tw`mb-0`}>Columns</Label>
                        <Button
                            type={'button'}
                            size={Button.Sizes.Small}
                            onClick={addColumnRow}
                            disabled={isSubmitting}
                        >
                            Add Column
                        </Button>
                    </div>

                    {columns.map((column, index) => {
                        const columnType = column.type.toUpperCase();
                        const showLength = LENGTH_TYPES.includes(columnType);
                        const showPrecision = precisionTypes.includes(columnType);
                        const showEnum = !isPostgres && columnType === 'ENUM';
                        const showSet = !isPostgres && columnType === 'SET';

                        return (
                            <div
                                key={column.id}
                                css={tw`p-3 border border-neutral-600 rounded bg-neutral-700 space-y-3`}
                            >
                                <div css={tw`flex items-center justify-between`}>
                                    <p css={tw`text-xs text-neutral-300 uppercase`}>Column {index + 1}</p>
                                    <Button
                                        type={'button'}
                                        size={Button.Sizes.Small}
                                        variant={Button.Variants.Secondary}
                                        onClick={() => removeColumnRow(column.id)}
                                        disabled={isSubmitting || columns.length < 2}
                                    >
                                        Remove
                                    </Button>
                                </div>

                                <div css={tw`grid grid-cols-1 md:grid-cols-3 gap-3`}>
                                    <div>
                                        <Label>Name</Label>
                                        <Input
                                            value={column.name}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => ({
                                                    ...current,
                                                    name: event.currentTarget.value,
                                                }))
                                            }
                                            disabled={isSubmitting}
                                        />
                                    </div>
                                    <div>
                                        <Label>Data type</Label>
                                        <select
                                            value={column.type}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => {
                                                    const type = event.currentTarget.value;
                                                    const nextColumn = { ...current, type };

                                                    if (!precisionTypes.includes(type)) {
                                                        nextColumn.precision = '';
                                                        nextColumn.scale = '';
                                                    }

                                                    if (!LENGTH_TYPES.includes(type)) {
                                                        nextColumn.length = '';
                                                    }

                                                    if (type !== 'ENUM') {
                                                        nextColumn.enumValues = '';
                                                    }

                                                    if (type !== 'SET') {
                                                        nextColumn.setValues = '';
                                                    }

                                                    if (!integerTypes.includes(type)) {
                                                        nextColumn.autoIncrement = false;
                                                    }

                                                    return nextColumn;
                                                })
                                            }
                                            disabled={isSubmitting}
                                            css={tw`w-full p-3 rounded border border-neutral-500 bg-neutral-600 text-neutral-200 text-sm`}
                                        >
                                            {supportedTypes.map((type) => (
                                                <option key={type} value={type}>
                                                    {type}
                                                </option>
                                            ))}
                                        </select>
                                    </div>
                                    <div>
                                        <Label>Key</Label>
                                        <select
                                            value={column.key}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => {
                                                    const key = event.currentTarget.value as ColumnKey;
                                                    return {
                                                        ...current,
                                                        key,
                                                        nullable: key === 'PRIMARY' ? false : current.nullable,
                                                    };
                                                })
                                            }
                                            disabled={isSubmitting}
                                            css={tw`w-full p-3 rounded border border-neutral-500 bg-neutral-600 text-neutral-200 text-sm`}
                                        >
                                            <option value={'NONE'}>None</option>
                                            <option value={'PRIMARY'}>Primary</option>
                                        </select>
                                    </div>
                                </div>

                                <div css={tw`grid grid-cols-1 md:grid-cols-3 gap-3`}>
                                    {showLength && (
                                        <div>
                                            <Label>Length</Label>
                                            <Input
                                                type={'number'}
                                                min={1}
                                                value={column.length}
                                                onChange={(event) =>
                                                    updateColumn(column.id, (current) => ({
                                                        ...current,
                                                        length: event.currentTarget.value,
                                                    }))
                                                }
                                                disabled={isSubmitting}
                                            />
                                        </div>
                                    )}

                                    {showPrecision && (
                                        <div>
                                            <Label>Precision</Label>
                                            <Input
                                                type={'number'}
                                                min={1}
                                                value={column.precision}
                                                onChange={(event) =>
                                                    updateColumn(column.id, (current) => ({
                                                        ...current,
                                                        precision: event.currentTarget.value,
                                                    }))
                                                }
                                                disabled={isSubmitting}
                                            />
                                        </div>
                                    )}

                                    {showPrecision && (
                                        <div>
                                            <Label>Scale</Label>
                                            <Input
                                                type={'number'}
                                                min={0}
                                                value={column.scale}
                                                onChange={(event) =>
                                                    updateColumn(column.id, (current) => ({
                                                        ...current,
                                                        scale: event.currentTarget.value,
                                                    }))
                                                }
                                                disabled={isSubmitting}
                                            />
                                        </div>
                                    )}
                                </div>

                                {(showEnum || showSet) && (
                                    <div>
                                        <Label>
                                            {showEnum
                                                ? 'ENUM values (comma separated)'
                                                : 'SET values (comma separated)'}
                                        </Label>
                                        <Input
                                            value={showEnum ? column.enumValues : column.setValues}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => ({
                                                    ...current,
                                                    enumValues: showEnum
                                                        ? event.currentTarget.value
                                                        : current.enumValues,
                                                    setValues: showSet ? event.currentTarget.value : current.setValues,
                                                }))
                                            }
                                            disabled={isSubmitting}
                                        />
                                    </div>
                                )}

                                <div>
                                    <Label>Default value</Label>
                                    <Input
                                        value={column.defaultValue}
                                        onChange={(event) =>
                                            updateColumn(column.id, (current) => ({
                                                ...current,
                                                defaultValue: event.currentTarget.value,
                                            }))
                                        }
                                        disabled={isSubmitting || column.autoIncrement}
                                    />
                                </div>

                                <div css={tw`grid grid-cols-1 md:grid-cols-2 gap-3`}>
                                    <div css={tw`flex items-center`}>
                                        <Input
                                            id={`create-table-nullable-${column.id}`}
                                            type={'checkbox'}
                                            checked={column.nullable}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => ({
                                                    ...current,
                                                    nullable: event.currentTarget.checked,
                                                }))
                                            }
                                            disabled={isSubmitting || column.key === 'PRIMARY'}
                                        />
                                        <Label htmlFor={`create-table-nullable-${column.id}`} css={tw`ml-3 mb-0`}>
                                            Nullable
                                        </Label>
                                    </div>
                                    <div css={tw`flex items-center`}>
                                        <Input
                                            id={`create-table-auto-inc-${column.id}`}
                                            type={'checkbox'}
                                            checked={column.autoIncrement}
                                            onChange={(event) =>
                                                updateColumn(column.id, (current) => ({
                                                    ...current,
                                                    autoIncrement: event.currentTarget.checked,
                                                    key: event.currentTarget.checked ? 'PRIMARY' : current.key,
                                                    nullable: event.currentTarget.checked ? false : current.nullable,
                                                    defaultValue: event.currentTarget.checked
                                                        ? ''
                                                        : current.defaultValue,
                                                }))
                                            }
                                            disabled={isSubmitting || !integerTypes.includes(columnType)}
                                        />
                                        <Label htmlFor={`create-table-auto-inc-${column.id}`} css={tw`ml-3 mb-0`}>
                                            {isPostgres ? 'Generated identity' : 'Auto increment'}
                                        </Label>
                                    </div>
                                </div>
                            </div>
                        );
                    })}
                </div>
            </div>

            <Dialog.Footer>
                <Button type={'button'} variant={Button.Variants.Secondary} onClick={onClose} disabled={isSubmitting}>
                    Cancel
                </Button>
                <Button type={'button'} onClick={submit} disabled={isSubmitting}>
                    Create Table
                </Button>
            </Dialog.Footer>
        </Dialog>
    );
};

export default CreateTableDialog;