/// Visual node-based programming system for event handlers

use serde::{Deserialize, Serialize};
use std::collections::HashSet;

use super::node_registry;

// ==================== Node Libraries ====================

/// Available node libraries that can be enabled/disabled
#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, Serialize, Deserialize)]
pub enum NodeLibrary {
    Core,   // Always enabled - basic flow, variables, math, component access
    Http,   // Network requests
    Sql,    // Database operations
    FileSystem, // File operations
    EnvironmentVariables, // Environment variable helpers
    Json,   // JSON manipulation and comparison operations
}

impl NodeLibrary {
    pub fn display_name(&self) -> &str {
        match self {
            NodeLibrary::Core => "Core",
            NodeLibrary::Http => "HTTP / Network",
            NodeLibrary::Sql => "SQL / Database",
            NodeLibrary::FileSystem => "File System",
            NodeLibrary::EnvironmentVariables => "Environment Variables",
            NodeLibrary::Json => "JSON Operations",
        }
    }

    pub fn description(&self) -> &str {
        match self {
            NodeLibrary::Core => "Basic nodes: flow control, variables, math, component access. Always enabled.",
            NodeLibrary::Http => "HTTP requests: GET, POST, PUT, DELETE, JSON parsing.",
            NodeLibrary::Sql => "Database operations: SELECT, INSERT, UPDATE, DELETE, query building.",
            NodeLibrary::FileSystem => "File operations: Read, write, append, delete files and check existence.",
            NodeLibrary::EnvironmentVariables => "Environment variable helpers: expand %VAR% patterns.",
            NodeLibrary::Json => "JSON manipulation: extract paths, arrays, compare datasets, split strings.",
        }
    }

    pub fn is_always_enabled(&self) -> bool {
        matches!(self, NodeLibrary::Core)
    }

    pub fn all() -> Vec<NodeLibrary> {
        vec![
            NodeLibrary::Core,
            NodeLibrary::Http,
            NodeLibrary::Sql,
            NodeLibrary::FileSystem,
            NodeLibrary::EnvironmentVariables,
            NodeLibrary::Json,
        ]
    }
}

/// Manages which node libraries are enabled
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct LibraryManager {
    pub enabled: HashSet<NodeLibrary>,
}

impl Default for LibraryManager {
    fn default() -> Self {
        let mut enabled = HashSet::new();
        enabled.insert(NodeLibrary::Core); // Core is always enabled
        Self { enabled }
    }
}

impl LibraryManager {
    pub fn is_enabled(&self, library: NodeLibrary) -> bool {
        library.is_always_enabled() || self.enabled.contains(&library)
    }

    pub fn set_enabled(&mut self, library: NodeLibrary, enabled: bool) {
        if library.is_always_enabled() {
            return; // Can't disable core
        }
        if enabled {
            self.enabled.insert(library);
        } else {
            self.enabled.remove(&library);
        }
    }

