use chrono::{DateTime, Utc};
use rusqlite::{params, Connection, Result as SqliteResult, Row};
use serde::{Deserialize, Serialize};
use uuid::Uuid;

/// Hedra 口型合成记录状态
#[derive(Debug, Clone, PartialEq)]
#[derive(Serialize, Deserialize)]
#[serde(rename_all = "lowercase")]
pub enum HedraLipSyncStatus {
    Pending,    // 等待中
    Processing, // 处理中
    Completed,  // 已完成
    Failed,     // 失败
    Cancelled,  // 已取消
}

impl HedraLipSyncStatus {
    pub fn as_str(&self) -> &'static str {
        match self {
            HedraLipSyncStatus::Pending => "pending",
            HedraLipSyncStatus::Processing => "processing",
            HedraLipSyncStatus::Completed => "completed",
            HedraLipSyncStatus::Failed => "failed",
            HedraLipSyncStatus::Cancelled => "cancelled",
        }
    }

    pub fn from_str(s: &str) -> Self {
        match s {
            "pending" => HedraLipSyncStatus::Pending,
            "processing" => HedraLipSyncStatus::Processing,
            "completed" => HedraLipSyncStatus::Completed,
            "failed" => HedraLipSyncStatus::Failed,
            "cancelled" => HedraLipSyncStatus::Cancelled,
            _ => HedraLipSyncStatus::Pending,
        }
    }
}

/// Hedra 口型合成记录
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct HedraLipSyncRecord {
    pub id: String,
    pub task_id: Option<String>,
    pub image_path: String,                    // 原始图片路径
    pub image_url: Option<String>,             // 上传后的图片URL
    pub audio_path: String,                    // 原始音频路径
    pub audio_url: Option<String>,             // 上传后的音频URL
    pub prompt: Option<String>,                // 提示词（如果有的话）
    pub status: HedraLipSyncStatus,
    pub progress: f32,
    pub result_video_url: Option<String>,      // 生成的视频URL
    pub result_video_path: Option<String>,     // 下载后的视频本地路径
    pub error_message: Option<String>,
    pub created_at: DateTime<Utc>,
    pub started_at: Option<DateTime<Utc>>,
    pub completed_at: Option<DateTime<Utc>>,
    pub duration_ms: Option<i64>,
    pub file_size_bytes: Option<i64>,          // 生成视频文件大小
    pub video_duration_seconds: Option<f32>,   // 视频时长
}

impl HedraLipSyncRecord {
    /// 创建新的 Hedra 口型合成记录
    pub fn new(
        image_path: String,
        audio_path: String,
        prompt: Option<String>,
    ) -> Self {
        Self {
            id: Uuid::new_v4().to_string(),
            task_id: None,
            image_path,
            image_url: None,
            audio_path,
            audio_url: None,
            prompt,
            status: HedraLipSyncStatus::Pending,
            progress: 0.0,
            result_video_url: None,
            result_video_path: None,
            error_message: None,
            created_at: Utc::now(),
            started_at: None,
            completed_at: None,
            duration_ms: None,
            file_size_bytes: None,
            video_duration_seconds: None,
        }
    }

    /// 开始处理
    pub fn start_processing(&mut self, task_id: String) {
        self.task_id = Some(task_id);
        self.status = HedraLipSyncStatus::Processing;
        self.started_at = Some(Utc::now());
    }

    /// 设置上传的文件URL
    pub fn set_uploaded_urls(&mut self, image_url: String, audio_url: String) {
        self.image_url = Some(image_url);
        self.audio_url = Some(audio_url);
    }

    /// 更新进度
    pub fn update_progress(&mut self, progress: f32) {
        self.progress = progress;
        if progress > 0.0 && self.status == HedraLipSyncStatus::Pending {
            self.status = HedraLipSyncStatus::Processing;
        }
    }

