use rusqlite::{params, Connection, Result};
use serde::Serialize;
use std::collections::{HashSet, HashMap};
use std::path::{Path, PathBuf};

use crate::frontparse::{parse_frontmatter, Frontmatter};

#[derive(Debug, Serialize)]
pub struct Note {
    pub filename: String,
    pub title: String,
    pub summary: Option<String>,
    pub date: Option<String>,
    pub time: Option<String>,
    pub tags: Option<Vec<String>>,
    pub note_type: Option<String>,
    pub uplink: Option<String>,
    pub content: String,
}

fn db_path(notebook_path: &Path) -> PathBuf {
    notebook_path.join(".smarana").join("index.db")
}

/// Initializes the SQLite database and its schema.
pub fn init_db(notebook_path: &Path) -> Result<()> {
    let path = db_path(notebook_path);
    let conn = Connection::open(&path)?;

    conn.execute(
        "CREATE TABLE IF NOT EXISTS notes (
            filename TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            summary TEXT,
            date TEXT,
            time TEXT,
            tags TEXT,
            type TEXT DEFAULT 'fleeting',
            uplink TEXT,
            content TEXT NOT NULL DEFAULT ''
        )",
        [],
    )?;

    Ok(())
}

/// Drops and recreates the database schema.
pub fn init_db_force(notebook_path: &Path) -> Result<()> {
    let path = db_path(notebook_path);
    let conn = Connection::open(&path)?;
    conn.execute("DROP TABLE IF EXISTS notes", [])?;
    init_db(notebook_path)
}

/// Upserts a note using extracted frontmatter variables and full content
pub fn upsert_note(notebook_path: &Path, filename: &str, fm: &Frontmatter, content: &str) -> Result<()> {
    let path = db_path(notebook_path);
    let conn = Connection::open(&path)?;

    let title = fm.title.as_deref().unwrap_or("null");
    
    // Store tags as JSON array string
    let tags_json = if let Some(tags) = &fm.tags {
        serde_json::to_string(tags).ok()
    } else {
        None
    };

    let note_type = fm.note_type.as_deref().unwrap_or("fleeting");
    let uplink_str = fm.uplink.clone();

    conn.execute(
        "INSERT INTO notes (filename, title, summary, date, time, tags, type, uplink, content) 
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
         ON CONFLICT(filename) DO UPDATE SET
         title=excluded.title,
         summary=excluded.summary,
         date=excluded.date,
         time=excluded.time,
         tags=excluded.tags,
         type=excluded.type,
         uplink=excluded.uplink,
         content=excluded.content",
        params![
            filename,
            title,
            fm.summary,
            fm.date,
            fm.time,
            tags_json,
            note_type,
            uplink_str,
            content
        ],
    )?;

    Ok(())
}



fn make_slug(title: &str) -> String {
    let re = regex::Regex::new(r"[^a-zA-Z0-9]+").unwrap();
    let slug = re.replace_all(title, "-").trim_matches('-').to_string();
    if slug.is_empty() { "untitled".to_string() } else { slug }
}