    pub fn is_node_available(&self, node_type: NodeType) -> bool {
        self.is_enabled(node_type.library())
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum PinType {
    Exec,       // White execution flow
    String,     // Pink
    Float,      // Green
    Bool,       // Red
    Color,      // Purple [f32; 4]
    BigInt,     // Yellow - arbitrary precision integer
    Row,        // Cyan - single database row
    Rows,       // Teal - multiple database rows
    Condition,  // Orange - SQL WHERE condition
    Array,      // Lime green - collection of values
    Map,        // Sky blue - key-value pairs
    Tuple,      // Lavender - fixed-size tuple (2-4 elements)
    Any,        // Gray - accepts any type
}

impl PinType {
    pub fn color(&self) -> [f32; 4] {
        match self {
            PinType::Exec => [1.0, 1.0, 1.0, 1.0],         // White
            PinType::String => [1.0, 0.4, 0.7, 1.0],       // Pink
            PinType::Float => [0.3, 0.9, 0.3, 1.0],        // Green
            PinType::Bool => [0.9, 0.3, 0.3, 1.0],         // Red
            PinType::Color => [0.6, 0.4, 1.0, 1.0],        // Purple
            PinType::BigInt => [1.0, 0.9, 0.2, 1.0],       // Yellow
            PinType::Row => [0.3, 0.8, 0.9, 1.0],          // Cyan
            PinType::Rows => [0.2, 0.7, 0.7, 1.0],         // Teal
            PinType::Condition => [1.0, 0.6, 0.2, 1.0],    // Orange
            PinType::Array => [0.6, 1.0, 0.4, 1.0],        // Lime green
            PinType::Map => [0.4, 0.8, 1.0, 1.0],          // Sky blue
            PinType::Tuple => [0.8, 0.6, 1.0, 1.0],        // Lavender
            PinType::Any => [0.6, 0.6, 0.6, 1.0],          // Gray
        }

    }

    pub fn display_name(&self) -> &str {
        match self {
            PinType::Exec => "Flow",
            PinType::String => "String",
            PinType::Float => "Number",
            PinType::Bool => "Boolean",
            PinType::Color => "Color",
            PinType::BigInt => "Big Integer",
            PinType::Row => "Database Row",
            PinType::Rows => "Row List",
            PinType::Condition => "Condition",
            PinType::Array => "Array",
            PinType::Map => "Dictionary",
            PinType::Tuple => "Tuple",
            PinType::Any => "Any",
        }
    }

    pub fn is_compatible(&self, other: &PinType) -> bool {
        // Exec pins can only connect to other Exec pins
        if matches!(self, PinType::Exec) || matches!(other, PinType::Exec) {
            return self == other;
        }

        // All data types are compatible with each other (automatic type conversion at runtime)
        true
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum PinValue {
    String(String),
    Float(f32),
    Bool(bool),
    Color([f32; 4]),
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Pin {
    pub id: usize,
    pub pin_type: PinType,
    pub is_input: bool,
    pub name: String,
    pub value: Option<PinValue>,  // For literal values
}

impl Pin {
    pub fn new(id: usize, pin_type: PinType, is_input: bool, name: &str) -> Self {
        Self {
            id,
            pin_type,
            is_input,
            name: name.to_string(),
            value: None,
        }
    }

    pub fn with_value(mut self, value: PinValue) -> Self {
        self.value = Some(value);
        self
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum MathOp {
    Add,
    Subtract,
    Multiply,
    Divide,
    LessThan,
    GreaterThan,
    Equals,
}

impl MathOp {
    pub fn symbol(&self) -> &str {
        match self {
            MathOp::Add => "+",
            MathOp::Subtract => "-",
            MathOp::Multiply => "*",
            MathOp::Divide => "/",
            MathOp::LessThan => "<",
            MathOp::GreaterThan => ">",
            MathOp::Equals => "==",
        }
    }

    pub fn all() -> Vec<MathOp> {
        vec![
            MathOp::Add,
            MathOp::Subtract,
            MathOp::Multiply,
            MathOp::Divide,
            MathOp::LessThan,
            MathOp::GreaterThan,
            MathOp::Equals,
        ]
    }
}

/// BigInt arithmetic operations
#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum BigIntOp {
    Add,
    Subtract,
    Multiply,
    Divide,
    Modulo,
    Power,
}

impl BigIntOp {
    pub fn display_name(&self) -> &str {
        match self {
            BigIntOp::Add => "Add",
            BigIntOp::Subtract => "Subtract",
            BigIntOp::Multiply => "Multiply",
            BigIntOp::Divide => "Divide",
            BigIntOp::Modulo => "Modulo",
            BigIntOp::Power => "Power",
        }
    }

    pub fn all() -> Vec<BigIntOp> {
        vec![
            BigIntOp::Add,
            BigIntOp::Subtract,
            BigIntOp::Multiply,
            BigIntOp::Divide,
            BigIntOp::Modulo,
            BigIntOp::Power,
        ]
    }
}

/// Target type for TypeCast node
#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum CastTarget {
    String,
    Float,
    Bool,
    BigInt,
}

impl CastTarget {
    pub fn display_name(&self) -> &str {
        match self {
            CastTarget::String => "String",
            CastTarget::Float => "Number",
            CastTarget::Bool => "Boolean",
            CastTarget::BigInt => "Big Integer",
        }
    }

    pub fn all() -> Vec<CastTarget> {
        vec![
            CastTarget::String,
            CastTarget::Float,
            CastTarget::Bool,
            CastTarget::BigInt,
        ]
    }

    pub fn to_pin_type(&self) -> PinType {
        match self {
            CastTarget::String => PinType::String,
            CastTarget::Float => PinType::Float,
            CastTarget::Bool => PinType::Bool,
            CastTarget::BigInt => PinType::BigInt,
        }
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum NodeParams {
    None,
    ComponentName(String),          // For Get/Set nodes
    StringValue(String),            // For string literals
    FloatValue(f32),                // For number literals
    BoolValue(bool),                // For bool literals
    ColorValue([f32; 4]),           // For color literals
    MathOperation(MathOp),          // For math nodes
    BigIntOperation(BigIntOp),      // For bigint math nodes
    FormatString(String),           // For string format nodes
    HttpHeaders(String),            // For HTTP nodes - JSON string of headers
    JsonPath(String),               // For Parse JSON node - JSON path like "data.user.name"
    VariableName(String),           // For Get/Set Variable nodes
    // SQL params
    TableName(String),              // For SQL CRUD nodes
    ColumnName(String),             // For GetColumn, OrderBy nodes
    SqlQuery(String),               // For Raw SQL node
    OrderDirection(OrderDirection), // For OrderBy node
    CompareOp(CompareOp),           // For Where nodes
    FileLocation(FileLocation),     // For CreateFile node
    TypeCast(CastTarget, CastTarget), // For TypeCast node - input type, output type
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum OrderDirection {
    Ascending,
    Descending,
}

impl OrderDirection {
    pub fn display_name(&self) -> &str {
        match self {
            OrderDirection::Ascending => "ASC",
            OrderDirection::Descending => "DESC",
        }
    }
    
    pub fn all() -> Vec<OrderDirection> {
        vec![OrderDirection::Ascending, OrderDirection::Descending]
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum CompareOp {
    Equals,
    NotEquals,
    GreaterThan,
    LessThan,
    GreaterOrEqual,
    LessOrEqual,
    Like,
    NotLike,
    IsNull,
    IsNotNull,
}

impl CompareOp {
    pub fn symbol(&self) -> &str {
        match self {
            CompareOp::Equals => "=",
            CompareOp::NotEquals => "!=",
            CompareOp::GreaterThan => ">",
            CompareOp::LessThan => "<",
            CompareOp::GreaterOrEqual => ">=",
            CompareOp::LessOrEqual => "<=",
            CompareOp::Like => "LIKE",
            CompareOp::NotLike => "NOT LIKE",
            CompareOp::IsNull => "IS NULL",
            CompareOp::IsNotNull => "IS NOT NULL",
        }
    }
    
    pub fn all() -> Vec<CompareOp> {
        vec![
            CompareOp::Equals,
            CompareOp::NotEquals,
            CompareOp::GreaterThan,
            CompareOp::LessThan,
            CompareOp::GreaterOrEqual,
            CompareOp::LessOrEqual,
            CompareOp::Like,
            CompareOp::NotLike,
            CompareOp::IsNull,
            CompareOp::IsNotNull,
        ]
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum FileLocation {
    Desktop,
    Documents,
    Downloads,
    Home,
    Temp,
}

impl FileLocation {
    pub fn display_name(&self) -> &str {
        match self {
            FileLocation::Desktop => "Desktop",
            FileLocation::Documents => "Documents",
            FileLocation::Downloads => "Downloads",
            FileLocation::Home => "Home",
            FileLocation::Temp => "Temporary Directory",
        }
    }

    pub fn all() -> Vec<FileLocation> {
        vec![
            FileLocation::Desktop,
            FileLocation::Documents,
            FileLocation::Downloads,
            FileLocation::Home,
            FileLocation::Temp,
        ]
    }
}

/// Variable type for user-defined variables
#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum VariableType {
    String,
    Float,
    Bool,
    Color,
}

impl VariableType {
    pub fn display_name(&self) -> &str {
        match self {
            VariableType::String => "String",
            VariableType::Float => "Number",
            VariableType::Bool => "Boolean",
            VariableType::Color => "Color",
        }
    }

    pub fn to_pin_type(&self) -> PinType {
        match self {
            VariableType::String => PinType::String,
            VariableType::Float => PinType::Float,
            VariableType::Bool => PinType::Bool,
            VariableType::Color => PinType::Color,
        }
    }

    pub fn all() -> Vec<VariableType> {
        vec![
            VariableType::String,
            VariableType::Float,
            VariableType::Bool,
            VariableType::Color,
        ]
    }
}

/// Source of a variable - user-defined or auto-generated from database
#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize, Default)]
pub enum VariableSource {
    #[default]
    User,           // User-created variable
    Database,       // Auto-generated from database table
}

/// User-defined variable
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Variable {
    pub name: String,
    pub var_type: VariableType,
    pub is_const: bool,  // false = let (mutable), true = const (immutable)
    pub default_value: VariableValue,
    #[serde(default)]
    pub source: VariableSource,  // Where this variable came from
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum VariableValue {
    String(String),
    Float(f32),
    Bool(bool),
    Color([f32; 4]),
}

impl Variable {
    pub fn new(name: String, var_type: VariableType, is_const: bool) -> Self {
        let default_value = match var_type {
            VariableType::String => VariableValue::String(String::new()),
            VariableType::Float => VariableValue::Float(0.0),
            VariableType::Bool => VariableValue::Bool(false),
            VariableType::Color => VariableValue::Color([1.0, 1.0, 1.0, 1.0]),
        };
        Self {
            name,
            var_type,
            is_const,
            default_value,
            source: VariableSource::User,
        }
    }

    /// Create a database-sourced variable (const string)
    pub fn database_var(name: String, value: String) -> Self {
        Self {
            name,
            var_type: VariableType::String,
            is_const: true,
            default_value: VariableValue::String(value),
            source: VariableSource::Database,
        }
    }

    /// Check if this is a database-generated variable
    pub fn is_database_var(&self) -> bool {
        self.source == VariableSource::Database
    }
}

#[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
#[serde(transparent)]
pub struct NodeType(String);

impl NodeType {
    pub fn new(id: impl Into<String>) -> Self {
        Self(id.into())
    }

    pub fn as_str(&self) -> &str {
        &self.0
    }

    pub fn display_name(&self) -> &str {
        node_registry::registry()
            .get_by_id(self.as_str())
            .map(|s| s.display_name)
            .unwrap_or_else(|| self.as_str())
    }

    pub fn documentation(&self) -> &str {
        node_registry::registry()
            .get_by_id(self.as_str())
            .map(|s| s.documentation)
            .unwrap_or("Unregistered node (missing NodeSpec)")
    }

    pub fn category(&self) -> NodeCategory {
        node_registry::registry()
            .get_by_id(self.as_str())
            .map(|s| s.category)
            .unwrap_or(NodeCategory::Action)
    }

    pub fn library(&self) -> NodeLibrary {
        node_registry::registry()
            .get_by_id(self.as_str())
            .map(|s| s.library)
            .unwrap_or(NodeLibrary::Core)
    }

    pub fn color(&self) -> [f32; 4] {
        self.category().color()
    }

    pub fn create_node(&self, id: usize, pin_id_start: usize) -> (Node, usize) {
        let mut next_pin_id = pin_id_start;

        if let Some(spec) = node_registry::registry().get_by_id(self.as_str()) {
            let params = (spec.default_params)();
            let pins = (spec.pins)(&mut next_pin_id);
            return (
                Node {
                    id,
                    node_type: self.clone(),
                    position: [0.0, 0.0],
                    pins,
                    params,
                },
                next_pin_id,
            );
        }

        // Unknown/unregistered node: create a placeholder node with minimal pins.
        let mut pins = Vec::new();
        pins.push(Pin::new(next_pin_id, PinType::Exec, true, ""));
        next_pin_id += 1;
        pins.push(Pin::new(next_pin_id, PinType::Exec, false, ""));
        next_pin_id += 1;

        (
            Node {
                id,
                node_type: self.clone(),
                position: [0.0, 0.0],
                pins,
                params: NodeParams::None,
            },
            next_pin_id,
        )
    }
}

impl From<&str> for NodeType {
    fn from(value: &str) -> Self {
        Self::new(value)
    }
}

impl From<String> for NodeType {
    fn from(value: String) -> Self {
        Self::new(value)
    }
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum NodeCategory {
    Entry,
    Action,
    ControlFlow,
    Data,
    Network,
    Database,
    FileSystem,
}

impl NodeCategory {
    pub fn color(&self) -> [f32; 4] {
        match self {
            NodeCategory::Entry => [0.6, 0.3, 0.8, 1.0],       // Purple
            NodeCategory::Action => [0.3, 0.6, 0.9, 1.0],      // Blue
            NodeCategory::ControlFlow => [0.9, 0.6, 0.3, 1.0], // Orange
            NodeCategory::Data => [0.3, 0.8, 0.5, 1.0],        // Green
            NodeCategory::Network => [0.2, 0.7, 0.8, 1.0],     // Teal
            NodeCategory::Database => [0.9, 0.5, 0.2, 1.0],    // Dark Orange
            NodeCategory::FileSystem => [0.8, 0.7, 0.2, 1.0],  // Gold/Yellow
        }
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Node {
    pub id: usize,
    pub node_type: NodeType,
    pub position: [f32; 2],
    pub pins: Vec<Pin>,
    pub params: NodeParams,
}

impl Node {
    pub fn input_pins(&self) -> Vec<&Pin> {
        self.pins.iter().filter(|p| p.is_input).collect()
    }

    pub fn output_pins(&self) -> Vec<&Pin> {
        self.pins.iter().filter(|p| !p.is_input).collect()
    }

    pub fn input_pins_mut(&mut self) -> Vec<&mut Pin> {
        self.pins.iter_mut().filter(|p| p.is_input).collect()
    }

    pub fn output_pins_mut(&mut self) -> Vec<&mut Pin> {
        self.pins.iter_mut().filter(|p| !p.is_input).collect()
    }

    pub fn get_pin(&self, pin_id: usize) -> Option<&Pin> {
        self.pins.iter().find(|p| p.id == pin_id)
    }

    pub fn get_pin_mut(&mut self, pin_id: usize) -> Option<&mut Pin> {
        self.pins.iter_mut().find(|p| p.id == pin_id)
    }

    pub fn get_component_name(&self) -> Option<String> {
        if let NodeParams::ComponentName(name) = &self.params {
            Some(name.clone())
        } else {
            None
        }
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Connection {
    pub from_node: usize,
    pub from_pin: usize,
    pub to_node: usize,
    pub to_pin: usize,
}

impl Connection {
    pub fn new(from_node: usize, from_pin: usize, to_node: usize, to_pin: usize) -> Self {
        Self {
            from_node,
            from_pin,
            to_node,
            to_pin,
        }
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Comment {
    pub id: usize,
    pub text: String,
    pub position: [f32; 2],
    pub size: [f32; 2],
    pub color: [f32; 4], // RGBA
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct NodeGraph {
    pub nodes: Vec<Node>,
    pub connections: Vec<Connection>,
    pub comments: Vec<Comment>,
    pub next_node_id: usize,
    pub next_pin_id: usize,
    pub next_comment_id: usize,
}

impl NodeGraph {
    pub fn new() -> Self {
        Self {
            nodes: Vec::new(),
            connections: Vec::new(),
            comments: Vec::new(),
            next_node_id: 0,
            next_pin_id: 0,
            next_comment_id: 0,
        }
    }

    pub fn add_node(&mut self, node_type: NodeType, position: [f32; 2]) -> usize {
        let (mut node, next_pin_id) = node_type.create_node(self.next_node_id, self.next_pin_id);
        node.position = position;
        let node_id = node.id;
        self.nodes.push(node);
        self.next_node_id += 1;
        self.next_pin_id = next_pin_id;
        node_id
    }

    pub fn remove_node(&mut self, node_id: usize) {
        // Remove connections involving this node
        self.connections
            .retain(|conn| conn.from_node != node_id && conn.to_node != node_id);
        // Remove the node
        self.nodes.retain(|node| node.id != node_id);
    }

    pub fn add_connection(&mut self, from_node: usize, from_pin: usize, to_node: usize, to_pin: usize) -> bool {
        // Validate connection
        let from_node_obj = self.nodes.iter().find(|n| n.id == from_node);
        let to_node_obj = self.nodes.iter().find(|n| n.id == to_node);

        if let (Some(from_node), Some(to_node)) = (from_node_obj, to_node_obj) {
            let from_pin_obj = from_node.get_pin(from_pin);
            let to_pin_obj = to_node.get_pin(to_pin);

            if let (Some(from_pin), Some(to_pin)) = (from_pin_obj, to_pin_obj) {
                // Check: from_pin must be output, to_pin must be input
                if !from_pin.is_input && to_pin.is_input {
                    // Check: pin types must match
                    if from_pin.pin_type.is_compatible(&to_pin.pin_type) {
                        // Remove existing connection to the same input pin
                        self.connections
                            .retain(|conn| !(conn.to_node == to_node.id && conn.to_pin == to_pin.id));

                        // Add new connection
                        self.connections.push(Connection::new(from_node.id, from_pin.id, to_node.id, to_pin.id));
                        return true;
                    }
                }
            }
        }

        false
    }

    pub fn remove_connection(&mut self, connection_index: usize) {
        if connection_index < self.connections.len() {
            self.connections.remove(connection_index);
        }
    }

    pub fn get_node(&self, node_id: usize) -> Option<&Node> {
        self.nodes.iter().find(|n| n.id == node_id)
    }

    pub fn get_node_mut(&mut self, node_id: usize) -> Option<&mut Node> {
        self.nodes.iter_mut().find(|n| n.id == node_id)
    }

    pub fn get_connection(&self, from_pin: usize) -> Option<&Connection> {
        self.connections.iter().find(|c| c.from_pin == from_pin)
    }

    pub fn get_connections_to_pin(&self, to_pin: usize) -> Vec<&Connection> {
        self.connections.iter().filter(|c| c.to_pin == to_pin).collect()
    }

    /// Check if the graph has meaningful content (more than just EventStart with no connections)
    pub fn has_executable_content(&self) -> bool {
        // If there are no nodes, no content
        if self.nodes.is_empty() {
            return false;
        }
        
        // If there's only one node and it's EventStart with no outgoing connections, no meaningful content
        if self.nodes.len() == 1 {
            if let Some(node) = self.nodes.first() {
                if node.node_type.as_str() == "EventStart" {
                    // Check if EventStart has any outgoing exec connections
                    let has_outgoing = self.connections.iter().any(|c| c.from_node == node.id);
                    return has_outgoing;
                }
            }
        }
        
        // Has multiple nodes or non-EventStart nodes
        true
    }

    pub fn add_comment_around_nodes(&mut self, node_ids: &[usize]) -> Option<usize> {
        if node_ids.is_empty() {
            return None;
        }

        // Calculate bounding box of selected nodes
        let mut min_x = f32::MAX;
        let mut min_y = f32::MAX;
        let mut max_x = f32::MIN;
        let mut max_y = f32::MIN;

        for node_id in node_ids {
            if let Some(node) = self.get_node(*node_id) {
                min_x = min_x.min(node.position[0]);
                min_y = min_y.min(node.position[1]);
                max_x = max_x.max(node.position[0] + 180.0); // node width
                max_y = max_y.max(node.position[1] + 100.0); // approximate node height
            }
        }

        // Add padding
        let padding = 20.0;
        let position = [min_x - padding, min_y - padding - 30.0]; // Extra space for comment text
        let size = [max_x - min_x + padding * 2.0, max_y - min_y + padding * 2.0 + 30.0];

        let comment = Comment {
            id: self.next_comment_id,
            text: "Comment".to_string(),
            position,
            size,
            color: [0.2, 0.3, 0.4, 0.5], // Semi-transparent blue
        };

        let comment_id = self.next_comment_id;
        self.next_comment_id += 1;
        self.comments.push(comment);
        Some(comment_id)
    }
}

impl Default for NodeGraph {
    fn default() -> Self {
        Self::new()
    }
}

// ==================== Database Schema ====================

/// SQL data types supported
#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum SqlDataType {
    Integer,
    Real,
    Text,
    Blob,
    Boolean,    // Stored as INTEGER 0/1
    DateTime,   // Stored as TEXT ISO8601
}

impl SqlDataType {
    pub fn display_name(&self) -> &str {
        match self {
            SqlDataType::Integer => "Integer",
            SqlDataType::Real => "Real (Float)",
            SqlDataType::Text => "Text",
            SqlDataType::Blob => "Blob (Binary)",
            SqlDataType::Boolean => "Boolean",
            SqlDataType::DateTime => "DateTime",
        }
    }

    pub fn sql_type(&self) -> &str {
        match self {
            SqlDataType::Integer => "INTEGER",
            SqlDataType::Real => "REAL",
            SqlDataType::Text => "TEXT",
            SqlDataType::Blob => "BLOB",
            SqlDataType::Boolean => "INTEGER",  // SQLite stores as 0/1
            SqlDataType::DateTime => "TEXT",    // ISO8601 format
        }
    }

    pub fn all() -> Vec<SqlDataType> {
        vec![
            SqlDataType::Integer,
            SqlDataType::Real,
            SqlDataType::Text,
            SqlDataType::Blob,
            SqlDataType::Boolean,
            SqlDataType::DateTime,
        ]
    }

    pub fn default_value(&self) -> String {
        match self {
            SqlDataType::Integer => "0".to_string(),
            SqlDataType::Real => "0.0".to_string(),
            SqlDataType::Text => "".to_string(),
            SqlDataType::Blob => "".to_string(),
            SqlDataType::Boolean => "false".to_string(),
            SqlDataType::DateTime => "".to_string(),
        }
    }
}

/// Foreign key reference
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ForeignKey {
    pub table: String,
    pub column: String,
    pub on_delete: ForeignKeyAction,
    pub on_update: ForeignKeyAction,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub enum ForeignKeyAction {
    NoAction,
    Restrict,
    SetNull,
    SetDefault,
    Cascade,
}

impl ForeignKeyAction {
    pub fn sql(&self) -> &str {
        match self {
            ForeignKeyAction::NoAction => "NO ACTION",
            ForeignKeyAction::Restrict => "RESTRICT",
            ForeignKeyAction::SetNull => "SET NULL",
            ForeignKeyAction::SetDefault => "SET DEFAULT",
            ForeignKeyAction::Cascade => "CASCADE",
        }
    }

    pub fn all() -> Vec<ForeignKeyAction> {
        vec![
            ForeignKeyAction::NoAction,
            ForeignKeyAction::Restrict,
            ForeignKeyAction::SetNull,
            ForeignKeyAction::SetDefault,
            ForeignKeyAction::Cascade,
        ]
    }
}

/// Column definition for a database table
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ColumnDefinition {
    pub name: String,
    pub data_type: SqlDataType,
    pub primary_key: bool,
    pub auto_increment: bool,
    pub not_null: bool,
    pub unique: bool,
    pub default_value: Option<String>,
    pub foreign_key: Option<ForeignKey>,
}

impl ColumnDefinition {
    pub fn new(name: &str, data_type: SqlDataType) -> Self {
        Self {
            name: name.to_string(),
            data_type,
            primary_key: false,
            auto_increment: false,
            not_null: false,
            unique: false,
            default_value: None,
            foreign_key: None,
        }
    }

    pub fn primary_key(mut self) -> Self {
        self.primary_key = true;
        self.not_null = true;
        self
    }

    pub fn auto_increment(mut self) -> Self {
        self.auto_increment = true;
        self
    }

    pub fn not_null(mut self) -> Self {
        self.not_null = true;
        self
    }

    pub fn unique(mut self) -> Self {
        self.unique = true;
        self
    }

    pub fn default(mut self, value: &str) -> Self {
        self.default_value = Some(value.to_string());
        self
    }

    /// Generate SQL for this column definition
    pub fn to_sql(&self) -> String {
        let mut sql = format!("{} {}", self.name, self.data_type.sql_type());
        
        if self.primary_key {
            sql.push_str(" PRIMARY KEY");
            if self.auto_increment {
                sql.push_str(" AUTOINCREMENT");
            }
        }
        
        if self.not_null && !self.primary_key {
            sql.push_str(" NOT NULL");
        }
        
        if self.unique && !self.primary_key {
            sql.push_str(" UNIQUE");
        }
        
        if let Some(ref default) = self.default_value {
            sql.push_str(&format!(" DEFAULT {}", default));
        }
        
        sql
    }
}

/// Seed data row (values for each column)
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SeedRow {
    pub values: Vec<String>,  // Values in column order
}

/// Table definition for the database schema
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct TableDefinition {
    pub id: usize,
    pub name: String,
    pub columns: Vec<ColumnDefinition>,
    pub seed_data: Vec<SeedRow>,
}

impl TableDefinition {
    pub fn new(id: usize, name: &str) -> Self {
        Self {
            id,
            name: name.to_string(),
            columns: vec![
                ColumnDefinition::new("id", SqlDataType::Integer)
                    .primary_key()
                    .auto_increment(),
            ],
            seed_data: Vec::new(),
        }
    }

    pub fn add_column(&mut self, column: ColumnDefinition) {
        self.columns.push(column);
    }

    pub fn add_seed_row(&mut self, values: Vec<String>) {
        self.seed_data.push(SeedRow { values });
    }

    /// Generate CREATE TABLE SQL
    pub fn to_create_sql(&self) -> String {
        let columns: Vec<String> = self.columns.iter()
            .map(|c| c.to_sql())
            .collect();
        
        let mut sql = format!("CREATE TABLE IF NOT EXISTS {} (\n    {}\n)", 
            self.name, 
            columns.join(",\n    "));
        
        // Add foreign key constraints
        for col in &self.columns {
            if let Some(ref fk) = col.foreign_key {
                sql.push_str(&format!(
                    ",\n    FOREIGN KEY ({}) REFERENCES {}({}) ON DELETE {} ON UPDATE {}",
                    col.name, fk.table, fk.column, fk.on_delete.sql(), fk.on_update.sql()
                ));
            }
        }
        
        sql
    }

    /// Generate INSERT statements for seed data
    pub fn to_seed_sql(&self) -> Vec<String> {
        self.seed_data.iter().map(|row| {
            let column_names: Vec<&str> = self.columns.iter()
                .filter(|c| !c.auto_increment)  // Skip auto-increment columns
                .map(|c| c.name.as_str())
                .collect();
            
            let values: Vec<String> = row.values.iter()
                .enumerate()
                .filter(|(i, _)| !self.columns.get(*i).map(|c| c.auto_increment).unwrap_or(false))
                .map(|(_, v)| {
                    // Quote string values
                    if v.parse::<f64>().is_err() && v != "NULL" && v != "true" && v != "false" {
                        format!("'{}'", v.replace("'", "''"))
                    } else if v == "true" {
                        "1".to_string()
                    } else if v == "false" {
                        "0".to_string()
                    } else {
                        v.clone()
                    }
                })
                .collect();
            
            format!("INSERT INTO {} ({}) VALUES ({})",
                self.name,
                column_names.join(", "),
                values.join(", "))
        }).collect()
    }
}

/// Database settings for the project
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DatabaseSettings {
    pub enabled: bool,
    pub default_path: String,           // Default DB path (can be network path)
    pub allow_runtime_override: bool,   // Allow --db-path CLI argument
    pub auto_create: bool,              // Create DB and tables if not exists
    pub run_seed_on_empty: bool,        // Insert seed data if tables are empty
}

impl Default for DatabaseSettings {
    fn default() -> Self {
        Self {
            enabled: false,
            default_path: "./data/app.db".to_string(),
            allow_runtime_override: true,
            auto_create: true,
            run_seed_on_empty: true,
        }
    }
}

/// Complete database schema for the project
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DatabaseSchema {
    pub settings: DatabaseSettings,
    pub tables: Vec<TableDefinition>,
    pub next_table_id: usize,
}

impl Default for DatabaseSchema {
    fn default() -> Self {
        Self {
            settings: DatabaseSettings::default(),
            tables: Vec::new(),
            next_table_id: 0,
        }
    }
}

impl DatabaseSchema {
    pub fn new() -> Self {
        Self::default()
    }

    pub fn add_table(&mut self, name: &str) -> usize {
        let id = self.next_table_id;
        self.next_table_id += 1;
        self.tables.push(TableDefinition::new(id, name));
        id
    }

    pub fn get_table(&self, id: usize) -> Option<&TableDefinition> {
        self.tables.iter().find(|t| t.id == id)
    }

    pub fn get_table_mut(&mut self, id: usize) -> Option<&mut TableDefinition> {
        self.tables.iter_mut().find(|t| t.id == id)
    }

    pub fn get_table_by_name(&self, name: &str) -> Option<&TableDefinition> {
        self.tables.iter().find(|t| t.name == name)
    }

    pub fn remove_table(&mut self, id: usize) {
        self.tables.retain(|t| t.id != id);
    }

    pub fn table_names(&self) -> Vec<&str> {
        self.tables.iter().map(|t| t.name.as_str()).collect()
    }

    /// Generate all CREATE TABLE statements
    pub fn to_schema_sql(&self) -> String {
        self.tables.iter()
            .map(|t| t.to_create_sql())
            .collect::<Vec<_>>()
            .join(";\n\n")
    }

    /// Generate all seed data INSERT statements
    pub fn to_seed_sql(&self) -> Vec<String> {
        self.tables.iter()
            .flat_map(|t| t.to_seed_sql())
            .collect()
    }

    /// Generate variables for all database tables
    /// Creates variables like: db_users_table, db_users_columns, db_users_pk, etc.
    pub fn generate_variables(&self) -> Vec<Variable> {
        if !self.settings.enabled {
            return Vec::new();
        }

        let mut vars = Vec::new();

        // Database path variable
        vars.push(Variable::database_var(
            "db_path".to_string(),
            self.settings.default_path.clone(),
        ));

        // Per-table variables
        for table in &self.tables {
            let prefix = format!("db_{}", table.name);

            // Table name
            vars.push(Variable::database_var(
                format!("{}_table", prefix),
                table.name.clone(),
            ));

            // Column names as comma-separated list
            let column_names: Vec<&str> = table.columns.iter()
                .map(|c| c.name.as_str())
                .collect();
            vars.push(Variable::database_var(
                format!("{}_columns", prefix),
                column_names.join(", "),
            ));

            // Primary key column(s)
            let pk_columns: Vec<&str> = table.columns.iter()
                .filter(|c| c.primary_key)
                .map(|c| c.name.as_str())
                .collect();
            if !pk_columns.is_empty() {
                vars.push(Variable::database_var(
                    format!("{}_pk", prefix),
                    pk_columns.join(", "),
                ));
            }

            // SELECT * query
            vars.push(Variable::database_var(
                format!("{}_select_all", prefix),
                format!("SELECT * FROM {}", table.name),
            ));

            // INSERT template (with placeholders)
            let placeholders: Vec<&str> = table.columns.iter()
                .filter(|c| !c.auto_increment)
                .map(|_| "?")
                .collect();
            let insert_cols: Vec<&str> = table.columns.iter()
                .filter(|c| !c.auto_increment)
                .map(|c| c.name.as_str())
                .collect();
            if !insert_cols.is_empty() {
                vars.push(Variable::database_var(
                    format!("{}_insert", prefix),
                    format!("INSERT INTO {} ({}) VALUES ({})", 
                        table.name, 
                        insert_cols.join(", "),
                        placeholders.join(", ")
                    ),
                ));
            }

            // Individual column names for easy access
            for col in &table.columns {
                vars.push(Variable::database_var(
                    format!("{}_col_{}", prefix, col.name),
                    col.name.clone(),
                ));
            }
        }

        vars
    }
}