    /// 完成处理
    pub fn complete_processing(
        &mut self,
        result_video_url: String,
        file_size_bytes: Option<i64>,
        video_duration_seconds: Option<f32>,
    ) {
        self.status = HedraLipSyncStatus::Completed;
        self.progress = 1.0;
        self.result_video_url = Some(result_video_url);
        self.file_size_bytes = file_size_bytes;
        self.video_duration_seconds = video_duration_seconds;
        self.completed_at = Some(Utc::now());
        
        if let Some(started_at) = self.started_at {
            self.duration_ms = Some((Utc::now() - started_at).num_milliseconds());
        }
    }

    /// 设置下载后的本地视频路径
    pub fn set_local_video_path(&mut self, local_path: String) {
        self.result_video_path = Some(local_path);
    }

    /// 失败处理
    pub fn fail_processing(&mut self, error_message: String) {
        self.status = HedraLipSyncStatus::Failed;
        self.error_message = Some(error_message);
        self.completed_at = Some(Utc::now());
        
        if let Some(started_at) = self.started_at {
            self.duration_ms = Some((Utc::now() - started_at).num_milliseconds());
        }
    }

    /// 取消处理
    pub fn cancel_processing(&mut self) {
        self.status = HedraLipSyncStatus::Cancelled;
        self.completed_at = Some(Utc::now());
        
        if let Some(started_at) = self.started_at {
            self.duration_ms = Some((Utc::now() - started_at).num_milliseconds());
        }
    }

    /// 获取图片文件名
    pub fn get_image_filename(&self) -> String {
        std::path::Path::new(&self.image_path)
            .file_name()
            .and_then(|name| name.to_str())
            .unwrap_or("unknown.jpg")
            .to_string()
    }

    /// 获取音频文件名
    pub fn get_audio_filename(&self) -> String {
        std::path::Path::new(&self.audio_path)
            .file_name()
            .and_then(|name| name.to_str())
            .unwrap_or("unknown.mp3")
            .to_string()
    }

    /// 从数据库行创建记录
    pub fn from_row(row: &Row) -> SqliteResult<Self> {
        let created_at_str: String = row.get("created_at")?;
        let created_at = DateTime::parse_from_rfc3339(&created_at_str)
            .map_err(|_| rusqlite::Error::InvalidColumnType(0, "created_at".to_string(), rusqlite::types::Type::Text))?
            .with_timezone(&Utc);

        let started_at_str: Option<String> = row.get("started_at")?;
        let started_at = started_at_str
            .and_then(|s| DateTime::parse_from_rfc3339(&s).ok())
            .map(|dt| dt.with_timezone(&Utc));

        let completed_at_str: Option<String> = row.get("completed_at")?;
        let completed_at = completed_at_str
            .and_then(|s| DateTime::parse_from_rfc3339(&s).ok())
            .map(|dt| dt.with_timezone(&Utc));

        Ok(Self {
            id: row.get("id")?,
            task_id: row.get("task_id")?,
            image_path: row.get("image_path")?,
            image_url: row.get("image_url")?,
            audio_path: row.get("audio_path")?,
            audio_url: row.get("audio_url")?,
            prompt: row.get("prompt")?,
            status: HedraLipSyncStatus::from_str(&row.get::<_, String>("status")?),
            progress: row.get("progress")?,
            result_video_url: row.get("result_video_url")?,
            result_video_path: row.get("result_video_path")?,
            error_message: row.get("error_message")?,
            created_at,
            started_at,
            completed_at,
            duration_ms: row.get("duration_ms")?,
            file_size_bytes: row.get("file_size_bytes")?,
            video_duration_seconds: row.get("video_duration_seconds")?,
        })
    }

    /// 保存到数据库
    pub fn save(&self, conn: &Connection) -> SqliteResult<()> {
        conn.execute(
            r#"
            INSERT OR REPLACE INTO hedra_lipsync_records (
                id, task_id, image_path, image_url, audio_path, audio_url,
                prompt, status, progress, result_video_url, result_video_path,
                error_message, created_at, started_at, completed_at, duration_ms,
                file_size_bytes, video_duration_seconds
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18)
            "#,
            params![
                self.id,
                self.task_id,
                self.image_path,
                self.image_url,
                self.audio_path,
                self.audio_url,
                self.prompt,
                self.status.as_str(),
                self.progress,
                self.result_video_url,
                self.result_video_path,
                self.error_message,
                self.created_at.to_rfc3339(),
                self.started_at.map(|dt| dt.to_rfc3339()),
                self.completed_at.map(|dt| dt.to_rfc3339()),
                self.duration_ms,
                self.file_size_bytes,
                self.video_duration_seconds,
            ],
        )?;

        Ok(())
    }

