ProjectDatabase.tsx

import { useState, useEffect } from "react";
import { useParams } from "react-router";
import { ProjectSidebar } from "../components/ProjectSidebar";
import { projectService } from "../services/projectService";
import { dynamicDBService } from "../services/dynamicDBService";
import type { ColumnReq, DynTableDef } from "../services/dynamicDBService";
import { Button } from "@/components/ui/button";
import { Input } from "@/components/ui/input";
import { Label } from "@/components/ui/label";
import {
    Dialog,
    DialogContent,
    DialogDescription,
    DialogFooter,
    DialogHeader,
    DialogTitle,
    DialogTrigger,
} from "@/components/ui/dialog";
import { Plus, Loader2, Database, Table as TableIcon, Trash2, ChevronDown, ChevronRight, Columns } from "lucide-react";


export default function ProjectDatabase() {
    const { id } = useParams<{ id: string }>();
    const projectId = id ? parseInt(id) : 0;

    const [project, setProject] = useState<{ id: number; name: string } | null>(null);
    const [loadingProject, setLoadingProject] = useState(true);

    // Tables State
    const [tables, setTables] = useState<DynTableDef[]>([]);
    const [loadingTables, setLoadingTables] = useState(false);

    // Create Table State
    const [createDialogOpen, setCreateDialogOpen] = useState(false);
    const [tableName, setTableName] = useState("");

    // Backend Implementation of CreateDynamicTable adds: id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    // So ID and Created_At are automatic. The user just defines *additional* columns.

    const [newColumns, setNewColumns] = useState<ColumnReq[]>([]);
    const [createLoading, setCreateLoading] = useState(false);
    const [createError, setCreateError] = useState<string | null>(null);

    // Add Column State
    const [addColDialogOpen, setAddColDialogOpen] = useState(false);
    const [selectedTable, setSelectedTable] = useState<DynTableDef | null>(null);
    const [colsToAdd, setColsToAdd] = useState<ColumnReq[]>([]);
    const [addColLoading, setAddColLoading] = useState(false);

    // Expand/Collapse State
    const [expandedTables, setExpandedTables] = useState<Record<number, boolean>>({});

    // Delete Table State
    const [deleteDialogOpen, setDeleteDialogOpen] = useState(false);
    const [tableToDelete, setTableToDelete] = useState<DynTableDef | null>(null);
    const [deleteLoading, setDeleteLoading] = useState(false);


    useEffect(() => {
        if (projectId) {
            fetchProject();
            fetchTables();
        }
    }, [projectId, id]);

    const fetchProject = async () => {
        try {
            const data = await projectService.getProjectById(id!);
            setProject(data);
        } catch (error) {
            console.error("Failed to fetch project", error);
        } finally {
            setLoadingProject(false);
        }
    };

    const fetchTables = async () => {
        setLoadingTables(true);
        try {
            const data = await dynamicDBService.getTables(projectId);
            setTables(data);
        } catch (error) {
            console.error("Failed to fetch tables", error);
        } finally {
            setLoadingTables(false);
        }
    };

    const toggleTableExpand = (tableId: number) => {
        setExpandedTables(prev => ({ ...prev, [tableId]: !prev[tableId] }));
    };

    // --- Create Table Handlers ---

    const handleAddColumnSlot = () => {
        setNewColumns([...newColumns, { name: "", type: "VARCHAR(255)" }]);
    };

    const handleRemoveColumnSlot = (index: number) => {
        const updated = [...newColumns];
        updated.splice(index, 1);
        setNewColumns(updated);
    };

    const handleColumnChange = (index: number, field: keyof ColumnReq, value: string) => {
        const updated = [...newColumns];
        updated[index] = { ...updated[index], [field]: value };
        setNewColumns(updated);
    };

    const handleCreateTable = async () => {
        if (!tableName) {
            setCreateError("Table name is required");
            return;
        }
        setCreateLoading(true);
        setCreateError(null);

        try {
            await dynamicDBService.createTable(projectId, {
                table_name: tableName,
                columns: newColumns
            });

            setCreateDialogOpen(false);
            setTableName("");
            setNewColumns([]);
            fetchTables(); // Refresh list
        } catch (err: any) {
            console.error("Failed to create table", err);
            setCreateError(err.response?.data?.message || err.message || "Failed to create table");
        } finally {
            setCreateLoading(false);
        }
    };

    // --- Add Column Handlers ---

    const openAddColDialog = (table: DynTableDef) => {
        setSelectedTable(table);
        setColsToAdd([{ name: "", type: "VARCHAR(255)" }]);
        setAddColDialogOpen(true);
    };

    const handleAddColSlot_Add = () => {
        setColsToAdd([...colsToAdd, { name: "", type: "VARCHAR(255)" }]);
    };

    const handleRemoveColSlot_Add = (index: number) => {
        const updated = [...colsToAdd];
        updated.splice(index, 1);
        setColsToAdd(updated);
    };

    const handleColChange_Add = (index: number, field: keyof ColumnReq, value: string) => {
        const updated = [...colsToAdd];
        updated[index] = { ...updated[index], [field]: value };
        setColsToAdd(updated);
    };

    const handleAddColumns = async () => {
        if (!selectedTable) return;

        // Validate
        if (colsToAdd.some(c => !c.name)) {
            // simple validation
            return;
        }

        setAddColLoading(true);
        try {
            await dynamicDBService.addColumn(projectId, selectedTable.id, {
                table_name: selectedTable.alias, // actually wrapper ignores this for add col usually, but good to have
                columns: colsToAdd
            });
            setAddColDialogOpen(false);
            setColsToAdd([]);
            setSelectedTable(null);
            fetchTables();
        } catch (err) {
            console.error("Failed to add columns", err);
        } finally {
            setAddColLoading(false);
        }
    };


    const openDeleteDialog = (table: DynTableDef) => {
        setTableToDelete(table);
        setDeleteDialogOpen(true);
    };

    const confirmDeleteTable = async () => {
        if (!tableToDelete) return;
        setDeleteLoading(true);
        try {
            await dynamicDBService.deleteTable(projectId, tableToDelete.id);
            setDeleteDialogOpen(false);
            setTableToDelete(null);
            fetchTables();
        } catch (err) {
            console.error("Failed to delete table", err);
            // In a real app, show a toast here
        } finally {
            setDeleteLoading(false);
        }
    };

    if (loadingProject) {
        return <div className="flex h-screen items-center justify-center bg-slate-50"><Loader2 className="animate-spin text-sky-600" /></div>;
    }

    if (!project) return <div>Project not found</div>;

    return (
        <div className="flex w-full h-screen bg-slate-50 relative overflow-hidden">
            <ProjectSidebar project={project} />

            <main className="flex-1 flex flex-col h-full z-10 overflow-hidden">
                <div className="flex-1 overflow-y-auto p-8">
                    <div className="max-w-6xl mx-auto space-y-8">
                        <div className="flex items-center justify-between">
                            <div>
                                <h2 className="text-2xl font-bold text-sky-900 tracking-tight flex items-center gap-2">
                                    <Database className="h-6 w-6 text-sky-600" />
                                    Database
                                </h2>
                                <p className="text-sky-600">Manage your project's database tables and schema.</p>
                            </div>

                            <Dialog open={createDialogOpen} onOpenChange={setCreateDialogOpen}>
                                <DialogTrigger asChild>
                                    <Button className="bg-sky-600 hover:bg-sky-700 text-white shadow-sm">
                                        <Plus className="mr-2 h-4 w-4" />
                                        Create Table
                                    </Button>
                                </DialogTrigger>
                                <DialogContent className="sm:max-w-[600px] max-h-[90vh] overflow-y-auto">
                                    <DialogHeader>
                                        <DialogTitle className="text-sky-900">Create New Table</DialogTitle>
                                        <DialogDescription>
                                            Define the table name and its initial columns. ID and Created_At are added automatically.
                                        </DialogDescription>
                                    </DialogHeader>

                                    <div className="grid gap-6 py-4">
                                        <div className="space-y-2">
                                            <Label htmlFor="tableName">Table Name</Label>
                                            <Input
                                                id="tableName"
                                                placeholder="e.g., users"
                                                value={tableName}
                                                onChange={(e) => setTableName(e.target.value)}
                                                className="border-sky-200 focus-visible:ring-sky-400"
                                            />
                                        </div>

                                        <div className="space-y-4">
                                            <div className="flex items-center justify-between">
                                                <Label>Columns</Label>
                                                <Button type="button" variant="outline" size="sm" onClick={handleAddColumnSlot} className="text-sky-600 border-sky-200 hover:bg-sky-50">
                                                    <Plus className="h-3 w-3 mr-1" /> Add Column
                                                </Button>
                                            </div>

                                            {newColumns.length === 0 && (
                                                <div className="text-sm text-slate-500 italic text-center py-4 border border-dashed border-slate-200 rounded-lg">
                                                    No additional columns. Table will only have 'id' and 'created_at'.
                                                </div>
                                            )}

                                            {newColumns.map((col, idx) => (
                                                <div key={idx} className="flex gap-3 items-end p-3 bg-slate-50 rounded-lg border border-slate-100">
                                                    <div className="flex-1 space-y-1">
                                                        <Label className="text-xs">Name</Label>
                                                        <Input
                                                            value={col.name}
                                                            onChange={(e) => handleColumnChange(idx, "name", e.target.value)}
                                                            placeholder="col_name"
                                                            className="h-8 text-sm"
                                                        />
                                                    </div>
                                                    <div className="w-[180px] space-y-1">
                                                        <Label className="text-xs">Type</Label>
                                                        <select
                                                            className="h-8 text-sm w-full rounded-md border border-input bg-background px-3 py-1 ring-offset-background placeholder:text-muted-foreground focus:outline-none focus:ring-2 focus:ring-ring focus:ring-offset-2 disabled:cursor-not-allowed disabled:opacity-50"
                                                            value={col.type}
                                                            onChange={(e) => handleColumnChange(idx, "type", e.target.value)}
                                                        >
                                                            <option value="VARCHAR(255)">VARCHAR(255)</option>
                                                            <option value="TEXT">TEXT</option>
                                                            <option value="INT">INT</option>
                                                            <option value="BOOLEAN">BOOLEAN</option>
                                                            <option value="TIMESTAMP">TIMESTAMP</option>
                                                            <option value="DECIMAL(10,2)">DECIMAL</option>
                                                        </select>
                                                    </div>
                                                    <Button
                                                        type="button"
                                                        variant="ghost"
                                                        size="icon"
                                                        className="h-8 w-8 text-red-500 hover:bg-red-50"
                                                        onClick={() => handleRemoveColumnSlot(idx)}
                                                    >
                                                        <Trash2 className="h-4 w-4" />
                                                    </Button>
                                                </div>
                                            ))}
                                        </div>

                                        {createError && (
                                            <div className="p-3 bg-red-50 text-red-600 text-sm rounded-md">
                                                {createError}
                                            </div>
                                        )}
                                    </div>

                                    <DialogFooter>
                                        <Button
                                            onClick={handleCreateTable}
                                            disabled={createLoading}
                                            className="bg-sky-600 hover:bg-sky-700 text-white"
                                        >
                                            {createLoading ? (
                                                <>
                                                    <Loader2 className="mr-2 h-4 w-4 animate-spin" />
                                                    Creating...
                                                </>
                                            ) : (
                                                "Create Table"
                                            )}
                                        </Button>
                                    </DialogFooter>
                                </DialogContent>
                            </Dialog>

                            {/* Add Column Dialog */}
                            <Dialog open={addColDialogOpen} onOpenChange={setAddColDialogOpen}>
                                <DialogContent className="sm:max-w-[600px]">
                                    <DialogHeader>
                                        <DialogTitle className="text-sky-900">Add Columns to {selectedTable?.alias}</DialogTitle>
                                        <DialogDescription>
                                            Add new columns to this table.
                                        </DialogDescription>
                                    </DialogHeader>
                                    <div className="grid gap-6 py-4">
                                        <div className="space-y-4">
                                            <div className="flex items-center justify-between">
                                                <Label>Columns</Label>
                                                <Button type="button" variant="outline" size="sm" onClick={handleAddColSlot_Add} className="text-sky-600 border-sky-200 hover:bg-sky-50">
                                                    <Plus className="h-3 w-3 mr-1" /> Add Column
                                                </Button>
                                            </div>

                                            {colsToAdd.map((col, idx) => (
                                                <div key={idx} className="flex gap-3 items-end p-3 bg-slate-50 rounded-lg border border-slate-100">
                                                    <div className="flex-1 space-y-1">
                                                        <Label className="text-xs">Name</Label>
                                                        <Input
                                                            value={col.name}
                                                            onChange={(e) => handleColChange_Add(idx, "name", e.target.value)}
                                                            placeholder="col_name"
                                                            className="h-8 text-sm"
                                                        />
                                                    </div>
                                                    <div className="w-[180px] space-y-1">
                                                        <Label className="text-xs">Type</Label>
                                                        <select
                                                            className="h-8 text-sm w-full rounded-md border border-input bg-background px-3 py-1 ring-offset-background placeholder:text-muted-foreground focus:outline-none focus:ring-2 focus:ring-ring focus:ring-offset-2 disabled:cursor-not-allowed disabled:opacity-50"
                                                            value={col.type}
                                                            onChange={(e) => handleColChange_Add(idx, "type", e.target.value)}
                                                        >
                                                            <option value="VARCHAR(255)">VARCHAR(255)</option>
                                                            <option value="TEXT">TEXT</option>
                                                            <option value="INT">INT</option>
                                                            <option value="BOOLEAN">BOOLEAN</option>
                                                            <option value="TIMESTAMP">TIMESTAMP</option>
                                                            <option value="DECIMAL(10,2)">DECIMAL</option>
                                                        </select>
                                                    </div>
                                                    <Button
                                                        type="button"
                                                        variant="ghost"
                                                        size="icon"
                                                        className="h-8 w-8 text-red-500 hover:bg-red-50"
                                                        onClick={() => handleRemoveColSlot_Add(idx)}
                                                    >
                                                        <Trash2 className="h-4 w-4" />
                                                    </Button>
                                                </div>
                                            ))}
                                        </div>
                                    </div>
                                    <DialogFooter>
                                        <Button
                                            onClick={handleAddColumns}
                                            disabled={addColLoading}
                                            className="bg-sky-600 hover:bg-sky-700 text-white"
                                        >
                                            {addColLoading ? (
                                                <>
                                                    <Loader2 className="mr-2 h-4 w-4 animate-spin" />
                                                    Adding...
                                                </>
                                            ) : (
                                                "Add Columns"
                                            )}
                                        </Button>
                                    </DialogFooter>
                                </DialogContent>
                            </Dialog>

                            {/* Delete Confirmation Dialog */}
                            <Dialog open={deleteDialogOpen} onOpenChange={setDeleteDialogOpen}>
                                <DialogContent>
                                    <DialogHeader>
                                        <DialogTitle className="text-red-600 flex items-center gap-2">
                                            <Trash2 className="h-5 w-5" /> Delete Table
                                        </DialogTitle>
                                        <DialogDescription>
                                            Are you sure you want to delete the table <strong>{tableToDelete?.alias}</strong>?
                                            <br /><br />
                                            This action cannot be undone and will permanently delete all data in this table.
                                        </DialogDescription>
                                    </DialogHeader>
                                    <DialogFooter>
                                        <Button variant="outline" onClick={() => setDeleteDialogOpen(false)} disabled={deleteLoading}>
                                            Cancel
                                        </Button>
                                        <Button
                                            variant="destructive"
                                            onClick={confirmDeleteTable}
                                            disabled={deleteLoading}
                                            className="bg-red-600 hover:bg-red-700"
                                        >
                                            {deleteLoading ? (
                                                <>
                                                    <Loader2 className="mr-2 h-4 w-4 animate-spin" />
                                                    Deleting...
                                                </>
                                            ) : (
                                                "Delete Table"
                                            )}
                                        </Button>
                                    </DialogFooter>
                                </DialogContent>
                            </Dialog>
                        </div>

                        {/* Tables List */}
                        {loadingTables ? (
                            <div className="flex justify-center p-12">
                                <Loader2 className="h-8 w-8 animate-spin text-sky-600" />
                            </div>
                        ) : tables.length > 0 ? (
                            <div className="grid gap-4">
                                {tables.map((t) => (
                                    <div key={t.id} className="bg-white rounded-xl border border-sky-100 shadow-sm overflow-hidden animate-in fade-in slide-in-from-bottom-2 duration-300">
                                        <div
                                            className="p-4 flex items-center justify-between bg-sky-50/50 hover:bg-sky-50 transition-colors cursor-pointer"
                                            onClick={() => toggleTableExpand(t.id)}
                                        >
                                            <div className="flex items-center space-x-3">
                                                <div className="p-2 bg-sky-100/50 rounded-lg text-sky-600">
                                                    <TableIcon className="h-5 w-5" />
                                                </div>
                                                <div>
                                                    <h3 className="font-semibold text-sky-900">{t.alias}</h3>
                                                    <p className="text-xs text-sky-500 font-mono">{t.name}</p>
                                                </div>
                                            </div>
                                            <div className="flex items-center space-x-2">
                                                <Button
                                                    size="icon"
                                                    variant="ghost"
                                                    className="h-8 w-8 text-red-500 hover:text-red-700 hover:bg-red-100"
                                                    onClick={(e) => {
                                                        e.stopPropagation();
                                                        openDeleteDialog(t);
                                                    }}
                                                    title="Drop Table"
                                                >
                                                    <Trash2 className="h-4 w-4" />
                                                </Button>
                                                <Button size="sm" variant="ghost" className="text-sky-600" onClick={(e) => {
                                                    e.stopPropagation();
                                                    openAddColDialog(t);
                                                }}>
                                                    <Plus className="h-4 w-4 mr-1" /> Add Column
                                                </Button>
                                                {expandedTables[t.id] ? <ChevronDown className="h-5 w-5 text-sky-400" /> : <ChevronRight className="h-5 w-5 text-sky-400" />}
                                            </div>
                                        </div>

                                        {/* Columns View */}
                                        {expandedTables[t.id] && (
                                            <div className="p-4 border-t border-sky-100 bg-slate-50/50">
                                                <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-3">
                                                    {t.columns?.map((col) => (
                                                        <div key={col.id} className="bg-white p-3 rounded-lg border border-sky-100 flex items-center justify-between group hover:border-sky-300 transition-colors">
                                                            <div className="flex items-center space-x-2 truncate">
                                                                <Columns className="h-4 w-4 text-slate-400" />
                                                                <span className="text-sm font-medium text-slate-700 truncate" title={col.name}>{col.name}</span>
                                                            </div>
                                                            <span className="text-xs px-2 py-1 bg-slate-100 text-slate-500 rounded-md font-mono">{col.data_type}</span>
                                                        </div>
                                                    ))}
                                                    {(!t.columns || t.columns.length === 0) && (
                                                        <div className="col-span-full text-sm text-slate-400 italic text-center py-2">
                                                            No custom columns defined.
                                                        </div>
                                                    )}
                                                </div>
                                            </div>
                                        )}
                                    </div>
                                ))}
                            </div>
                        ) : (
                            <div className="col-span-full flex flex-col items-center justify-center p-12 bg-white/50 backdrop-blur-sm rounded-xl border border-sky-100 border-dashed">
                                <div className="p-4 bg-slate-50 rounded-full mb-4">
                                    <Database className="h-12 w-12 text-slate-300" />
                                </div>
                                <h3 className="text-lg font-medium text-slate-900">No tables found</h3>
                                <p className="text-slate-500 max-w-sm text-center">
                                    Create your first table to start storing data in your project.
                                </p>
                            </div>
                        )}

                        {/* Info Note */}
                        <div className="bg-blue-50 border border-blue-100 rounded-lg p-4 text-sm text-blue-700">
                            <strong>Note:</strong> Table management is currently in beta. You can create tables and add columns.
                        </div>
                    </div>
                </div>
            </main>
        </div>
    );
}