use crate::models::{Status, Task};
use chrono::Local;
use rusqlite::{Connection, Result};

pub struct Database {
    conn: Connection,
}

impl Database {
    pub fn new(db_path: &str) -> Result<Self> {
        let conn = Connection::open(db_path)?;
        let db = Database { conn };
        db.init()?;
        Ok(db)
    }

    fn init(&self) -> Result<()> {
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            status TEXT NOT NULL,
            story_points INTEGER DEFAULT 0,
            start_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            update_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            end_date DATETIME DEFAULT NULL)",
            [],
        )?;
        Ok(())
    }

    pub fn add_task(&self, name: &str, status: Status) -> Result<()> {
        if name.trim().is_empty() {
            return Err(rusqlite::Error::InvalidParameterName(
                "Name cannot be empty".to_string(),
            ));
        }

        let str_status: String = format!("{}", status);
        self.conn.execute(
            "INSERT INTO tasks (name, status) VALUES (?1, ?2)",
            [name, &str_status],
        )?;
        Ok(())
    }

    pub fn get_tasks(&self) -> Result<Vec<Task>> {
        let mut stmt = self.conn.prepare(
            "SELECT
                id,
                name,
                status,
                story_points,
                start_date,
                IFNULL(end_date, ''),
                update_date
            FROM tasks ORDER BY start_date ASC",
        )?;
        let tasks = stmt.query_map([], |row| {
            Ok(Task {
                id: row.get(0)?,
                name: row.get(1)?,
                status: row.get(2)?,
                story_points: row.get::<_, i32>(3)?,
                start_date: row.get(4)?,
                end_date: row.get(5)?,
                update_date: row.get(6)?,
            })
        })?;

        tasks.collect()
    }

    pub fn get_tasks_paginated(&self, page: u32, page_size: u32) -> Result<Vec<Task>> {
        let offset = page * page_size;
        let mut stmt = self.conn.prepare(
            "SELECT
            id,
            name,
            status,
            story_points,
            start_date,
            IFNULL(end_date, ''),
            update_date
        FROM tasks ORDER BY start_date ASC LIMIT ?1 OFFSET ?2",
        )?;

        let tasks = stmt.query_map([page_size, offset], |row| {
            Ok(Task {
                id: row.get(0)?,
                name: row.get(1)?,
                status: row.get(2)?,
                story_points: row.get::<_, i32>(3)?,
                start_date: row.get(4)?,
                end_date: row.get(5)?,
                update_date: row.get(6)?,
            })
        })?;

        tasks.collect()
    }

    pub fn count_tasks(&self) -> Result<u32> {
        let count: i64 = self
            .conn
            .query_row("SELECT COUNT(*) FROM tasks", [], |row| row.get(0))?;
        Ok(count as u32)
    }

    pub fn update_status(&self, id: i64, status: Status) -> Result<()> {
        if status == Status::Done {
            let end_date = Local::now();
            let row_affected = self.conn.execute(
                "UPDATE tasks SET status = ?1, end_date = ?2 WHERE id = ?3",
                [
                    &format!("{}", status),
                    &end_date.to_string(),
                    &id.to_string(),
                ],
            )?;
            if row_affected == 0 {
                return Err(rusqlite::Error::QueryReturnedNoRows);
            }
            return Ok(());
        } else {
            let update_date = Local::now();
            let row_affected = self.conn.execute(
                "UPDATE tasks SET status = ?1, update_date = ?2 WHERE id = ?3",
                [status.as_ref(), &update_date.to_string(), &id.to_string()],
            )?;
            if row_affected == 0 {
                return Err(rusqlite::Error::QueryReturnedNoRows);
            }
        }

        Ok(())
    }

    pub fn get_task_by_id(&self, id: i64) -> Result<Option<Task>> {
        let mut stmt = self.conn.prepare(
            "SELECT
                id,
                name,
                status,
                story_points,
                start_date,
                IFNULL(end_date, ''),
                update_date
            FROM tasks WHERE id = ?1",
        )?;
        let mut rows = stmt.query([id])?;

        if let Some(row) = rows.next()? {
            Ok(Some(Task {
                id: row.get(0)?,
                name: row.get(1)?,
                status: row.get(2)?,
                story_points: row.get::<_, i32>(3)?,
                start_date: row.get(4)?,
                end_date: row.get(5)?,
                update_date: row.get(6)?,
            }))
        } else {
            Ok(None)
        }
    }

    pub fn update_story_point(&self, id: i64, points: String) -> Result<()> {
        let affected_rows = self.conn.execute(
            "UPDATE tasks SET story_points = ?1 WHERE id = ?2",
            [points, id.to_string()],
        )?;

        if affected_rows == 0 {
            return Err(rusqlite::Error::QueryReturnedNoRows);
        }

        Ok(())
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn create_test_db() -> Database {
        Database::new(":memory:").expect("Failed to create in-memory database")
    }

    #[test]
    fn test_database_initialization() {
        let db = create_test_db();
        let todos = db.get_tasks().expect("Failed to get tasks");
        assert!(todos.is_empty());
    }

    #[test]
    fn test_add_task() {
        let db = create_test_db();
        db.add_task("MST2-1313", Status::WIP)
            .expect("Cannot add new task");

        let task = db.get_tasks().expect("Failed to get tasks");
        assert_eq!(task.len(), 1);
        assert_ne!(task[0].start_date, "");
    }

    #[test]
    fn test_update_task_done() {
        let db = create_test_db();
        db.add_task("MST2-1313", Status::Selected)
            .expect("Cannot add new task");

        let mut tasks = db.get_tasks().expect("Failed to get tasks");
        db.update_status(tasks[0].id, Status::Done)
            .expect("Failed to update tasks");
        tasks = db.get_tasks().expect("Failed to get tasks");

        assert_ne!(tasks[0].end_date, "");
        assert_eq!(tasks[0].status, format!("{}", Status::Done));
    }

    #[test]
    fn test_update_task() {
        let db = create_test_db();
        db.add_task("MST2-1313", Status::Selected)
            .expect("Cannot add new task");

        let mut tasks = db.get_tasks().expect("Failed to get tasks");
        db.update_status(tasks[0].id, Status::WIP)
            .expect("Failed to update tasks");
        tasks = db.get_tasks().expect("Failed to get tasks");

        assert_eq!(tasks[0].status, format!("{}", Status::WIP));
    }

    #[test]
    fn test_count_tasks() {
        let db = create_test_db();
        db.add_task("test", Status::Selected)
            .expect("Failed to add new task");
        db.add_task("test2", Status::Selected)
            .expect("Failed to add new task");

        let total = db.count_tasks().expect("Failed to get tasks count");
        assert_eq!(total, 2);
    }
}