    /// 根据ID查找记录
    pub fn find_by_id(conn: &Connection, id: &str) -> SqliteResult<Option<Self>> {
        let mut stmt = conn.prepare(
            "SELECT * FROM hedra_lipsync_records WHERE id = ?1"
        )?;

        let mut rows = stmt.query_map(params![id], Self::from_row)?;

        match rows.next() {
            Some(row) => Ok(Some(row?)),
            None => Ok(None),
        }
    }

    /// 根据任务ID查找记录
    pub fn find_by_task_id(conn: &Connection, task_id: &str) -> SqliteResult<Option<Self>> {
        let mut stmt = conn.prepare(
            "SELECT * FROM hedra_lipsync_records WHERE task_id = ?1"
        )?;

        let mut rows = stmt.query_map(params![task_id], Self::from_row)?;

        match rows.next() {
            Some(row) => Ok(Some(row?)),
            None => Ok(None),
        }
    }

    /// 获取所有记录（按创建时间倒序）
    pub fn get_all(conn: &Connection, limit: Option<i32>) -> SqliteResult<Vec<Self>> {
        let sql = if let Some(limit) = limit {
            format!("SELECT * FROM hedra_lipsync_records ORDER BY created_at DESC LIMIT {}", limit)
        } else {
            "SELECT * FROM hedra_lipsync_records ORDER BY created_at DESC".to_string()
        };

        let mut stmt = conn.prepare(&sql)?;
        let rows = stmt.query_map([], Self::from_row)?;

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

        Ok(records)
    }

    /// 根据状态获取记录
    pub fn get_by_status(conn: &Connection, status: HedraLipSyncStatus) -> SqliteResult<Vec<Self>> {
        let mut stmt = conn.prepare(
            "SELECT * FROM hedra_lipsync_records WHERE status = ?1 ORDER BY created_at DESC"
        )?;

        let rows = stmt.query_map(params![status.as_str()], Self::from_row)?;

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

        Ok(records)
    }

    /// 删除记录
    pub fn delete(&self, conn: &Connection) -> SqliteResult<()> {
        conn.execute(
            "DELETE FROM hedra_lipsync_records WHERE id = ?1",
            params![self.id],
        )?;
        Ok(())
    }
}

/// 创建 Hedra 口型合成记录表
pub fn create_table(conn: &Connection) -> SqliteResult<()> {
    conn.execute(
        r#"
        CREATE TABLE IF NOT EXISTS hedra_lipsync_records (
            id TEXT PRIMARY KEY,
            task_id TEXT,
            image_path TEXT NOT NULL,
            image_url TEXT,
            audio_path TEXT NOT NULL,
            audio_url TEXT,
            prompt TEXT,
            status TEXT NOT NULL DEFAULT 'pending',
            progress REAL NOT NULL DEFAULT 0.0,
            result_video_url TEXT,
            result_video_path TEXT,
            error_message TEXT,
            created_at TEXT NOT NULL,
            started_at TEXT,
            completed_at TEXT,
            duration_ms INTEGER,
            file_size_bytes INTEGER,
            video_duration_seconds REAL
        )
        "#,
        [],
    )?;

    // 创建索引
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_hedra_lipsync_records_task_id ON hedra_lipsync_records(task_id)",
        [],
    )?;

    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_hedra_lipsync_records_created_at ON hedra_lipsync_records(created_at)",
        [],
    )?;

    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_hedra_lipsync_records_status ON hedra_lipsync_records(status)",
        [],
    )?;

    Ok(())
}
