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 { addColumn, OperationResponse } from '../api/tableDataOperations';

type ColumnKey = 'NONE' | 'PRIMARY';

interface AddColumnDialogContext {
    databaseId: string;
    engine?: string;
    tableName: string;
    existingColumns?: string[];
}

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

const FLASH_KEY = 'database:content:add-column-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 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 AddColumnDialog = ({ open, onClose, context, onSuccess }: AddColumnDialogProps) => {
    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 [columnName, setColumnName] = useState('');
    const [type, setType] = useState('VARCHAR');
    const [length, setLength] = useState('255');
    const [precision, setPrecision] = useState('');
    const [scale, setScale] = useState('');
    const [nullable, setNullable] = useState(true);
    const [defaultValue, setDefaultValue] = useState('');
    const [key, setKey] = useState<ColumnKey>('NONE');
    const [autoIncrement, setAutoIncrement] = useState(false);
    const [enumValues, setEnumValues] = useState('');
    const [setValues, setSetValues] = useState('');
    const [after, setAfter] = useState('');

    const showLength = LENGTH_TYPES.includes(type);
    const showPrecision = precisionTypes.includes(type);
    const showEnum = !isPostgres && type === 'ENUM';
    const showSet = !isPostgres && type === 'SET';

    useEffect(() => {
        if (!open) {
            clearFlashes(FLASH_KEY);
            setIsSubmitting(false);
            setColumnName('');
            setType('VARCHAR');
            setLength('255');
            setPrecision('');
            setScale('');
            setNullable(true);
            setDefaultValue('');
            setKey('NONE');
            setAutoIncrement(false);
            setEnumValues('');
            setSetValues('');
            setAfter('');
            return;
        }

        clearFlashes(FLASH_KEY);
    }, [open]);

    const normalizedExistingColumns = useMemo(
        () => new Set((context.existingColumns || []).map((column) => column.toLowerCase())),
        [context.existingColumns]
    );

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

        const trimmedName = columnName.trim();
        if (!trimmedName) {
            return 'Column name is required.';
        }

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

        if (normalizedExistingColumns.has(trimmedName.toLowerCase())) {
            return `Column "${trimmedName}" already exists.`;
        }

        if (!(supportedTypes as readonly string[]).includes(type)) {
            return 'Selected data type is not supported.';
        }

        if (autoIncrement) {
            if (!integerTypes.includes(type)) {
                return `${isPostgres ? 'Generated identity' : 'AUTO_INCREMENT'} requires an integer type.`;
            }

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

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

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

        if (showLength && !parsePositiveInt(length)) {
            return 'Length must be a positive integer for this data type.';
        }

        if (showPrecision) {
            if (precision && !parsePositiveInt(precision)) {
                return 'Precision must be a positive integer.';
            }

            if (scale && parseNonNegativeInt(scale) === undefined) {
                return 'Scale must be a non-negative integer.';
            }

            const parsedPrecision = parsePositiveInt(precision) || parsePositiveInt(length);
            const parsedScale = parseNonNegativeInt(scale);

            if (parsedScale !== undefined && parsedPrecision !== undefined && parsedScale > parsedPrecision) {
                return 'Scale cannot be larger than precision.';
            }
        }

        if (showEnum && parseCsvValues(enumValues).length < 1) {
            return 'ENUM requires at least one value.';
        }

        if (showSet && parseCsvValues(setValues).length < 1) {
            return 'SET requires at least one value.';
        }

        if (!isPostgres && after.trim()) {
            if (!isValidIdentifier(after.trim())) {
                return 'After column must be a valid column name.';
            }

            if (!normalizedExistingColumns.has(after.trim().toLowerCase())) {
                return `After column "${after.trim()}" does not exist.`;
            }
        }

        return null;
    };

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

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

        setIsSubmitting(true);

        try {
            const parsedLength = parsePositiveInt(length);
            const parsedPrecision = parsePositiveInt(precision);
            const parsedScale = parseNonNegativeInt(scale);

            const request: {
                name: string;
                type: string;
                length?: number;
                precision?: number;
                scale?: number;
                enum_values?: string[];
                set_values?: string[];
                nullable: boolean;
                default?: string;
                auto_increment: boolean;
                primary_key?: boolean;
                after?: string;
            } = {
                name: columnName.trim(),
                type,
                nullable,
                auto_increment: autoIncrement,
                primary_key: key === 'PRIMARY',
            };

            if (showLength && parsedLength) {
                request.length = parsedLength;
            }

            if (showPrecision) {
                const effectiveLength = parsedPrecision || parsedLength;
                if (effectiveLength) {
                    request.length = effectiveLength;
                }

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

            if (showEnum) {
                request.enum_values = parseCsvValues(enumValues);
            }

            if (showSet) {
                request.set_values = parseCsvValues(setValues);
            }

            if (!autoIncrement && defaultValue.trim()) {
                request.default = defaultValue.trim();
            }

            if (after.trim()) {
                request.after = after.trim();
            }

            const response = await addColumn(uuid, context.databaseId, context.tableName, request);

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

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

        setIsSubmitting(false);
    };

    return (
        <Dialog
            open={open}
            onClose={onClose}
            title={'Add column'}
            description={`Table: ${context.tableName || '-'}`}
            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 css={tw`grid grid-cols-1 md:grid-cols-2 gap-3`}>
                    <div>
                        <Label htmlFor={'add-column-name'}>Column name</Label>
                        <Input
                            id={'add-column-name'}
                            value={columnName}
                            onChange={(event) => setColumnName(event.currentTarget.value)}
                            disabled={isSubmitting}
                        />
                    </div>
                    <div>
                        <Label>Data type</Label>
                        <select
                            value={type}
                            onChange={(event) => {
                                const nextType = event.currentTarget.value;
                                setType(nextType);

                                if (!LENGTH_TYPES.includes(nextType)) {
                                    setLength('');
                                }

                                if (!precisionTypes.includes(nextType)) {
                                    setPrecision('');
                                    setScale('');
                                }

                                if (nextType !== 'ENUM') {
                                    setEnumValues('');
                                }

                                if (nextType !== 'SET') {
                                    setSetValues('');
                                }

                                if (!integerTypes.includes(nextType)) {
                                    setAutoIncrement(false);
                                }
                            }}
                            disabled={isSubmitting}
                            css={tw`w-full p-3 rounded border border-neutral-500 bg-neutral-600 text-neutral-200 text-sm`}
                        >
                            {supportedTypes.map((supportedType) => (
                                <option key={supportedType} value={supportedType}>
                                    {supportedType}
                                </option>
                            ))}
                        </select>
                    </div>
                </div>

                <div css={tw`grid grid-cols-1 md:grid-cols-3 gap-3`}>
                    <div>
                        <Label>Key</Label>
                        <select
                            value={key}
                            onChange={(event) => {
                                const nextKey = event.currentTarget.value as ColumnKey;
                                setKey(nextKey);
                                if (nextKey === 'PRIMARY') {
                                    setNullable(false);
                                }
                            }}
                            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>

                    {showLength && (
                        <div>
                            <Label>Length</Label>
                            <Input
                                type={'number'}
                                min={1}
                                value={length}
                                onChange={(event) => setLength(event.currentTarget.value)}
                                disabled={isSubmitting}
                            />
                        </div>
                    )}

                    {showPrecision && (
                        <div>
                            <Label>Precision</Label>
                            <Input
                                type={'number'}
                                min={1}
                                value={precision}
                                onChange={(event) => setPrecision(event.currentTarget.value)}
                                disabled={isSubmitting}
                            />
                        </div>
                    )}

                    {showPrecision && (
                        <div>
                            <Label>Scale</Label>
                            <Input
                                type={'number'}
                                min={0}
                                value={scale}
                                onChange={(event) => setScale(event.currentTarget.value)}
                                disabled={isSubmitting}
                            />
                        </div>
                    )}
                </div>

                {showEnum && (
                    <div>
                        <Label>ENUM values (comma separated)</Label>
                        <Input
                            value={enumValues}
                            onChange={(event) => setEnumValues(event.currentTarget.value)}
                            disabled={isSubmitting}
                        />
                    </div>
                )}

                {showSet && (
                    <div>
                        <Label>SET values (comma separated)</Label>
                        <Input
                            value={setValues}
                            onChange={(event) => setSetValues(event.currentTarget.value)}
                            disabled={isSubmitting}
                        />
                    </div>
                )}

                <div>
                    <Label htmlFor={'add-column-default'}>Default value</Label>
                    <Input
                        id={'add-column-default'}
                        value={defaultValue}
                        onChange={(event) => setDefaultValue(event.currentTarget.value)}
                        disabled={isSubmitting || autoIncrement}
                    />
                </div>

                {!isPostgres && (
                    <div>
                        <Label htmlFor={'add-column-after'}>After column (optional)</Label>
                        <Input
                            id={'add-column-after'}
                            list={'add-column-after-list'}
                            value={after}
                            onChange={(event) => setAfter(event.currentTarget.value)}
                            disabled={isSubmitting}
                        />
                        <datalist id={'add-column-after-list'}>
                            {(context.existingColumns || []).map((column) => (
                                <option key={column} value={column} />
                            ))}
                        </datalist>
                    </div>
                )}

                <div css={tw`grid grid-cols-1 md:grid-cols-2 gap-3`}>
                    <div css={tw`flex items-center`}>
                        <Input
                            id={'add-column-nullable'}
                            type={'checkbox'}
                            checked={nullable}
                            onChange={(event) => setNullable(event.currentTarget.checked)}
                            disabled={isSubmitting || key === 'PRIMARY'}
                        />
                        <Label htmlFor={'add-column-nullable'} css={tw`ml-3 mb-0`}>
                            Nullable
                        </Label>
                    </div>
                    <div css={tw`flex items-center`}>
                        <Input
                            id={'add-column-auto-increment'}
                            type={'checkbox'}
                            checked={autoIncrement}
                            onChange={(event) => {
                                const checked = event.currentTarget.checked;
                                setAutoIncrement(checked);

                                if (checked) {
                                    setKey('PRIMARY');
                                    setNullable(false);
                                    setDefaultValue('');
                                }
                            }}
                            disabled={isSubmitting || !integerTypes.includes(type)}
                        />
                        <Label htmlFor={'add-column-auto-increment'} css={tw`ml-3 mb-0`}>
                            {isPostgres ? 'Generated identity' : 'Auto increment'}
                        </Label>
                    </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}>
                    Add Column
                </Button>
            </Dialog.Footer>
        </Dialog>
    );
};

export default AddColumnDialog;