// ── Edit Table Modal ─────────────────────────────────────────────────
let editTableOriginalName = '';

const editDialect = window.databaseDialect || {};
const editFieldTypes = Array.isArray(editDialect.field_types) && editDialect.field_types.length ? editDialect.field_types : ['VARCHAR'];
const editNoLengthTypes = new Set(editDialect.no_length_types || []);
const editNoDefaultTypes = new Set(editDialect.no_value_default_types || []);
const editIntegerTypes = new Set(editDialect.integer_types || ['INT']);
const editFloatingTypes = new Set(['FLOAT', 'DOUBLE', 'REAL']);
const editSizedStringTypes = new Set(editDialect.sized_types || ['VARCHAR', 'CHAR']);
const editEnumLikeTypes = new Set(editDialect.enum_types || []);
const editTemporalTypes = new Set(editDialect.temporal_types || ['DATETIME', 'TIMESTAMP']);
const editIdentityType = editFieldTypes.includes('INT') ? 'INT' : (editFieldTypes.includes('INTEGER') ? 'INTEGER' : [...editIntegerTypes][0]);

function editEscapeAttr(value) {
    return String(value ?? '').replace(/[&<>"']/g, char => ({
        '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;', "'": '&#039;'
    }[char]));
}

function editTypeOptions(selectedType) {
    const selected = String(selectedType || 'VARCHAR').toUpperCase();
    const options = editFieldTypes.includes(selected) ? editFieldTypes : [selected, ...editFieldTypes];
    return options.map(type => `<option value="${type}" ${type === selected ? 'selected' : ''}>${type}</option>`).join('');
}

function editRowControls(row) {
    return {
        nameInput: row.querySelector('.field-name'),
        typeSelect: row.querySelector('.field-type'),
        sizeInput: row.querySelector('.field-size'),
        scaleInput: row.querySelector('.field-scale'),
        nullSelect: row.querySelector('.null-select'),
        defaultMode: row.querySelector('.default-mode'),
        defaultInput: row.querySelector('.field-default'),
        pkCheck: row.querySelector('.pk-check'),
        aiCheck: row.querySelector('.ai-check'),
        enumInput: row.querySelector('.enum-values'),
    };
}

function syncEditDefaultControls(row) {
    const { typeSelect, nullSelect, defaultMode, defaultInput } = editRowControls(row);
    if (!typeSelect || !defaultMode || !defaultInput) return;

    const type = typeSelect.value.toUpperCase();
    const currentMode = defaultMode.value || 'NONE';
    const canUseCurrentTimestamp = editTemporalTypes.has(type);
    const isNullable = nullSelect?.value === '1';
    const requiresExpressionDefault = editNoDefaultTypes.has(type);

    [...defaultMode.options].forEach(option => {
        option.disabled = false;
        if (option.value === 'CURRENT_TIMESTAMP' && !canUseCurrentTimestamp) option.disabled = true;
        if (option.value === 'VALUE' && requiresExpressionDefault) option.disabled = true;
        if (option.value === 'NULL' && !isNullable) option.disabled = true;
    });

    if (defaultMode.selectedOptions[0]?.disabled) {
        defaultMode.value = currentMode === 'VALUE' && requiresExpressionDefault ? 'EXPRESSION' : 'NONE';
    }

    const mode = defaultMode.value || 'NONE';
    const needsInput = mode === 'VALUE' || mode === 'EXPRESSION';
    defaultInput.disabled = !needsInput;
    defaultInput.placeholder = mode === 'EXPRESSION' ? 'e.g., JSON_ARRAY()' : 'Default value';
    if (!needsInput) defaultInput.value = '';
}

function refreshEditFieldRowIds() {
    document.querySelectorAll('#editFieldRows .field-row').forEach((row, index) => {
        const pk = row.querySelector('.pk-check');
        const ai = row.querySelector('.ai-check');
        if (pk) {
            pk.id = `edit_pk_${index}`;
            pk.closest('.form-check')?.querySelector('label')?.setAttribute('for', pk.id);
        }
        if (ai) {
            ai.id = `edit_ai_${index}`;
            ai.closest('.form-check')?.querySelector('label')?.setAttribute('for', ai.id);
        }
    });
}

