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

/// 系统音色类型
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
#[serde(rename_all = "lowercase")]
pub enum SystemVoiceType {
    /// 系统预设音色
    System,
    /// 精品音色
    Premium,
    /// 童声音色
    Child,
    /// 角色音色
    Character,
    /// 节日音色
    Holiday,
    /// 英文音色
    English,
}

impl SystemVoiceType {
    pub fn from_str(s: &str) -> Self {
        match s {
            "system" => SystemVoiceType::System,
            "premium" => SystemVoiceType::Premium,
            "child" => SystemVoiceType::Child,
            "character" => SystemVoiceType::Character,
            "holiday" => SystemVoiceType::Holiday,
            "english" => SystemVoiceType::English,
            _ => SystemVoiceType::System,
        }
    }

    pub fn to_string(&self) -> String {
        match self {
            SystemVoiceType::System => "system".to_string(),
            SystemVoiceType::Premium => "premium".to_string(),
            SystemVoiceType::Child => "child".to_string(),
            SystemVoiceType::Character => "character".to_string(),
            SystemVoiceType::Holiday => "holiday".to_string(),
            SystemVoiceType::English => "english".to_string(),
        }
    }
}

/// 音色性别
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
#[serde(rename_all = "lowercase")]
pub enum VoiceGender {
    Male,
    Female,
    Child,
    Other,
}

impl VoiceGender {
    pub fn from_str(s: &str) -> Self {
        match s {
            "male" => VoiceGender::Male,
            "female" => VoiceGender::Female,
            "child" => VoiceGender::Child,
            "other" => VoiceGender::Other,
            _ => VoiceGender::Other,
        }
    }

    pub fn to_string(&self) -> String {
        match self {
            VoiceGender::Male => "male".to_string(),
            VoiceGender::Female => "female".to_string(),
            VoiceGender::Child => "child".to_string(),
            VoiceGender::Other => "other".to_string(),
        }
    }
}

/// 系统音色实体模型
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SystemVoice {
    pub id: String,
    pub voice_id: String,
    pub voice_name: String,
    pub voice_name_en: Option<String>,
    pub description: Option<String>,
    pub voice_type: SystemVoiceType,
    pub gender: VoiceGender,
    pub language: String,
    pub is_active: bool,
    pub sort_order: i32,
    pub created_at: String,
    pub updated_at: String,
}

/// 创建系统音色请求
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct CreateSystemVoiceRequest {
    pub voice_id: String,
    pub voice_name: String,
    pub voice_name_en: Option<String>,
    pub description: Option<String>,
    pub voice_type: SystemVoiceType,
    pub gender: VoiceGender,
    pub language: String,
    pub sort_order: i32,
}

impl SystemVoice {
    /// 从数据库行创建实例
    pub fn from_row(row: &Row) -> SqliteResult<Self> {
        Ok(SystemVoice {
            id: row.get("id")?,
            voice_id: row.get("voice_id")?,
            voice_name: row.get("voice_name")?,
            voice_name_en: row.get("voice_name_en")?,
            description: row.get("description")?,
            voice_type: SystemVoiceType::from_str(&row.get::<_, String>("voice_type")?),
            gender: VoiceGender::from_str(&row.get::<_, String>("gender")?),
            language: row.get("language")?,
            is_active: row.get("is_active")?,
            sort_order: row.get("sort_order")?,
            created_at: row.get("created_at")?,
            updated_at: row.get("updated_at")?,
        })
    }