fn topological_sort_atomic(notes: &[(String, String, Option<String>, Option<String>, Option<String>)]) -> Vec<String> {
    // Map of slug -> filename to resolve up-links that are Typst labels (@Slug)
    let mut slug_to_filename: HashMap<String, String> = HashMap::new();
    let mut filename_to_note: HashMap<String, &(String, String, Option<String>, Option<String>, Option<String>)> = HashMap::new();
    
    for note in notes {
        slug_to_filename.insert(make_slug(&note.1), note.0.clone());
        filename_to_note.insert(note.0.clone(), note);
    }
    
    // Adjacency: uplink targets -> dependents
    let mut dependents: HashMap<String, Vec<String>> = HashMap::new();
    let mut has_parent: HashSet<String> = HashSet::new();
    
    for note in notes {
        if let Some(uplink_slug) = &note.4 {
            if let Some(parent_filename) = slug_to_filename.get(uplink_slug) {
                dependents.entry(parent_filename.clone()).or_default().push(note.0.clone());
                has_parent.insert(note.0.clone());
            }
        }
    }
    
    // Sort comparator: Recency (DESC = newest first)
    let cmp_date_desc = |a: &String, b: &String| {
        let na = filename_to_note.get(a).unwrap();
        let nb = filename_to_note.get(b).unwrap();
        let da = (na.2.as_deref().unwrap_or(""), na.3.as_deref().unwrap_or(""));
        let db = (nb.2.as_deref().unwrap_or(""), nb.3.as_deref().unwrap_or(""));
        db.cmp(&da) // descending
    };
    
    let mut roots: Vec<String> = notes.iter()
        .filter(|note| !has_parent.contains(&note.0))
        .map(|note| note.0.clone())
        .collect();
        
    roots.sort_by(&cmp_date_desc);
    
    let mut result = Vec::new();
    let mut visited = HashSet::new();
    let mut visiting = HashSet::new();
    
    fn dfs(
        node: String, 
        dependents: &HashMap<String, Vec<String>>, 
        visited: &mut HashSet<String>, 
        visiting: &mut HashSet<String>, 
        result: &mut Vec<String>,
        cmp: &impl Fn(&String, &String) -> std::cmp::Ordering
    ) {
        if visiting.contains(&node) || visited.contains(&node) {
            return;
        }
        visiting.insert(node.clone());
        result.push(node.clone());
        
        if let Some(mut children) = dependents.get(&node).cloned() {
            children.sort_by(cmp);
            for child in children {
                dfs(child, dependents, visited, visiting, result, cmp);
            }
        }
        
        visiting.remove(&node);
        visited.insert(node);
    }
    
    for root in roots {
        dfs(root, &dependents, &mut visited, &mut visiting, &mut result, &cmp_date_desc);
    }
    
    // Catch any detached cycle components
    let mut all_nodes: Vec<String> = notes.iter().map(|n| n.0.clone()).collect();
    all_nodes.sort_by(&cmp_date_desc);
    
    for node in all_nodes {
        if !visited.contains(&node) {
            dfs(node, &dependents, &mut visited, &mut visiting, &mut result, &cmp_date_desc);
        }
    }
    
    result
}

/// Generates `smarana.typ` at the notebook root with ordered `#include` directives
/// organized by compartment: fleeting → capture → atomic → appendix (tag index)
pub fn export_smarana_typ(notebook_path: &Path) {
    let path = db_path(notebook_path);
    let conn = match Connection::open(&path) {
        Ok(c) => c,
        Err(e) => {
            eprintln!("Failed to open database for smarana.typ export: {}", e);
            return;
        }
    };

    let mut content = String::from("// Auto-generated by smarana — do not edit manually\n\n#import \".smarana/user.typ\": conf, section-divider, tag-index-page\n#show: conf\n\n");

    // ── FLEETING Section ──
    content.push_str("// ═══ Fleeting Notes ═══\n");
    content.push_str("#section-divider(\"FLEETING\", \"fleeting\")\n\n");
    {
        let mut stmt = conn
            .prepare("SELECT filename FROM notes WHERE type = 'fleeting' ORDER BY date DESC, time DESC")
            .unwrap();
        let filenames: Vec<String> = stmt
            .query_map([], |row| row.get(0))
            .into_iter()
            .flatten()
            .filter_map(|r| r.ok())
            .collect();
        for f in &filenames {
            content.push_str(&format!("#include \"{}\"\n", f));
        }
    }

    content.push('\n');

    // ── CAPTURE Section ──
    content.push_str("// ═══ Capture Notes ═══\n");
    content.push_str("#section-divider(\"CAPTURE\", \"capture\")\n\n");
    {
        let mut stmt = conn
            .prepare("SELECT filename FROM notes WHERE type = 'capture' ORDER BY date DESC, time DESC")
            .unwrap();
        let filenames: Vec<String> = stmt
            .query_map([], |row| row.get(0))
            .into_iter()
            .flatten()
            .filter_map(|r| r.ok())
            .collect();
        for f in &filenames {
            content.push_str(&format!("#include \"{}\"\n", f));
        }
    }

    content.push('\n');

    // ── ATOMIC Section ──
    content.push_str("// ═══ Atomic Notes ═══\n");
    content.push_str("#section-divider(\"ATOMIC\", \"atomic\")\n\n");
    {
        let mut stmt = conn
            .prepare("SELECT filename, title, date, time, uplink FROM notes WHERE type = 'atomic' ORDER BY date ASC, time ASC")
            .unwrap();
        let atomic_notes: Vec<(String, String, Option<String>, Option<String>, Option<String>)> = stmt
            .query_map([], |row| {
                Ok((
                    row.get(0)?,
                    row.get(1)?,
                    row.get(2)?,
                    row.get(3)?,
                    row.get(4)?
                ))
            })
            .into_iter()
            .flatten()
            .filter_map(|r| r.ok())
            .collect();

        let sorted = topological_sort_atomic(&atomic_notes);
        for f in &sorted {
            content.push_str(&format!("#include \"{}\"\n", f));
        }
    }

    content.push('\n');

    // ── APPENDIX Section — Tag Index Pages ──
    content.push_str("// ═══ Appendix — Tag Index ═══\n");
    content.push_str("#section-divider(\"APPENDIX\", \"appendix\")\n\n");
    {
        // Collect all tags and their associated notes (title + slug)
        let mut stmt = conn
            .prepare("SELECT title, tags FROM notes WHERE tags IS NOT NULL AND tags != '[]' AND tags != '' ORDER BY title ASC")
            .unwrap();
        
        let mut tag_map: std::collections::BTreeMap<String, Vec<(String, String)>> = std::collections::BTreeMap::new();
        
        let rows: Vec<(String, String)> = stmt
            .query_map([], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, String>(1)?,
                ))
            })
            .into_iter()
            .flatten()
            .filter_map(|r| r.ok())
            .collect();
        
        for (title, tags_json) in &rows {
            if let Ok(tags) = serde_json::from_str::<Vec<String>>(tags_json) {
                let slug = make_slug(title);
                for tag in tags {
                    tag_map.entry(tag).or_default().push((title.clone(), slug.clone()));
                }
            }
        }
        
        // Generate a tag-index-page call for each unique tag
        for (tag, notes) in &tag_map {
            content.push_str(&format!("#tag-index-page(\"{}\", (\n", escape_typst_string(tag)));
            for (title, slug) in notes {
                content.push_str(&format!("  (\"{}\", \"{}\"),\n", escape_typst_string(title), slug));
            }
            content.push_str("))\n\n");
        }
    }

    let smarana_path = notebook_path.join("smarana.typ");
    if let Err(e) = std::fs::write(&smarana_path, &content) {
        eprintln!("Failed to write smarana.typ: {}", e);
    }
}