function applyEditTypeRules(row) {
    const { typeSelect, sizeInput, scaleInput, enumInput } = editRowControls(row);
    if (!typeSelect) return;

    const type = typeSelect.value.toUpperCase();
    if (enumInput) {
        enumInput.style.display = editEnumLikeTypes.has(type) ? 'block' : 'none';
        if (!editEnumLikeTypes.has(type)) enumInput.value = '';
    }
    if (scaleInput) {
        const supportsScale = type === 'DECIMAL' || type === 'NUMERIC';
        scaleInput.style.display = supportsScale ? '' : 'none';
        scaleInput.disabled = !supportsScale;
        if (supportsScale && scaleInput.value === '') scaleInput.value = '2';
    }

    syncEditDefaultControls(row);

    if (!sizeInput) return;
    if (editSizedStringTypes.has(type) && !sizeInput.value) {
        sizeInput.value = '255';
    } else if (editIntegerTypes.has(type) || editFloatingTypes.has(type)) {
        sizeInput.value = '';
    } else if ((type === 'DECIMAL' || type === 'NUMERIC') && (!sizeInput.value || sizeInput.value === '255')) {
        sizeInput.value = '10';
    }

    if (editNoLengthTypes.has(type)) {
        sizeInput.value = '';
        sizeInput.disabled = true;
    } else {
        sizeInput.disabled = false;
    }
}

function applyEditKeyRules(row) {
    const { typeSelect, sizeInput, nullSelect, defaultMode, defaultInput, pkCheck, aiCheck } = editRowControls(row);
    if (!typeSelect || !pkCheck || !aiCheck) return;

    if (aiCheck.checked) {
        pkCheck.checked = true;
        pkCheck.disabled = true;
        if (defaultMode) defaultMode.value = 'NONE';
        if (defaultInput) {
            defaultInput.value = '';
            defaultInput.disabled = true;
        }
    } else {
        pkCheck.disabled = false;
    }

    if (pkCheck.checked || aiCheck.checked) {
        typeSelect.value = editIdentityType;
        if (sizeInput) {
            sizeInput.value = '';
            sizeInput.disabled = false;
        }
        if (nullSelect) {
            nullSelect.value = '0';
            nullSelect.disabled = true;
        }
    } else if (nullSelect) {
        nullSelect.disabled = false;
    }

    applyEditTypeRules(row);
    syncEditDefaultControls(row);
}

function bindEditFieldRow(row) {
    const { typeSelect, pkCheck, aiCheck } = editRowControls(row);
    typeSelect?.addEventListener('change', () => {
        applyEditTypeRules(row);
        applyEditKeyRules(row);
    });
    pkCheck?.addEventListener('change', () => applyEditKeyRules(row));
    aiCheck?.addEventListener('change', () => applyEditKeyRules(row));
    row.querySelector('.null-select')?.addEventListener('change', () => syncEditDefaultControls(row));
    row.querySelector('.default-mode')?.addEventListener('change', () => syncEditDefaultControls(row));
    applyEditTypeRules(row);
    applyEditKeyRules(row);
}

function columnSize(col, type) {
    const upperType = String(type || '').toUpperCase();
    if (upperType === 'VARCHAR' || upperType === 'CHAR' || upperType === 'BINARY' || upperType === 'VARBINARY') return col.CHARACTER_MAXIMUM_LENGTH || '';
    if (upperType === 'DECIMAL' || upperType === 'NUMERIC') return col.NUMERIC_PRECISION || '';
    return '';
}

