use rusqlite::{params, Connection, Result};
use crate::models::{QueuedOutput, DispatchStatus};
use uuid::Uuid;
use chrono::{DateTime, Utc};
use std::str::FromStr;

pub struct Database {
    conn: Connection,
}

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

    fn init(conn: &Connection) -> Result<()> {
        conn.execute(
            "CREATE TABLE IF NOT EXISTS stack (
                id TEXT PRIMARY KEY,
                target TEXT NOT NULL,
                content TEXT NOT NULL,
                metadata TEXT,
                status TEXT NOT NULL,
                comment TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )",
            [],
        )?;
        Ok(())
    }

    pub fn enqueue(&self, target: &str, content: &str, metadata: Option<&str>) -> Result<Uuid> {
        let id = Uuid::new_v4();
        let now = Utc::now().to_rfc3339();
        let status = "Pending";

        self.conn.execute(
            "INSERT INTO stack (id, target, content, metadata, status, created_at, updated_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![id.to_string(), target, content, metadata, status, now, now],
        )?;

        Ok(id)
    }

    pub fn approve(&self, id: &Uuid) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        self.conn.execute(
            "UPDATE stack SET status = 'Approved', updated_at = ?2 WHERE id = ?1",
            params![id.to_string(), now],
        )?;
        Ok(())
    }

    pub fn reject(&self, id: &Uuid, comment: &str) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        self.conn.execute(
            "UPDATE stack SET status = 'Rejected', comment = ?2, updated_at = ?3 WHERE id = ?1",
            params![id.to_string(), comment, now],
        )?;
        Ok(())
    }

    pub fn list_pending(&self) -> Result<Vec<QueuedOutput>> {
        self.list_by_status("Pending")
    }

    pub fn get_rejected(&self) -> Result<Vec<QueuedOutput>> {
        self.list_by_status("Rejected")
    }

    fn list_by_status(&self, status_filter: &str) -> Result<Vec<QueuedOutput>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, target, content, metadata, status, comment, created_at, updated_at FROM stack WHERE status = ?1"
        )?;
        let rows = stmt.query_map(params![status_filter], |row| {
            let id_str: String = row.get(0)?;
            let status_str: String = row.get(4)?;
            let created_at_str: String = row.get(6)?;
            let updated_at_str: String = row.get(7)?;

            let status = match status_str.as_str() {
                "Pending" => DispatchStatus::Pending,
                "Approved" => DispatchStatus::Approved,
                "Rejected" => DispatchStatus::Rejected,
                "Sent" => DispatchStatus::Sent,
                "Failed" => DispatchStatus::Failed,
                _ => DispatchStatus::Pending,
            };

            Ok(QueuedOutput {
                id: Uuid::from_str(&id_str).unwrap_or_else(|_| Uuid::nil()),
                target: row.get(1)?,
                content: row.get(2)?,
                metadata: row.get(3)?,
                status,
                comment: row.get(5)?,
                created_at: DateTime::parse_from_rfc3339(&created_at_str).map(|dt| dt.with_timezone(&Utc)).unwrap_or_else(|_| Utc::now()),
                updated_at: DateTime::parse_from_rfc3339(&updated_at_str).map(|dt| dt.with_timezone(&Utc)).unwrap_or_else(|_| Utc::now()),
            })
        })?;

        let mut results = Vec::new();
        for row in rows {
            results.push(row?);
        }
        Ok(results)
    }
}