use crate::auth::PointAdjustment;
use crate::db::Database;

impl Database {
    pub async fn create_point_adjustment(&self, adj: &PointAdjustment) -> Result<(), String> {
        self.conn()
            .await?
            .execute(
                "INSERT INTO point_adjustments (id, user_id, points, reason, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
                turso::params![
                    adj.id.clone(),
                    adj.user_id.clone(),
                    adj.points,
                    adj.reason.clone(),
                    adj.created_at.clone()
                ],
            )
            .await
            .map_err(|e| e.to_string())?;
        Ok(())
    }

    pub async fn get_point_adjustments_for_user(
        &self,
        user_id: &str,
    ) -> Result<Vec<PointAdjustment>, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT id, user_id, points, reason, created_at FROM point_adjustments WHERE user_id = ?1 ORDER BY created_at DESC",
                turso::params![user_id],
            )
            .await
            .map_err(|e| e.to_string())?;

        let mut result = Vec::new();
        while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
            result.push(PointAdjustment {
                id: row.get(0).map_err(|e| e.to_string())?,
                user_id: row.get(1).map_err(|e| e.to_string())?,
                points: row.get(2).map_err(|e| e.to_string())?,
                reason: row.get(3).map_err(|e| e.to_string())?,
                created_at: row.get(4).map_err(|e| e.to_string())?,
            });
        }
        Ok(result)
    }

    pub async fn get_all_point_adjustments(&self) -> Result<Vec<PointAdjustment>, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT id, user_id, points, reason, created_at FROM point_adjustments ORDER BY created_at DESC",
                (),
            )
            .await
            .map_err(|e| e.to_string())?;

        let mut result = Vec::new();
        while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
            result.push(PointAdjustment {
                id: row.get(0).map_err(|e| e.to_string())?,
                user_id: row.get(1).map_err(|e| e.to_string())?,
                points: row.get(2).map_err(|e| e.to_string())?,
                reason: row.get(3).map_err(|e| e.to_string())?,
                created_at: row.get(4).map_err(|e| e.to_string())?,
            });
        }
        Ok(result)
    }

    pub async fn get_adjustment_points_for_user(&self, user_id: &str) -> Result<f64, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT COALESCE(SUM(points), 0.0) FROM point_adjustments WHERE user_id = ?1",
                turso::params![user_id],
            )
            .await
            .map_err(|e| e.to_string())?;

        if let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
            Ok(row.get(0).map_err(|e| e.to_string())?)
        } else {
            Ok(0.0)
        }
    }

    pub async fn get_total_adjustment_points(&self) -> Result<f64, String> {
        let mut rows = self
            .conn()
            .await?
            .query(
                "SELECT COALESCE(SUM(points), 0.0) FROM point_adjustments",
                (),
            )
            .await
            .map_err(|e| e.to_string())?;

        if let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
            Ok(row.get(0).map_err(|e| e.to_string())?)
        } else {
            Ok(0.0)
        }
    }

    pub async fn delete_point_adjustment(&self, id: &str) -> Result<(), String> {
        self.conn()
            .await?
            .execute(
                "DELETE FROM point_adjustments WHERE id = ?1",
                turso::params![id],
            )
            .await
            .map_err(|e| e.to_string())?;
        Ok(())
    }
}