/*
 * This file is a part of RSPlayer
 * Licensed under the GPL-3.0 License
 * Copyright (C) 2026 Salmonik @ salmonik@tuta.io
 * https://codeberg.org/salmonik/RSPlayer
*/

use directories::ProjectDirs;
use rusqlite::{Connection, Result as SqlResult};
use std::fs;
use std::path::{Path, PathBuf};
use std::time::SystemTime;

#[derive(Debug, Clone)]
#[allow(dead_code)]
pub struct Artist {
    pub id: i64,
    pub name: String,
}

#[derive(Debug, Clone)]
#[allow(dead_code)]
pub struct Album {
    pub id: i64,
    pub artist_id: i64,
    pub title: String,
}

#[derive(Debug, Clone)]
#[allow(dead_code)]
pub struct Track {
    pub id: i64,
    pub album_id: i64,
    pub title: String,
    pub path: PathBuf,
    pub duration: Option<i64>,
    pub track_number: Option<i32>,
    pub artist_name: Option<String>,
    pub album_title: Option<String>,
}

pub struct Database {
    conn: Connection,
}

const LIMIT: usize = 50;

#[allow(dead_code)]
impl Database {
    pub fn conn(&self) -> &Connection {
        &self.conn
    }

    pub fn open(_config: &crate::config::Config) -> SqlResult<Self> {
        let proj_dirs = ProjectDirs::from("com", "rsplayer", "rsplayer").ok_or_else(|| {
            rusqlite::Error::InvalidPath("Failed to get project directories".into())
        })?;

        let data_dir = if let Ok(xdg) = std::env::var("XDG_DATA_HOME") {
            PathBuf::from(xdg).join("rsplayer")
        } else {
            proj_dirs.data_dir().to_path_buf()
        };

        fs::create_dir_all(&data_dir).ok();
        let db_path = data_dir.join("library.db");

        let debug_log = std::env::temp_dir().join("rsp-completion-debug.log");
        macro_rules! debug_log {
            ($($arg:tt)*) => {
                let _ = std::fs::OpenOptions::new()
                    .create(true)
                    .append(true)
                    .open(&debug_log)
                    .and_then(|mut f| {
                        use std::io::Write;
                        writeln!(f, $($arg)*)
                    });
            };
        }
        debug_log!("[Database::open] db_path: {:?}", db_path);
        debug_log!("[Database::open] db exists: {}", db_path.exists());

        let conn = match Connection::open(&db_path) {
            Ok(c) => c,
            Err(e) => {
                debug_log!("[Database::open] Connection::open failed: {e}");
                return Err(e);
            }
        };
        let db = Database { conn };
        db.init_schema()?;
        Ok(db)
    }