    /// 创建新的系统音色
    pub fn create(conn: &Connection, request: CreateSystemVoiceRequest) -> SqliteResult<SystemVoice> {
        let id = Uuid::new_v4().to_string();
        let now = chrono::Utc::now().to_rfc3339();

        conn.execute(
            r#"
            INSERT INTO system_voices (
                id, voice_id, voice_name, voice_name_en, description,
                voice_type, gender, language, is_active, sort_order,
                created_at, updated_at
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
            "#,
            (
                &id,
                &request.voice_id,
                &request.voice_name,
                &request.voice_name_en,
                &request.description,
                &request.voice_type.to_string(),
                &request.gender.to_string(),
                &request.language,
                true,
                &request.sort_order,
                &now,
                &now,
            ),
        )?;

        Ok(SystemVoice {
            id,
            voice_id: request.voice_id,
            voice_name: request.voice_name,
            voice_name_en: request.voice_name_en,
            description: request.description,
            voice_type: request.voice_type,
            gender: request.gender,
            language: request.language,
            is_active: true,
            sort_order: request.sort_order,
            created_at: now.clone(),
            updated_at: now,
        })
    }

    /// 获取所有活跃的系统音色
    pub fn get_all_active(conn: &Connection) -> SqliteResult<Vec<SystemVoice>> {
        let mut stmt = conn.prepare(
            r#"
            SELECT id, voice_id, voice_name, voice_name_en, description,
                   voice_type, gender, language, is_active, sort_order,
                   created_at, updated_at
            FROM system_voices
            WHERE is_active = 1
            ORDER BY sort_order ASC, voice_name ASC
            "#,
        )?;

        let voice_iter = stmt.query_map([], |row| SystemVoice::from_row(row))?;
        let mut voices = Vec::new();

        for voice in voice_iter {
            voices.push(voice?);
        }

        Ok(voices)
    }

    /// 根据类型获取系统音色
    pub fn get_by_type(conn: &Connection, voice_type: SystemVoiceType) -> SqliteResult<Vec<SystemVoice>> {
        let mut stmt = conn.prepare(
            r#"
            SELECT id, voice_id, voice_name, voice_name_en, description,
                   voice_type, gender, language, is_active, sort_order,
                   created_at, updated_at
            FROM system_voices
            WHERE voice_type = ?1 AND is_active = 1
            ORDER BY sort_order ASC, voice_name ASC
            "#,
        )?;

        let voice_iter = stmt.query_map([voice_type.to_string()], |row| SystemVoice::from_row(row))?;
        let mut voices = Vec::new();

        for voice in voice_iter {
            voices.push(voice?);
        }

        Ok(voices)
    }

    /// 根据voice_id获取系统音色
    pub fn get_by_voice_id(conn: &Connection, voice_id: &str) -> SqliteResult<Option<SystemVoice>> {
        let mut stmt = conn.prepare(
            r#"
            SELECT id, voice_id, voice_name, voice_name_en, description,
                   voice_type, gender, language, is_active, sort_order,
                   created_at, updated_at
            FROM system_voices
            WHERE voice_id = ?1 AND is_active = 1
            "#,
        )?;

        let mut rows = stmt.query_map([voice_id], |row| SystemVoice::from_row(row))?;

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

    /// 批量插入系统音色
    pub fn batch_insert(conn: &Connection, voices: Vec<CreateSystemVoiceRequest>) -> SqliteResult<()> {
        let tx = conn.unchecked_transaction()?;
        
        for voice_request in voices {
            let id = Uuid::new_v4().to_string();
            let now = chrono::Utc::now().to_rfc3339();

            tx.execute(
                r#"
                INSERT OR IGNORE INTO system_voices (
                    id, voice_id, voice_name, voice_name_en, description,
                    voice_type, gender, language, is_active, sort_order,
                    created_at, updated_at
                ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
                "#,
                (
                    &id,
                    &voice_request.voice_id,
                    &voice_request.voice_name,
                    &voice_request.voice_name_en,
                    &voice_request.description,
                    &voice_request.voice_type.to_string(),
                    &voice_request.gender.to_string(),
                    &voice_request.language,
                    true,
                    &voice_request.sort_order,
                    &now,
                    &now,
                ),
            )?;
        }

        tx.commit()?;
        Ok(())
    }
}

/// 创建系统音色表
pub fn create_table(conn: &Connection) -> SqliteResult<()> {
    conn.execute(
        r#"
        CREATE TABLE IF NOT EXISTS system_voices (
            id TEXT PRIMARY KEY,
            voice_id TEXT NOT NULL UNIQUE,
            voice_name TEXT NOT NULL,
            voice_name_en TEXT,
            description TEXT,
            voice_type TEXT NOT NULL,
            gender TEXT NOT NULL,
            language TEXT NOT NULL DEFAULT 'zh-CN',
            is_active BOOLEAN NOT NULL DEFAULT 1,
            sort_order INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        "#,
        [],
    )?;

    // 创建索引
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_system_voices_voice_id ON system_voices(voice_id)",
        [],
    )?;

    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_system_voices_type ON system_voices(voice_type)",
        [],
    )?;

    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_system_voices_active ON system_voices(is_active)",
        [],
    )?;

    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_system_voices_sort ON system_voices(sort_order)",
        [],
    )?;

    Ok(())
}
