use std::fmt;

use rusqlite::{
    Connection, ToSql, params,
    types::{FromSql, FromSqlError, FromSqlResult, ToSqlOutput, Value, ValueRef},
};

use crate::app::Sort;

// *brakoll - d: work on connecting gui to backend, p: 0, t: feature, s: closed
#[allow(dead_code)]
pub enum IssueStatus {
    Open,
    InProgress,
    Closed,
}
impl IssueStatus {
    fn rank(&self) -> u8 {
        match self {
            IssueStatus::InProgress => 0,
            IssueStatus::Open => 1,
            IssueStatus::Closed => 2,
        }
    }
}
// *brakoll - d: make methods more intuitive to maintain, p: 0, t: refactor, s: closed
impl ToSql for IssueStatus {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        let s = match self {
            IssueStatus::Open => "open",
            IssueStatus::InProgress => "in_progress",
            IssueStatus::Closed => "closed",
        };

        Ok(ToSqlOutput::Owned(Value::Text(s.to_string())))
    }
}

impl FromSql for IssueStatus {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        let s = value.as_str()?;

        match s {
            "open" => Ok(IssueStatus::Open),
            "in_progress" => Ok(IssueStatus::InProgress),
            "closed" => Ok(IssueStatus::Closed),
            _ => Err(FromSqlError::Other("invalid IssueStatus".into())),
        }
    }
}
impl fmt::Display for IssueStatus {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        let s = match self {
            IssueStatus::Open => "Open",
            IssueStatus::InProgress => "In Progress",
            IssueStatus::Closed => "Closed",
        };
        write!(f, "{s}")
    }
}

pub struct Issue {
    pub id: Option<i64>,
    pub descr: String,
    pub tag: String,
    pub file_name: String,
    pub status: IssueStatus,
    pub priority: i64,
    pub date: String,
}

impl Issue {
    pub fn insert(&mut self, conn: &Connection) -> rusqlite::Result<()> {
        conn.execute(
            "INSERT INTO issues (descr, tag, file_name, status, priority, date)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
             rusqlite::params![
             self.descr,
             self.tag,
             self.file_name,
             self.status,
             self.priority,
             self.date,
             ],
        )?;

        self.id = Some(conn.last_insert_rowid());
        Ok(())
    }

    #[allow(dead_code)]
    pub fn delete(&mut self, conn: &Connection) -> rusqlite::Result<()> {
        conn.execute("DELETE FROM issues WHERE id = ?1", params![self.id])?;
        Ok(())
    }

    #[allow(dead_code)]
    /// cycle between status types
    pub fn toggle_status(&mut self) {
        match self.status {
            IssueStatus::Open => self.status = IssueStatus::InProgress,
            IssueStatus::InProgress => self.status = IssueStatus::Closed,
            IssueStatus::Closed => self.status = IssueStatus::Open,
        }
    }

    #[allow(dead_code)]
    pub fn copy_to_cb(&mut self) {
        let output = format!(
            "{t} ({f}): {d}",
            t = self.tag,
            f = self.file_name,
            d = self.descr
        );
        cli_clipboard::set_contents(output.to_owned()).unwrap();
    }
}

#[allow(dead_code)]
pub enum TodoStatus {
    NotDone,
    Done,
}

impl ToSql for TodoStatus {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        let s = match self {
            TodoStatus::NotDone => "not_done",
            TodoStatus::Done => "done",
        };

        Ok(ToSqlOutput::Owned(Value::Text(s.to_string())))
    }
}

impl FromSql for TodoStatus {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        match value.as_str()? {
            "not_done" => Ok(TodoStatus::NotDone),
            "done" => Ok(TodoStatus::Done),
            other => Err(FromSqlError::Other(
                    format!("invalid TodoStatus: {other}").into(),
            )),
        }
    }
}

impl fmt::Display for TodoStatus {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        let s = match self {
            TodoStatus::NotDone => "Not Done",
            TodoStatus::Done => "Done",
        };
        write!(f, "{s}")
    }
}

pub struct Todo {
    pub id: Option<i64>,
    pub descr: String,
    pub status: TodoStatus,
    pub date: String,
}

impl Todo {
    pub fn insert(&mut self, conn: &Connection) -> rusqlite::Result<()> {
        conn.execute(
            "INSERT INTO todo (descr, status, date)
            VALUES (?1, ?2, ?3)",
            rusqlite::params![self.descr, self.status, self.date,],
        )?;
        self.id = Some(conn.last_insert_rowid());
        Ok(())
    }

    #[allow(dead_code)]
    fn delete(&mut self, conn: &Connection) -> rusqlite::Result<()> {
        conn.execute("DELETE FROM todo WHERE id = ?1", params![self.id])?;
        Ok(())
    }

    #[allow(dead_code)]
    /// cycle between status types
    pub fn toggle_status(&mut self) {
        match self.status {
            TodoStatus::NotDone => self.status = TodoStatus::Done,
            TodoStatus::Done => self.status = TodoStatus::NotDone,
        }
    }
}

pub struct Db {
    pub conn: Connection,
    pub issues: Vec<Issue>,
    pub todos: Vec<Todo>,
}