    fn init_schema(&self) -> SqlResult<()> {
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS artists (
                id INTEGER PRIMARY KEY,
                name TEXT UNIQUE NOT NULL
            )",
            [],
        )?;

        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS albums (
                id INTEGER PRIMARY KEY,
                artist_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                FOREIGN KEY (artist_id) REFERENCES artists(id),
                UNIQUE(artist_id, title)
            )",
            [],
        )?;

        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS tracks (
                id INTEGER PRIMARY KEY,
                album_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                path TEXT UNIQUE NOT NULL,
                duration INTEGER,
                track_number INTEGER,
                last_scanned INTEGER,
                FOREIGN KEY (album_id) REFERENCES albums(id)
            )",
            [],
        )?;

        Ok(())
    }

    pub fn scan_paths(&self, paths: &[PathBuf], force: bool) -> SqlResult<()> {
        for path in paths {
            self.scan_directory(path, force)?;
        }
        Ok(())
    }

    fn scan_directory(&self, dir: &Path, force: bool) -> SqlResult<()> {
        if !dir.exists() {
            return Ok(());
        }

        let now = SystemTime::now()
            .duration_since(SystemTime::UNIX_EPOCH)
            .unwrap()
            .as_secs() as i64;

        let mut count = 0u64;
        self.scan_recursive(dir, now, force, &mut count)?;
        eprintln!("Scanned {} tracks in {:?}", count, dir);
        self.cleanup_old_tracks(now)?;
        Ok(())
    }

    fn scan_recursive(
        &self,
        dir: &Path,
        scan_time: i64,
        force: bool,
        count: &mut u64,
    ) -> SqlResult<()> {
        let entries = fs::read_dir(dir).ok();
        if let Some(entries) = entries {
            for entry in entries.flatten() {
                let path = entry.path();
                if path.is_dir() {
                    self.scan_recursive(&path, scan_time, force, count)?;
                } else if is_audio_file(&path) {
                    self.process_audio_file(&path, scan_time, force)?;
                    *count += 1;
                    eprint!("Found {} tracks...\r", count);
                }
            }
        }
        Ok(())
    }

    fn process_audio_file(&self, path: &Path, scan_time: i64, force: bool) -> SqlResult<()> {
        let path_str = path.to_string_lossy().to_string();

        if !force {
            let count: i64 = self
                .conn
                .query_row(
                    "SELECT COUNT(*) FROM tracks WHERE path = ? AND last_scanned = ?",
                    rusqlite::params![path_str, scan_time],
                    |row| row.get(0),
                )
                .unwrap_or(0);
            if count > 0 {
                return Ok(());
            }
        }

        let metadata = extract_metadata(path);
        let (artist_name, album_title, track_title, track_number, duration) = metadata;
        let duration_i64 = duration.map(|d| d as i64);

        let artist_id = self.get_or_create_artist(&artist_name)?;
        let album_id = self.get_or_create_album(artist_id, &album_title)?;

        self.conn.execute(
            "INSERT OR REPLACE INTO tracks (album_id, title, path, duration, track_number, last_scanned)
             VALUES (?, ?, ?, ?, ?, ?)",
            rusqlite::params![album_id, track_title, path_str, duration_i64, track_number, scan_time],
        )?;

        Ok(())
    }

    fn get_or_create_artist(&self, name: &str) -> SqlResult<i64> {
        self.conn
            .execute("INSERT OR IGNORE INTO artists (name) VALUES (?)", [name])?;
        self.conn
            .query_row("SELECT id FROM artists WHERE name = ?", [name], |row| {
                row.get(0)
            })
    }

    fn get_or_create_album(&self, artist_id: i64, title: &str) -> SqlResult<i64> {
        self.conn.execute(
            "INSERT OR IGNORE INTO albums (artist_id, title) VALUES (?, ?)",
            rusqlite::params![artist_id, title],
        )?;
        self.conn.query_row(
            "SELECT id FROM albums WHERE artist_id = ? AND title = ?",
            rusqlite::params![artist_id, title],
            |row| row.get(0),
        )
    }

    fn cleanup_old_tracks(&self, scan_time: i64) -> SqlResult<()> {
        self.conn
            .execute("DELETE FROM tracks WHERE last_scanned < ?", [scan_time])?;
        Ok(())
    }

    pub fn get_album_tracks_after(
        &self,
        album_id: i64,
        after_track_number: Option<i32>,
        exclude_path: &Path,
    ) -> SqlResult<Vec<Track>> {
        let exclude = exclude_path.to_string_lossy();
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title
             FROM tracks t
             JOIN albums a ON t.album_id = a.id
             JOIN artists ar ON a.artist_id = ar.id
             WHERE t.album_id = ?1
               AND t.path != ?3
               AND (?2 IS NULL OR t.track_number > ?2)
             ORDER BY t.track_number",
        )?;

        let rows = stmt.query_map(
            rusqlite::params![album_id, after_track_number, exclude.as_ref()],
            |row| {
                Ok(Track {
                    id: row.get(0)?,
                    album_id: row.get(1)?,
                    title: row.get(2)?,
                    path: PathBuf::from(row.get::<_, String>(3)?),
                    duration: row.get(4)?,
                    track_number: row.get(5)?,
                    artist_name: Some(row.get(6)?),
                    album_title: Some(row.get(7)?),
                })
            },
        )?;

        rows.collect()
    }

    /// Find album tracks that come after the given file's position in its album.
    /// Uses directory-based metadata extraction (matching how the DB stores data).
    /// Returns empty vec if the file's album isn't in the DB.
    pub fn find_album_tracks_after_path(&self, path: &Path) -> SqlResult<Vec<Track>> {
        // Canonicalize so relative paths match absolute paths stored in the DB
        let path = std::fs::canonicalize(path).unwrap_or_else(|_| path.to_path_buf());
        let (ref artist, ref album, _, _, _) = extract_metadata(&path);

        let album_id = match self.conn.query_row(
            "SELECT al.id FROM albums al
             JOIN artists ar ON al.artist_id = ar.id
             WHERE ar.name = ? AND al.title = ?",
            rusqlite::params![artist, album],
            |row| row.get::<_, i64>(0),
        ) {
            Ok(id) => id,
            Err(rusqlite::Error::QueryReturnedNoRows) => return Ok(Vec::new()),
            Err(e) => return Err(e),
        };

        let path_str = path.to_string_lossy();
        let track_number: Option<i32> = self
            .conn
            .query_row(
                "SELECT track_number FROM tracks WHERE path = ?",
                rusqlite::params![path_str.as_ref()],
                |row| row.get(0),
            )
            .unwrap_or(None);

        self.get_album_tracks_after(album_id, track_number, &path)
    }

    pub fn complete(&self, query: &str) -> SqlResult<Vec<String>> {
        let mut results: Vec<String> = Vec::new();

        if query.is_empty() {
            let mut stmt = self.conn.prepare(
                &format!("SELECT name FROM artists ORDER BY name LIMIT {}", LIMIT).to_string(),
            )?;
            let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
            for name in rows.flatten() {
                results.push(format!("{name}/"));
            }
            return Ok(results);
        }

        let has_trailing_slash = query.ends_with('/');

        if has_trailing_slash {
            let prefix = query.trim_end_matches('/');
            let segments: Vec<&str> = prefix.split('/').filter(|s| !s.is_empty()).collect();

            match segments.len() {
                1 => {
                    let artist = segments[0];
                    let mut stmt = self.conn.prepare(
                        &format!(
                            "SELECT a.title FROM albums a
                         JOIN artists ar ON a.artist_id = ar.id
                         WHERE ar.name LIKE ?1
                         ORDER BY a.title LIMIT {}",
                            LIMIT
                        )
                        .to_string(),
                    )?;
                    let rows = stmt.query_map([artist], |row| row.get::<_, String>(0))?;
                    for album in rows.flatten() {
                        results.push(format!("{artist}/{album}/"));
                    }
                }
                2 => {
                    let artist = segments[0];
                    let album = segments[1];
                    let mut stmt = self.conn.prepare(
                        &format!(
                            "SELECT t.title FROM tracks t
                         JOIN albums a ON t.album_id = a.id
                         JOIN artists ar ON a.artist_id = ar.id
                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2
                         ORDER BY t.track_number, t.title LIMIT {}",
                            LIMIT
                        )
                        .to_string(),
                    )?;
                    let rows = stmt.query_map(rusqlite::params![artist, album], |row| {
                        row.get::<_, String>(0)
                    })?;
                    for track in rows.flatten() {
                        results.push(format!("{artist}/{album}/{track}"));
                    }
                }
                _ => {}
            }
        } else {
            let segments: Vec<&str> = query.split('/').collect();

            match segments.len() {
                1 => {
                    let prefix = segments[0];
                    let like_pattern = format!("%{prefix}%");
                    let prefix_pattern = format!("{prefix}%");

                    let mut stmt = self.conn.prepare(
                        &format!(
                            "SELECT name FROM artists
                         WHERE name LIKE ?1
                         ORDER BY
                           CASE WHEN name LIKE ?2 THEN 0 ELSE 1 END,
                           name
                         LIMIT {}",
                            LIMIT
                        )
                        .to_string(),
                    )?;
                    let rows = stmt
                        .query_map(rusqlite::params![&like_pattern, &prefix_pattern], |row| {
                            row.get::<_, String>(0)
                        })?;
                    for name in rows.flatten() {
                        results.push(format!("{name}/"));
                    }
                }
                2 => {
                    let artist = segments[0];
                    let album_prefix = segments[1];
                    if artist.is_empty() {
                        return Ok(results);
                    }

                    let artist_pattern = format!("%{artist}%");
                    let artist_prefix = format!("{artist}%");
                    let album_pattern = format!("%{album_prefix}%");
                    let album_prefix_pattern = format!("{album_prefix}%");

                    let mut stmt = self.conn.prepare(
                        &format!(
                            "SELECT ar.name, a.title FROM albums a
                         JOIN artists ar ON a.artist_id = ar.id
                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2
                         ORDER BY
                           CASE WHEN ar.name LIKE ?3 THEN 0 ELSE 1 END,
                           CASE WHEN a.title LIKE ?4 THEN 0 ELSE 1 END,
                           ar.name, a.title
                         LIMIT {}",
                            LIMIT
                        )
                        .to_string(),
                    )?;
                    let rows = stmt.query_map(
                        rusqlite::params![
                            &artist_pattern,
                            &album_pattern,
                            &artist_prefix,
                            &album_prefix_pattern,
                        ],
                        |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)),
                    )?;
                    for (name, album) in rows.flatten() {
                        results.push(format!("{name}/{album}/"));
                    }
                }
                3 => {
                    let artist = segments[0];
                    let album = segments[1];
                    let track_prefix = segments[2];
                    if artist.is_empty() || album.is_empty() {
                        return Ok(results);
                    }

                    let artist_pattern = format!("%{artist}%");
                    let artist_prefix = format!("{artist}%");
                    let album_pattern = format!("%{album}%");
                    let album_prefix = format!("{album}%");
                    let track_pattern = format!("%{track_prefix}%");
                    let track_prefix_pattern = format!("{track_prefix}%");

                    let mut stmt = self.conn.prepare(
                        &format!(
                            "SELECT ar.name, a.title, t.title FROM tracks t
                         JOIN albums a ON t.album_id = a.id
                         JOIN artists ar ON a.artist_id = ar.id
                         WHERE ar.name LIKE ?1 AND a.title LIKE ?2 AND t.title LIKE ?3
                         ORDER BY
                           CASE WHEN ar.name LIKE ?4 THEN 0 ELSE 1 END,
                           CASE WHEN a.title LIKE ?5 THEN 0 ELSE 1 END,
                           CASE WHEN t.title LIKE ?6 THEN 0 ELSE 1 END,
                           t.track_number, t.title
                         LIMIT {}",
                            LIMIT
                        )
                        .to_string(),
                    )?;
                    let rows = stmt.query_map(
                        rusqlite::params![
                            &artist_pattern,
                            &album_pattern,
                            &track_pattern,
                            &artist_prefix,
                            &album_prefix,
                            &track_prefix_pattern,
                        ],
                        |row| {
                            Ok((
                                row.get::<_, String>(0)?,
                                row.get::<_, String>(1)?,
                                row.get::<_, String>(2)?,
                            ))
                        },
                    )?;
                    for (name, album_title, track) in rows.flatten() {
                        results.push(format!("{name}/{album_title}/{track}"));
                    }
                }
                _ => {}
            }
        }

        results.truncate(LIMIT);

        Ok(results)
    }

    #[allow(dead_code)]
    pub fn open_in_memory() -> SqlResult<Database> {
        let conn = Connection::open_in_memory()?;
        let db = Database { conn };
        db.init_schema()?;
        Ok(db)
    }

    /// Find all tracks in an album by case-insensitive artist and album match.
    pub fn find_tracks_by_album(&self, artist: &str, album: &str) -> SqlResult<Vec<Track>> {
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title
             FROM tracks t
             JOIN albums a ON t.album_id = a.id
             JOIN artists ar ON a.artist_id = ar.id
             WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2
             ORDER BY t.track_number",
        )?;
        let rows = stmt.query_map(rusqlite::params![artist, album], |row| {
            Ok(Track {
                id: row.get(0)?,
                album_id: row.get(1)?,
                title: row.get(2)?,
                path: PathBuf::from(row.get::<_, String>(3)?),
                duration: row.get(4)?,
                track_number: row.get(5)?,
                artist_name: Some(row.get(6)?),
                album_title: Some(row.get(7)?),
            })
        })?;
        rows.collect()
    }

    /// Find a single track by exact artist, exact album, and track title prefix.
    /// Returns None if no match found.
    pub fn find_track_by_parts(
        &self,
        artist: &str,
        album: &str,
        track_prefix: &str,
    ) -> SqlResult<Option<Track>> {
        let pattern = format!("{}%", track_prefix);
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title
             FROM tracks t
             JOIN albums a ON t.album_id = a.id
             JOIN artists ar ON a.artist_id = ar.id
             WHERE ar.name COLLATE NOCASE = ?1 AND a.title COLLATE NOCASE = ?2 AND t.title LIKE ?3
             ORDER BY t.track_number LIMIT 1",
        )?;
        let mut rows = stmt.query_map(rusqlite::params![artist, album, pattern], |row| {
            Ok(Track {
                id: row.get(0)?,
                album_id: row.get(1)?,
                title: row.get(2)?,
                path: PathBuf::from(row.get::<_, String>(3)?),
                duration: row.get(4)?,
                track_number: row.get(5)?,
                artist_name: Some(row.get(6)?),
                album_title: Some(row.get(7)?),
            })
        })?;
        match rows.next() {
            Some(Ok(t)) => Ok(Some(t)),
            Some(Err(e)) => Err(e),
            None => Ok(None),
        }
    }

    /// Resolve a hierarchical path like "Artist/Album" or "Artist/Album/Track"
    /// to DB tracks. Returns None if the path isn't hierarchical (single part
    /// or 4+ parts) — the caller should fall back to search_tracks().
    pub fn resolve_path(&self, path: &str) -> SqlResult<Option<Vec<Track>>> {
        let path = path.strip_suffix('/').unwrap_or(path);
        let parts: Vec<&str> = path.splitn(3, '/').collect();
        match parts.len() {
            2 => Ok(Some(self.find_tracks_by_album(parts[0], parts[1])?)),
            3 => match self.find_track_by_parts(parts[0], parts[1], parts[2])? {
                Some(t) => Ok(Some(vec![t])),
                None => Ok(Some(vec![])),
            },
            _ => Ok(None),
        }
    }

    pub fn search_tracks(&self, query: &str) -> SqlResult<Vec<Track>> {
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.album_id, t.title, t.path, t.duration, t.track_number, ar.name, a.title
             FROM tracks t
             JOIN albums a ON t.album_id = a.id
             JOIN artists ar ON a.artist_id = ar.id
             WHERE t.title LIKE ? OR a.title LIKE ? OR ar.name LIKE ?
             ORDER BY ar.name, a.title, t.track_number",
        )?;

        let search = format!("%{}%", query);
        let rows = stmt.query_map([&search, &search, &search], |row| {
            Ok(Track {
                id: row.get(0)?,
                album_id: row.get(1)?,
                title: row.get(2)?,
                path: PathBuf::from(row.get::<_, String>(3)?),
                duration: row.get(4)?,
                track_number: row.get(5)?,
                artist_name: Some(row.get(6)?),
                album_title: Some(row.get(7)?),
            })
        })?;

        rows.collect()
    }
}

