use sqlx::types::chrono::{self, NaiveDateTime};
use sqlx::{FromRow, MySqlPool};

#[derive(Debug, Clone, FromRow)]
pub struct SnapshotDescription {
    pub id: String,
    pub tenant_token: String,
    pub collection_name: String,
    pub name: String,
    pub size: i64,
    pub checksum: String,
    pub path: String,
    pub created_at: NaiveDateTime,
    pub deleted_at: Option<NaiveDateTime>,
}

impl SnapshotDescription {
    pub fn new(
        tenant_token: String,
        collection_name: String,
        name: String,
        size: i64,
        checksum: String,
        path: String,
    ) -> Self {
        Self {
            id: uuid::Uuid::new_v4().to_string(),
            tenant_token,
            collection_name,
            name,
            size,
            checksum,
            path,
            created_at: chrono::Utc::now().naive_utc(),
            deleted_at: None,
        }
    }

    pub async fn save(self, db: &MySqlPool) -> sqlx::Result<()> {
        let query = format!(
            r#"
        INSERT INTO `snapshots_description` (
            `id`, `tenant_token`, `collection_name`, `name`, `size`, `checksum`, `path`, `created_at`
        ) VALUES (
            '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}'
        );
        "#,
            self.id,
            self.tenant_token,
            self.collection_name,
            self.name,
            self.size,
            self.checksum,
            self.path,
            self.created_at
        );
        sqlx::query(&query).execute(db).await?;
        Ok(())
    }

    pub async fn delete_by_id(db: &MySqlPool, id: &str) -> sqlx::Result<()> {
        let query = format!(
            r#"
        UPDATE `snapshots_description` SET `deleted_at` = CURRENT_TIMESTAMP WHERE `id` = '{}';
        "#,
            id
        );
        sqlx::query(&query).execute(db).await?;
        Ok(())
    }

    pub async fn delete(
        db: &MySqlPool,
        tenant_token: &str,
        collection_name: &str,
        name: &str,
    ) -> sqlx::Result<()> {
        let query = format!(
            r#"
        UPDATE `snapshots_description` SET `deleted_at` = CURRENT_TIMESTAMP WHERE `tenant_token` = '{}' AND `collection_name` = '{}' AND `name` = '{}';
        "#,
            tenant_token, collection_name, name
        );
        sqlx::query(&query).execute(db).await?;
        Ok(())
    }

    pub async fn query_list(
        db: &MySqlPool,
        tenant_token: &str,
        collection_name: &str,
    ) -> sqlx::Result<Vec<Self>> {
        let query = format!(
            r#"
        SELECT * FROM `snapshots_description` WHERE `tenant_token` = '{}' AND `collection_name` = '{}' AND `deleted_at` IS NULL ORDER BY `created_at` DESC;
        "#,
            tenant_token, collection_name
        );
        let snapshots = sqlx::query_as::<_, SnapshotDescription>(&query)
            .fetch_all(db)
            .await?;
        Ok(snapshots)
    }

    pub async fn query_count(
        db: &MySqlPool,
        tenant_token: &str,
        collection_name: &str,
    ) -> sqlx::Result<u64> {
        let query = format!(
            r#"
        SELECT COUNT(*) FROM `snapshots_description` WHERE `tenant_token` = '{}' AND `collection_name` = '{}' AND `deleted_at` IS NULL;
        "#,
            tenant_token, collection_name
        );
        let count: (i64,) = sqlx::query_as(&query).fetch_one(db).await?;
        Ok(count.0 as u64)
    }
}