use cgcx_core::{AdminAction, Content, ContentFile, ContentId, ContentStatus, ForwardDefinition, ForwardSubmission, Punishment, Report, ReportStatus, Result, CgcxError, User};
use rusqlite::{params, OptionalExtension, Connection};
use std::sync::Arc;
use tokio::sync::Mutex;

pub struct UserRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl UserRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn ensure_exists(&self, id: i64, username: Option<&str>, first_name: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO users (id, telegram_username, first_name) VALUES (?1, ?2, ?3)
             ON CONFLICT(id) DO UPDATE SET telegram_username=excluded.telegram_username, first_name=excluded.first_name",
            params![id, username, first_name],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn get(&self, id: i64) -> Result<Option<User>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, telegram_username, first_name, role, accepted_terms_at, created_at FROM users WHERE id = ?1",
            params![id],
            |row| {
                let role: String = row.get(3)?;
                Ok(User {
                    id: row.get(0)?,
                    telegram_username: row.get(1)?,
                    first_name: row.get(2)?,
                    role: match role.as_str() {
                        "admin" => cgcx_core::UserRole::Admin,
                        "banned" => cgcx_core::UserRole::Banned,
                        _ => cgcx_core::UserRole::User,
                    },
                    accepted_terms_at: row.get(4)?,
                    created_at: row.get(5)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn set_role(&self, id: i64, role: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE users SET role = ?1 WHERE id = ?2",
            params![role, id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn set_accepted_terms(&self, id: i64) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE users SET accepted_terms_at = datetime('now') WHERE id = ?1",
            params![id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }
}

pub struct ContentRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl ContentRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(&self, content: &Content) -> Result<()> {
        let conn = self.conn.lock().await;
        let status = format!("{:?}", content.status).to_lowercase();
        conn.execute(
            "INSERT INTO contents (id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
             ON CONFLICT(id) DO NOTHING",
            params![
                content.id.as_str(),
                content.user_id,
                status,
                content.view_count as i64,
                content.max_views.map(|v| v as i64),
                content.allow_download as i64,
                content.password_hash.as_ref(),
                content.created_at,
                content.deleted_at,
            ],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn get(&self, id: &ContentId) -> Result<Option<Content>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at
             FROM contents WHERE id = ?1",
            params![id.as_str()],
            |row| {
                let status: String = row.get(2)?;
                Ok(Content {
                    id: ContentId::new_unchecked(row.get(0)?),
                    user_id: row.get(1)?,
                    status: match status.as_str() {
                        "staged" => ContentStatus::Staged,
                        "deleted" => ContentStatus::Deleted,
                        "blacklisted" => ContentStatus::Blacklisted,
                        _ => ContentStatus::Active,
                    },
                    view_count: row.get::<_, i64>(3)? as u64,
                    max_views: row.get::<_, Option<i64>>(4)?.map(|v| v as u64),
                    allow_download: row.get::<_, i64>(5)? != 0,
                    password_hash: row.get(6)?,
                    created_at: row.get(7)?,
                    deleted_at: row.get(8)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn list_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Content>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, user_id, status, view_count, max_views, allow_download, password_hash, created_at, deleted_at
             FROM contents WHERE user_id = ?1 AND status != 'deleted' ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map(params![user_id, limit as i64, offset as i64], |row| {
            let status: String = row.get(2)?;
            Ok(Content {
                id: ContentId::new_unchecked(row.get(0)?),
                user_id: row.get(1)?,
                status: match status.as_str() {
                    "staged" => ContentStatus::Staged,
                    "deleted" => ContentStatus::Deleted,
                    "blacklisted" => ContentStatus::Blacklisted,
                    _ => ContentStatus::Active,
                },
                view_count: row.get::<_, i64>(3)? as u64,
                max_views: row.get::<_, Option<i64>>(4)?.map(|v| v as u64),
                allow_download: row.get::<_, i64>(5)? != 0,
                password_hash: row.get(6)?,
                created_at: row.get(7)?,
                deleted_at: row.get(8)?,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut out = Vec::new();
        for r in rows {
            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(out)
    }

    pub async fn count_by_user(&self, user_id: i64) -> Result<usize> {
        let conn = self.conn.lock().await;
        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM contents WHERE user_id = ?1 AND status != 'deleted'",
            params![user_id],
            |row| row.get(0),
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(count as usize)
    }

    pub async fn increment_views(&self, id: &ContentId) -> Result<u64> {
        let conn = self.conn.lock().await;
        let new: i64 = conn.query_row(
            "UPDATE contents SET view_count = view_count + 1 WHERE id = ?1 RETURNING view_count",
            params![id.as_str()],
            |row| row.get(0),
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(new as u64)
    }

    pub async fn set_status(&self, id: &ContentId, status: ContentStatus) -> Result<()> {
        let conn = self.conn.lock().await;
        let s = format!("{:?}", status).to_lowercase();
        conn.execute(
            "UPDATE contents SET status = ?1, deleted_at = CASE WHEN ?1 IN ('deleted','blacklisted') THEN datetime('now') ELSE NULL END WHERE id = ?2",
            params![s, id.as_str()],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn delete_permanent(&self, id: &ContentId) -> Result<()> {
        let mut conn = self.conn.lock().await;
        let tx = conn.transaction().map_err(|e| CgcxError::Database(e.to_string()))?;
        tx.execute("DELETE FROM content_files WHERE content_id = ?1", params![id.as_str()])
            .map_err(|e| CgcxError::Database(e.to_string()))?;
        tx.execute("DELETE FROM contents WHERE id = ?1", params![id.as_str()])
            .map_err(|e| CgcxError::Database(e.to_string()))?;
        tx.commit().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn update_password_hash(&self, id: &ContentId, password_hash: Option<&str>) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE contents SET password_hash = ?1 WHERE id = ?2",
            params![password_hash, id.as_str()],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }
}

pub struct ContentFileRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl ContentFileRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(&self, file: &ContentFile) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO content_files (content_id, file_index, original_name, stored_path, mime_type, size_bytes, ciphertext_size_bytes, encrypted_key_wrapped, encrypted_hash, render_flags, created_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)
             ON CONFLICT(content_id, file_index) DO NOTHING",
            params![
                file.content_id.as_str(),
                file.file_index as i64,
                &file.original_name,
                file.stored_path.to_str(),
                &file.mime_type,
                file.size_bytes as i64,
                file.ciphertext_size_bytes as i64,
                &file.encrypted_key_wrapped,
                &file.encrypted_hash,
                file.render_flags as i64,
                file.created_at,
            ],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn list_by_content(&self, content_id: &ContentId) -> Result<Vec<ContentFile>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT content_id, file_index, original_name, stored_path, mime_type, size_bytes, ciphertext_size_bytes, encrypted_key_wrapped, encrypted_hash, render_flags, created_at
             FROM content_files WHERE content_id = ?1 ORDER BY file_index"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map(params![content_id.as_str()], |row| {
            Ok(ContentFile {
                content_id: ContentId::new_unchecked(row.get(0)?),
                file_index: row.get::<_, i64>(1)? as u32,
                original_name: row.get(2)?,
                stored_path: std::path::PathBuf::from(row.get::<_, String>(3)?),
                mime_type: row.get(4)?,
                size_bytes: row.get::<_, i64>(5)? as u64,
                ciphertext_size_bytes: row.get::<_, i64>(6)? as u64,
                encrypted_key_wrapped: row.get(7)?,
                encrypted_hash: row.get(8)?,
                render_flags: row.get::<_, i64>(9)? as u32,
                created_at: row.get(10)?,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut out = Vec::new();
        for r in rows {
            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(out)
    }

    pub async fn find_orphan_files(&self) -> Result<Vec<String>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT cf.stored_path FROM content_files cf
             JOIN contents c ON c.id = cf.content_id
             WHERE c.status IN ('deleted', 'blacklisted')"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map([], |row| {
            row.get::<_, String>(0)
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut out = Vec::new();
        for r in rows {
            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(out)
    }
}

pub struct ReportRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl ReportRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(&self, content_id: &ContentId, reporter_user_id: i64, reason: &str) -> Result<i64> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO reports (content_id, reporter_user_id, reason) VALUES (?1, ?2, ?3)",
            params![content_id.as_str(), reporter_user_id, reason],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(conn.last_insert_rowid())
    }

    pub async fn get(&self, id: i64) -> Result<Option<Report>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, content_id, reporter_user_id, reason, status, created_at, resolved_at, resolver_id
             FROM reports WHERE id = ?1",
            params![id],
            |row| {
                let status: String = row.get(4)?;
                Ok(Report {
                    id: row.get(0)?,
                    content_id: ContentId::new_unchecked(row.get(1)?),
                    reporter_user_id: row.get(2)?,
                    reason: row.get(3)?,
                    status: match status.as_str() {
                        "dismissed" => ReportStatus::Dismissed,
                        "actioned" => ReportStatus::Actioned,
                        _ => ReportStatus::Open,
                    },
                    created_at: row.get(5)?,
                    resolved_at: row.get(6)?,
                    resolver_id: row.get(7)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn list(&self, limit: usize, offset: usize) -> Result<Vec<Report>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, content_id, reporter_user_id, reason, status, created_at, resolved_at, resolver_id
             FROM reports ORDER BY created_at DESC LIMIT ?1 OFFSET ?2"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map(params![limit as i64, offset as i64], |row| {
            let status: String = row.get(4)?;
            Ok(Report {
                id: row.get(0)?,
                content_id: ContentId::new_unchecked(row.get(1)?),
                reporter_user_id: row.get(2)?,
                reason: row.get(3)?,
                status: match status.as_str() {
                    "dismissed" => ReportStatus::Dismissed,
                    "actioned" => ReportStatus::Actioned,
                    _ => ReportStatus::Open,
                },
                created_at: row.get(5)?,
                resolved_at: row.get(6)?,
                resolver_id: row.get(7)?,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut out = Vec::new();
        for r in rows {
            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(out)
    }

    pub async fn resolve(&self, id: i64, status: ReportStatus, resolver_id: i64) -> Result<()> {
        let conn = self.conn.lock().await;
        let s = format!("{:?}", status).to_lowercase();
        conn.execute(
            "UPDATE reports SET status = ?1, resolver_id = ?2, resolved_at = datetime('now') WHERE id = ?3",
            params![s, resolver_id, id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }
}

pub struct AdminActionRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl AdminActionRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(&self, action: &AdminAction) -> Result<i64> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO admin_actions (admin_user_id, target_type, target_id, action)
             VALUES (?1, ?2, ?3, ?4)",
            params![
                action.admin_user_id,
                &action.target_type,
                &action.target_id,
                &action.action,
            ],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(conn.last_insert_rowid())
    }
}

pub struct ForwardRepo {
    conn: Arc<Mutex<rusqlite::Connection>>,
}

impl ForwardRepo {
    pub fn new(conn: Arc<Mutex<rusqlite::Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(&self, creator_user_id: i64, source_chat_id: i64, destination_chat_id: i64, review_group_id: i64, forward_message: &str, code: &str, share_mode: &str) -> Result<i64> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO forward_definitions (creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(conn.last_insert_rowid())
    }

    pub async fn get_by_code(&self, code: &str) -> Result<Option<ForwardDefinition>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode, revoked_at, created_at
             FROM forward_definitions WHERE code = ?1",
            params![code],
            |row| {
                Ok(ForwardDefinition {
                    id: row.get(0)?,
                    creator_user_id: row.get(1)?,
                    source_chat_id: row.get(2)?,
                    destination_chat_id: row.get(3)?,
                    review_group_id: row.get(4)?,
                    forward_message: row.get(5)?,
                    code: row.get(6)?,
                    share_mode: row.get(7)?,
                    revoked_at: row.get(8)?,
                    created_at: row.get(9)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn list_by_source_chat(&self, source_chat_id: i64, limit: usize, offset: usize) -> Result<Vec<ForwardDefinition>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode, revoked_at, created_at
             FROM forward_definitions WHERE source_chat_id = ?1 ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map(params![source_chat_id, limit as i64, offset as i64], |row| {
            Ok(ForwardDefinition {
                id: row.get(0)?,
                creator_user_id: row.get(1)?,
                source_chat_id: row.get(2)?,
                destination_chat_id: row.get(3)?,
                review_group_id: row.get(4)?,
                forward_message: row.get(5)?,
                code: row.get(6)?,
                share_mode: row.get(7)?,
                revoked_at: row.get(8)?,
                created_at: row.get(9)?,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut out = Vec::new();
        for r in rows {
            out.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(out)
    }

    pub async fn revoke(&self, id: i64) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE forward_definitions SET revoked_at = datetime('now') WHERE id = ?1",
            params![id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn is_allowed(&self, forward_id: i64, user_id: i64) -> Result<bool> {
        let conn = self.conn.lock().await;
        let def: Option<ForwardDefinition> = conn.query_row(
            "SELECT id, creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode, revoked_at, created_at
             FROM forward_definitions WHERE id = ?1",
            params![forward_id],
            |row| {
                Ok(ForwardDefinition {
                    id: row.get(0)?,
                    creator_user_id: row.get(1)?,
                    source_chat_id: row.get(2)?,
                    destination_chat_id: row.get(3)?,
                    review_group_id: row.get(4)?,
                    forward_message: row.get(5)?,
                    code: row.get(6)?,
                    share_mode: row.get(7)?,
                    revoked_at: row.get(8)?,
                    created_at: row.get(9)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;

        if let Some(def) = def {
            if def.creator_user_id == user_id {
                return Ok(true);
            }
            let list_entry: Option<String> = conn.query_row(
                "SELECT list_type FROM forward_lists WHERE forward_id = ?1 AND user_id = ?2",
                params![forward_id, user_id],
                |row| row.get(0),
            ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
            match def.share_mode.as_str() {
                "w" => Ok(list_entry.map(|t| t == "allow").unwrap_or(false)),
                _ => Ok(list_entry.map(|t| t != "block").unwrap_or(true)),
            }
        } else {
            Ok(false)
        }
    }

    pub async fn insert_submission(&self, forward_id: i64, user_id: i64, content_id: &str) -> Result<i64> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO forward_submissions (forward_id, user_id, content_id) VALUES (?1, ?2, ?3)",
            params![forward_id, user_id, content_id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(conn.last_insert_rowid())
    }

    pub async fn get_submission(&self, id: i64) -> Result<Option<ForwardSubmission>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, forward_id, user_id, content_id, status, review_message_id, created_at, resolved_at, resolver_id
             FROM forward_submissions WHERE id = ?1",
            params![id],
            |row| {
                Ok(ForwardSubmission {
                    id: row.get(0)?,
                    forward_id: row.get(1)?,
                    user_id: row.get(2)?,
                    content_id: ContentId::new_unchecked(row.get(3)?),
                    status: row.get(4)?,
                    review_message_id: row.get(5)?,
                    created_at: row.get(6)?,
                    resolved_at: row.get(7)?,
                    resolver_id: row.get(8)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn set_review_message_id(&self, id: i64, message_id: i32) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE forward_submissions SET review_message_id = ?1 WHERE id = ?2",
            params![message_id, id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn update_status(&self, id: i64, status: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE forward_submissions SET status = ?1, resolved_at = datetime('now') WHERE id = ?2",
            params![status, id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn get_definition(&self, id: i64) -> Result<Option<ForwardDefinition>> {
        let conn = self.conn.lock().await;
        let row = conn.query_row(
            "SELECT id, creator_user_id, source_chat_id, destination_chat_id, review_group_id, forward_message, code, share_mode, revoked_at, created_at
             FROM forward_definitions WHERE id = ?1",
            params![id],
            |row| {
                Ok(ForwardDefinition {
                    id: row.get(0)?,
                    creator_user_id: row.get(1)?,
                    source_chat_id: row.get(2)?,
                    destination_chat_id: row.get(3)?,
                    review_group_id: row.get(4)?,
                    forward_message: row.get(5)?,
                    code: row.get(6)?,
                    share_mode: row.get(7)?,
                    revoked_at: row.get(8)?,
                    created_at: row.get(9)?,
                })
            },
        ).optional().map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(row)
    }

    pub async fn add_to_list(&self, forward_id: i64, user_id: i64, list_type: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO forward_lists (forward_id, user_id, list_type) VALUES (?1, ?2, ?3) ON CONFLICT DO NOTHING",
            params![forward_id, user_id, list_type],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn remove_from_list(&self, forward_id: i64, user_id: i64, list_type: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "DELETE FROM forward_lists WHERE forward_id = ?1 AND user_id = ?2 AND list_type = ?3",
            params![forward_id, user_id, list_type],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }
}
pub struct PunishmentRepo {
    conn: Arc<Mutex<Connection>>,
}

impl PunishmentRepo {
    pub fn new(conn: Arc<Mutex<Connection>>) -> Self {
        Self { conn }
    }

    pub async fn insert(
        &self,
        chat_id: i64,
        target_user_id: i64,
        action_type: &str,
        duration_seconds: Option<i64>,
        reason: Option<&str>,
        created_by: i64,
    ) -> Result<i64> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO punishments (chat_id, target_user_id, action_type, duration_seconds, reason, created_by) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![chat_id, target_user_id, action_type, duration_seconds, reason, created_by],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(conn.last_insert_rowid())
    }

    pub async fn get_active_for_chat_target(&self, chat_id: i64, target_user_id: i64, action_type: &str) -> Result<Vec<Punishment>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, chat_id, target_user_id, action_type, duration_seconds, reason, created_by, created_at, revoked_at, revoked_by, active FROM punishments WHERE chat_id = ?1 AND target_user_id = ?2 AND action_type = ?3 AND active = 1"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map(params![chat_id, target_user_id, action_type], |row| {
            Ok(Punishment {
                id: row.get(0)?,
                chat_id: row.get(1)?,
                target_user_id: row.get(2)?,
                action_type: row.get(3)?,
                duration_seconds: row.get(4)?,
                reason: row.get(5)?,
                created_by: row.get(6)?,
                created_at: row.get(7)?,
                revoked_at: row.get(8)?,
                revoked_by: row.get(9)?,
                active: row.get::<_, i64>(10)? != 0,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut results = vec![];
        for r in rows {
            results.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(results)
    }

    pub async fn revoke(&self, id: i64, revoked_by: i64) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE punishments SET active = 0, revoked_at = datetime('now'), revoked_by = ?1 WHERE id = ?2",
            params![revoked_by, id],
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        Ok(())
    }

    pub async fn list_expired(&self) -> Result<Vec<Punishment>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, chat_id, target_user_id, action_type, duration_seconds, reason, created_by, created_at, revoked_at, revoked_by, active FROM punishments WHERE active = 1 AND duration_seconds IS NOT NULL AND datetime(created_at, '+' || duration_seconds || ' seconds') <= datetime('now')"
        ).map_err(|e| CgcxError::Database(e.to_string()))?;
        let rows = stmt.query_map([], |row| {
            Ok(Punishment {
                id: row.get(0)?,
                chat_id: row.get(1)?,
                target_user_id: row.get(2)?,
                action_type: row.get(3)?,
                duration_seconds: row.get(4)?,
                reason: row.get(5)?,
                created_by: row.get(6)?,
                created_at: row.get(7)?,
                revoked_at: row.get(8)?,
                revoked_by: row.get(9)?,
                active: row.get::<_, i64>(10)? != 0,
            })
        }).map_err(|e| CgcxError::Database(e.to_string()))?;
        let mut results = vec![];
        for r in rows {
            results.push(r.map_err(|e| CgcxError::Database(e.to_string()))?);
        }
        Ok(results)
    }
}