impl Db {
    pub fn collect_todos(&mut self, sort: Sort) -> rusqlite::Result<()> {
        let mut stmt = self.conn.prepare(
            "SELECT id, descr, status, date
            FROM todo
             ORDER BY id DESC",
        )?;

        let rows = stmt.query_map([], |row| {
            Ok(Todo {
                id: row.get(0)?,
                descr: row.get(1)?,
                status: row.get(2)?,
                date: row.get(3)?,
            })
        })?;

        self.todos = rows.collect::<rusqlite::Result<Vec<Todo>>>()?;

        match sort {
            Sort::Id => self.todos.sort_by(|a, b| b.id.cmp(&a.id)),
            Sort::Status => self.todos.sort_by(|a, b| {
                let a_rank = match a.status {
                    TodoStatus::NotDone => 0,
                    TodoStatus::Done => 1,
                };
                let b_rank = match b.status {
                    TodoStatus::NotDone => 0,
                    TodoStatus::Done => 1,
                };
                a_rank.cmp(&b_rank)
            }),
            Sort::Date => self.todos.sort_by(|a, b| b.date.cmp(&a.date)),
        }

        Ok(())
    }

    pub fn collect_issues(&mut self, sort: Sort) -> rusqlite::Result<()> {
        let mut stmt = self.conn.prepare(
            "SELECT id, descr, tag, file_name, status, priority, date
             FROM issues
             ORDER BY id DESC",
        )?;

        let rows = stmt.query_map([], |row| {
            Ok(Issue {
                id: row.get(0)?,
                descr: row.get(1)?,
                tag: row.get(2)?,
                file_name: row.get(3)?,
                status: row.get(4)?,
                priority: row.get(5)?,
                date: row.get(6)?,
            })
        })?;

        self.issues = rows.collect::<rusqlite::Result<Vec<Issue>>>()?;

        match sort {
            Sort::Id => {
                self.issues.sort_by(|a, b| {
                    b.id.cmp(&a.id).then(a.priority.cmp(&b.priority))
                });
            }
            Sort::Status => {
                self.issues.sort_by(|a, b| {
                    a.status.rank()
                        .cmp(&b.status.rank())
                        .then(a.priority.cmp(&b.priority))
                });
            }
            Sort::Date => {
                self.issues.sort_by(|a, b| {
                    b.date.cmp(&a.date).then(a.priority.cmp(&b.priority))
                });
            }
        }

        Ok(())
    }

    pub fn init_tables(&mut self) -> rusqlite::Result<()> {
        {
            let changed_rows = self.conn.execute(
                "CREATE TABLE IF NOT EXISTS issues (
                    id INTEGER PRIMARY KEY,
                descr TEXT NOT NULL CHECK(length(descr) > 0),
                tag TEXT NOT NULL CHECK(length(tag) > 0),
                file_name TEXT NOT NULL CHECK(length(file_name) > 0),
                status TEXT NOT NULL CHECK(status IN ('open', 'in_progress', 'closed')),
                priority INTEGER NOT NULL DEFAULT 1 CHECK(priority BETWEEN 1 AND 10),
                date TEXT NOT NULL
            )",
                [],
            )?;
            if changed_rows > 0 {
                self.collect_issues(Sort::Status)?;
            }
        }
        {
            let changed_rows = self.conn.execute(
                "CREATE TABLE IF NOT EXISTS todo (
                    id INTEGER PRIMARY KEY,
                descr TEXT NOT NULL CHECK(length(descr) > 0),
                status TEXT NOT NULL CHECK(status IN ('not_done', 'done')),
                date TEXT NOT NULL
            )",
                [],
            )?;
            if changed_rows > 0 {
                self.collect_todos(Sort::Status)?;
            }
        }
        {
            let todo_has_items: bool = self.conn.query_row(
                "SELECT EXISTS(SELECT 1 FROM todo LIMIT 1)",
                [],
                |row| row.get(0),
            )?;
            if todo_has_items {
                self.collect_todos(Sort::Status)?;
            }
            let issues_has_items: bool = self.conn.query_row(
                "SELECT EXISTS(SELECT 1 FROM issues LIMIT 1)",
                [],
                |row| row.get(0),
            )?;
            if issues_has_items {
                self.collect_issues(Sort::Status)?;
            }
        }

        Ok(())
    }

    #[allow(dead_code)]
    pub fn debug_print_all(&mut self) -> rusqlite::Result<()> {
        println!("\nISSUES");
        println!("----------------------------");
        {
            let mut stmt = self.conn.prepare(
                "SELECT id, descr, tag, file_name, status, priority, date
                FROM issues
            ORDER BY id DESC",
            )?;

            let rows = stmt.query_map([], |row| {
                Ok((
                        row.get::<_, i64>(0)?,
                        row.get::<_, String>(1)?,
                        row.get::<_, String>(2)?,
                        row.get::<_, String>(3)?,
                        row.get::<_, String>(4)?,
                        row.get::<_, i64>(5)?,
                        row.get::<_, String>(6)?,
                ))
            })?;

            for row in rows {
                println!("{:?}", row?);
            }
        }

        println!("\nTODO");
        println!("----------------------------");
        {
            let mut stmt = self.conn.prepare(
                "SELECT id, descr, status, date
                FROM todo
                ORDER BY id DESC",
            )?;

            let rows = stmt.query_map([], |row| {
                Ok((
                        row.get::<_, i64>(0)?,
                        row.get::<_, String>(1)?,
                        row.get::<_, String>(2)?,
                        row.get::<_, String>(3)?,
                ))
            })?;

            for row in rows {
                println!("{:?}", row?);
            }
        }

        Ok(())
    }
}