pub fn is_audio_file(path: &Path) -> bool {
    path.extension()
        .and_then(|ext| ext.to_str())
        .map(|ext| {
            matches!(
                ext.to_lowercase().as_str(),
                "mp3" | "flac" | "ogg" | "m4a" | "wav" | "opus"
            )
        })
        .unwrap_or(false)
}

pub fn extract_metadata(path: &Path) -> (String, String, String, Option<i32>, Option<u64>) {
    let file_stem = path
        .file_stem()
        .and_then(|s| s.to_str())
        .unwrap_or("Unknown");
    let parent = path
        .parent()
        .and_then(|p| p.file_name())
        .and_then(|s| s.to_str())
        .unwrap_or("Unknown");
    let grandparent = path
        .parent()
        .and_then(|p| p.parent())
        .and_then(|p| p.file_name())
        .and_then(|s| s.to_str())
        .unwrap_or("Unknown");

    let fs_artist = grandparent.to_string();
    let fs_album = parent.to_string();
    let fs_title = file_stem.to_string();

    if let Some(audio_meta) = crate::queue::extract_metadata(path) {
        (
            audio_meta.artist.unwrap_or(fs_artist),
            audio_meta.album.unwrap_or(fs_album),
            audio_meta.title.unwrap_or(fs_title),
            audio_meta.track_number,
            audio_meta.duration_secs.map(|d| d as u64),
        )
    } else {
        (fs_artist, fs_album, fs_title, None, None)
    }
}