function enumValuesFromColumn(col) {
    const columnType = String(col.COLUMN_TYPE || '');
    const match = columnType.match(/^(enum|set)\((.+)\)$/i);
    return match ? match[2].replace(/'/g, '') : '';
}

function defaultModeFromColumn(col) {
    const rawDefault = col.COLUMN_DEFAULT ?? col.default;
    if (rawDefault === null || rawDefault === undefined) return 'NONE';
    const normalized = String(rawDefault).trim().toUpperCase();
    if (normalized === 'CURRENT_TIMESTAMP' || normalized.startsWith('CURRENT_TIMESTAMP(')) return 'CURRENT_TIMESTAMP';
    return 'VALUE';
}

function defaultValueFromColumn(col) {
    const mode = defaultModeFromColumn(col);
    return mode === 'VALUE' ? (col.COLUMN_DEFAULT ?? col.default ?? '') : '';
}

function createEditFieldRow(col = {}) {
    const row = document.createElement('div');
    row.className = 'field-row schema-field-row';

    const currentType = String(col.DATA_TYPE || col.type || 'VARCHAR').toUpperCase();
    const isPK = (col.COLUMN_KEY || '') === 'PRI' || !!col.primary_key;
    const isAI = (col.EXTRA || '') === 'auto_increment' || !!col.auto_increment;
    const nullable = Object.prototype.hasOwnProperty.call(col, 'nullable')
        ? !!col.nullable
        : (col.IS_NULLABLE || 'YES') === 'YES';
    const enumValues = editEnumLikeTypes.has(currentType) ? enumValuesFromColumn(col) : (col.enum_values || '');
    const size = col.size ?? columnSize(col, currentType);
    const defaultMode = col.default_mode || defaultModeFromColumn(col);
    const defaultValue = col.default_expression || defaultValueFromColumn(col);
    const scale = col.scale ?? col.NUMERIC_SCALE ?? 2;
    const originalName = col.original_name || col.COLUMN_NAME || col.name || '';
    const identityLocked = !!editDialect.capabilities?.identity_change_requires_rebuild && originalName !== '';
    row.dataset.originalName = originalName;

    row.innerHTML = `
        <div class="schema-cell field-name-cell"><input type="text" class="form-control form-control-sm field-name" value="${editEscapeAttr(col.COLUMN_NAME || col.name || '')}" placeholder="Field name"></div>
        <div class="schema-cell field-type-cell"><select class="form-select form-select-sm field-type">${editTypeOptions(currentType)}</select></div>
        <div class="schema-cell field-size-cell">
            <input type="number" class="form-control form-control-sm field-size" placeholder="Size" value="${editEscapeAttr(size)}" min="0">
            <input type="number" class="form-control form-control-sm field-scale" placeholder="Scale" value="${editEscapeAttr(scale)}" min="0" style="display:${currentType === 'DECIMAL' || currentType === 'NUMERIC' ? '' : 'none'}">
        </div>
        <div class="schema-cell field-null-cell"><select class="form-select form-select-sm null-select"><option value="1" ${nullable ? 'selected' : ''}>NULL</option><option value="0" ${!nullable ? 'selected' : ''}>NOT NULL</option></select></div>
        <div class="schema-cell default-cell">
            <select class="form-select form-select-sm default-mode" title="Default mode">
                <option value="NONE" ${defaultMode === 'NONE' ? 'selected' : ''}>No default</option>
                <option value="NULL" ${defaultMode === 'NULL' ? 'selected' : ''}>NULL</option>
                <option value="VALUE" ${defaultMode === 'VALUE' ? 'selected' : ''}>Value</option>
                <option value="CURRENT_TIMESTAMP" ${defaultMode === 'CURRENT_TIMESTAMP' ? 'selected' : ''}>Current time</option>
                <option value="EXPRESSION" ${defaultMode === 'EXPRESSION' ? 'selected' : ''}>Expression</option>
            </select>
            <input type="text" class="form-control form-control-sm field-default" placeholder="Default value" value="${editEscapeAttr(defaultValue)}">
        </div>
        <div class="schema-cell field-key-cell">
            <div class="form-check form-check-inline small mb-0 schema-check schema-check-pk">
                <input type="checkbox" class="form-check-input pk-check" ${isPK ? 'checked' : ''}>
                <label class="form-check-label">PK</label>
            </div>
            <div class="form-check form-check-inline small mb-0 schema-check schema-check-ai">
                <input type="checkbox" class="form-check-input ai-check" ${isAI ? 'checked' : ''} ${identityLocked ? 'disabled' : ''} title="${identityLocked ? 'SQL Server identity cannot be changed on an existing field; add a new identity field or rebuild the table.' : 'Use a database-generated integer identity.'}">
                <label class="form-check-label">AI</label>
            </div>
            <input type="text" class="form-control form-control-sm mt-1 enum-values" placeholder="ENUM/SET values (comma)" value="${editEscapeAttr(enumValues)}" style="display:${editEnumLikeTypes.has(currentType) ? 'block' : 'none'};">
        </div>
        <div class="schema-cell field-remove-cell"><button type="button" class="btn btn-sm btn-outline-danger remove-field py-0 px-1" data-bs-toggle="tooltip" title="Remove this field from the table when changes are saved."><i class="bi bi-x"></i></button></div>
    `;
    bindEditFieldRow(row);
    return row;
}

function populateEditFields(tableName, details) {
    const fieldRows = document.getElementById('editFieldRows');
    fieldRows.innerHTML = '';
    (details.columns || []).forEach(col => fieldRows.appendChild(createEditFieldRow(col)));
    refreshEditFieldRowIds();
    window.initializeTablesTooltips?.(fieldRows);
}

function openEditModal(tableName) {
    editTableOriginalName = tableName;
    window.editTableOriginalName = tableName;
    document.getElementById('editTableNameInput').value = tableName;
    const fieldRows = document.getElementById('editFieldRows');
    fieldRows.innerHTML = '<div class="text-center py-2"><div class="spinner-border spinner-border-sm"></div></div>';

    if (window.tableDetailsCache[tableName]) {
        populateEditFields(tableName, window.tableDetailsCache[tableName]);
    } else {
        fetch(`${baseUrl}/project/${projectSlug}/tables/details/${encodeURIComponent(tableName)}`)
            .then(res => res.json())
            .then(data => {
                if (data.success) {
                    window.tableDetailsCache[tableName] = { columns: data.columns, indexes: data.indexes };
                    populateEditFields(tableName, window.tableDetailsCache[tableName]);
                } else {
                    fieldRows.innerHTML = '<div class="alert alert-danger small">Failed to load fields.</div>';
                }
            })
            .catch(() => fieldRows.innerHTML = '<div class="alert alert-danger small">Error loading fields.</div>');
    }

    new bootstrap.Modal(document.getElementById('editTableModal')).show();
}

function collectEditFields() {
    const fields = [];
    document.querySelectorAll('#editFieldRows .field-row').forEach(row => {
        const { nameInput, typeSelect, sizeInput, scaleInput, nullSelect, defaultMode, defaultInput, pkCheck, aiCheck, enumInput } = editRowControls(row);
        const name = nameInput?.value.trim() || '';
        if (!name) return;
        const mode = defaultMode?.value || 'NONE';
        fields.push({
            name,
            original_name: row.dataset.originalName || name,
            type: typeSelect?.value || 'VARCHAR',
            size: sizeInput?.value || 0,
            scale: scaleInput?.value || 0,
            nullable: nullSelect?.value === '1',
            default_mode: mode,
            default: defaultInput?.value ?? null,
            default_expression: mode === 'EXPRESSION' ? (defaultInput?.value ?? null) : null,
            primary_key: !!pkCheck?.checked,
            auto_increment: !!aiCheck?.checked,
            enum_values: (typeSelect?.value === 'ENUM' || typeSelect?.value === 'SET') ? (enumInput?.value || '') : '',
        });
    });
    return fields;
}

function validateEditTablePayload(tableName, fields) {
    if (!tableName) return 'Table name is required.';
    if (!/^[A-Za-z_][A-Za-z0-9_]*$/.test(tableName)) {
        return 'Table name can contain letters, numbers, and underscores only. It must start with a letter or underscore.';
    }
    if (fields.length === 0) return 'At least one field is required.';

    const names = {};
    for (const field of fields) {
        if (!/^[A-Za-z_][A-Za-z0-9_]*$/.test(field.name)) {
            return `Field "${field.name}" is not valid. Use letters, numbers, and underscores. It must start with a letter or underscore.`;
        }
        const key = field.name.toLowerCase();
        if (names[key]) return `Field "${field.name}" is duplicated.`;
        names[key] = true;
    }
    if (fields.filter(field => field.auto_increment).length > 1) return 'Only one field can use AI in a table.';
    return '';
}

document.getElementById('editAddFieldBtn')?.addEventListener('click', () => {
    const fieldRows = document.getElementById('editFieldRows');
    const defaultType = editFieldTypes.includes('VARCHAR') ? 'VARCHAR' : editFieldTypes[0];
    const row = createEditFieldRow({ name: '', original_name: '', type: defaultType, size: 255, nullable: true });
    fieldRows.appendChild(row);
    refreshEditFieldRowIds();
    window.initializeTablesTooltips?.(row);
});

document.getElementById('editFieldRows')?.addEventListener('click', (e) => {
    const removeBtn = e.target.closest('.remove-field');
    if (!removeBtn) return;
    const rows = document.querySelectorAll('#editFieldRows .field-row');
    if (rows.length <= 1) {
        Swal.fire('Field required', 'A table must have at least one field.', 'warning');
        return;
    }
    removeBtn.closest('.field-row')?.remove();
    refreshEditFieldRowIds();
});

document.getElementById('btnSaveTableChanges')?.addEventListener('click', async () => {
    const tableName = document.getElementById('editTableNameInput').value.trim();
    const fields = collectEditFields();
    const validationMessage = validateEditTablePayload(tableName, fields);
    if (validationMessage) {
        Swal.fire('Invalid table', validationMessage, 'warning');
        return;
    }

    const button = document.getElementById('btnSaveTableChanges');
    const originalHtml = button.innerHTML;
    button.disabled = true;
    button.innerHTML = '<span class="spinner-border spinner-border-sm me-1"></span>Saving';

    try {
        const res = await fetch(`${baseUrl}/project/${projectSlug}/tables/edit/${encodeURIComponent(editTableOriginalName)}`, {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify({ new_table_name: tableName, fields })
        });
        const data = await res.json();
        if (data.success) {
            delete window.tableDetailsCache[editTableOriginalName];
            delete window.tableDetailsCache[tableName];
            Swal.fire('Saved', data.message, 'success');
            bootstrap.Modal.getInstance(document.getElementById('editTableModal')).hide();
            location.reload();
        } else {
            Swal.fire('Error', data.message || 'Failed to update table.', 'error');
        }
    } catch (err) {
        Swal.fire('Error', 'Failed to update table.', 'error');
    } finally {
        button.disabled = false;
        button.innerHTML = originalHtml;
    }
});

window.openEditModal = openEditModal;