/// Escape special characters for Typst string literals
fn escape_typst_string(s: &str) -> String {
    s.replace('\\', "\\\\").replace('"', "\\\"")
}


/// Scans exclusively via `.typ` in the notebook root.
pub fn sync(notebook_path: &Path) {
    // Ensure we have the latest schema for a full sync
    if let Err(e) = init_db_force(notebook_path) {
        eprintln!("Failed to initialize database schema: {}", e);
        return;
    }

    if let Ok(entries) = std::fs::read_dir(notebook_path) {
        for entry in entries.filter_map(|e| e.ok()) {
            let path = entry.path();
            if path.is_file() {
                if let Some(ext) = path.extension() {
                    if ext == "typ" {
                        // Skip smarana.typ (the main document)
                        if let Some(name) = path.file_name() {
                            if name == "smarana.typ" {
                                continue;
                            }
                        }
                        // Ignore internal configurations beneath .smarana
                        if path.to_string_lossy().contains(".smarana") {
                            continue;
                        }
                        // Use internal sync without per-file export
                        if let Ok(content) = std::fs::read_to_string(&path) {
                            let rel_path = path.strip_prefix(notebook_path).unwrap_or(&path).to_string_lossy().to_string();
                            let normalized_path = rel_path.replace("\\", "/");
                            let fm = parse_frontmatter(&content, &normalized_path);
                            if let Err(e) = upsert_note(notebook_path, &normalized_path, &fm, &content) {
                                eprintln!("Failed to register note '{}': {}", normalized_path, e);
                            }
                        }
                    }
                }
            }
        }
    }

    // Export smarana.typ once after all notes are indexed
    export_smarana_typ(notebook_path);
}

/// Lists all notes structured explicitly for `-j` native JSON conversions!
pub fn list_notes(notebook_path: &Path) -> Result<Vec<Note>> {
    let path = db_path(notebook_path);
    let conn = Connection::open(&path)?;

    // We can order by date DESC
    let mut stmt = conn.prepare("SELECT filename, title, summary, date, time, tags, type, uplink, content FROM notes ORDER BY date DESC, time DESC")?;
    let note_iter = stmt.query_map([], |row| {
        let tags_str: Option<String> = row.get(5)?;
        let tags = if let Some(ts) = tags_str {
            serde_json::from_str(&ts).ok()
        } else {
            None
        };

        let uplink: Option<String> = row.get(7)?;

        Ok(Note {
            filename: row.get(0)?,
            title: row.get(1)?,
            summary: row.get(2)?,
            date: row.get(3)?,
            time: row.get(4)?,
            tags,
            note_type: row.get(6)?,
            uplink,
            content: row.get(8)?
        })
    })?;

    let mut notes = Vec::new();
    for note in note_iter {
        notes.push(note?);
    }

    Ok(notes)
}
