/*
 * 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,
}

#[allow(dead_code)]
impl Database {
    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 conn = Connection::open(&db_path)?;
        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;

        self.scan_recursive(dir, now, force)?;
        self.cleanup_old_tracks(now)?;
        Ok(())
    }

    fn scan_recursive(&self, dir: &Path, scan_time: i64, force: bool) -> 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)?;
                } else if is_audio_file(&path) {
                    self.process_audio_file(&path, scan_time, force)?;
                }
            }
        }
        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 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");

    (
        grandparent.to_string(),
        parent.to_string(),
        file_stem.to_string(),
        None,
        None,
    )
}