//! # db
//!
//! æ•°æ�®ã€�æ•°æ�®åº“æœ‰å…³æ“�ä½œ
//!
//! å�Ÿå§‹æ•°æ�®çš„å¤„ç�†ã€�å»ºç«‹æ•°æ�®åº“è„šæœ¬æ–‡ä»¶ã€‚è¯·ä¿�ç•™ä»¥ä¾›æœªæ�¥å�‚è€ƒ
//!
//! ä½¿ç”¨ sqlite - å…³ç³»å�‹æ•°æ�®åº“ï¼Œå¼±ç±»å�‹
//!
//! ã€�å®¢ä½“è¡¨ã€‘objects
//! _å­¦æ ¡ç±»åˆ« < _å­¦æ ¡ < _å­¦é™¢ < _å¯¼å¸ˆ - _æ—¥æœŸ - _ä¿¡æ�¯ - object (key)
//!           | åŒ…å�«å­¦é™¢æœ¬èº« self ä¸‹å�Œ
//! - school_cate TEXT NOT NULL,
//! - university TEXT NOT NULL,
//! - department TEXT NOT NULL,
//! - supervisor TEXT NOT NULL,
//! - date TEXT NOT NULL,
//! - info TEXT,
//! - object TEXT NOT NULL,
//! - PRIMARY KEY (object)
//! objectï¼šä»…åœ¨ç¬¬ä¸€æ¬¡æ·»åŠ å®¢ä½“æ—¶è®¡ç®—ï¼Œæ‰€ä»¥å…¶ä»–å­—æ®µä¹Ÿå�¯æ˜¯å�¯å�˜çš„
//! sha256( å­¦æ ¡ | å­¦é™¢ | å¯¼å¸ˆ )[:8byte]
//!
//! ã€�è¯„ä»·è¡¨ã€‘comments
//! object < è¯„ä»· - æ—¥æœŸ - _æ�¥æº�åˆ†ç±» - _è¯„ä»·ç±»å�‹ - å�‘å¸ƒäººç­¾å�� - è¯„ä»· id (key)
//! - object TEXT NOT NULL,
//! - description TEXT NOT NULL,
//! - date TEXT NOT NULL,
//! - source_cate TEXT NOT NULL,
//! - type TEXT NOT NULL,
//! - author_sign TEXT,
//! - id TEXT NOT NULL,
//!
//! `_` è¡¨ç¤ºå��ç»­å�¯å�˜
//! æ�¥æº�åˆ†ç±»ï¼šadmin, urfire, telegram...
//! è¯„ä»·ç±»å�‹ï¼šnestï¼ˆè¯„ä»·çš„è¯„ä»·ï¼‰, teacher, course, student, unity, infoï¼ˆwiki_likeï¼‰ ...
//! è¯„ä»· id = sha256( object | è¯„ä»· | æ—¥æœŸ )[:8byte] æ³¨æ„�ï¼Œè¿™ä¸ªä¹ŸåŒ…å�«å�»é‡�çš„æ€§è´¨
//! å�‘å¸ƒäººç­¾å�� å�¯ä¸ºç©º = sha256( è¯„ä»· id | sha256(salt + å�‘å¸ƒäººä¸€æ¬¡æ€§å¯†è¯­).hex )
//! salt: SAFC_salt
//!
//!
//! TODO è½¬æ�¢ä¸ºä¸¥æ ¼çš„å…³ç³»å�‹æ•°æ�®åº“ï¼Œç›®å‰�ä¸ºäº†æ•�æ�·å¼€å�‘ï¼Œä½¿ç”¨ ~2NF
//!
//! TODO å¤‡ä»½ä¸�å�‘å¸ƒ
//!
//! TODO åŒºå�—é“¾ã€�åˆ†å¸ƒå¼�æ•°æ�®åº“ï¼Ÿ- åŸºäº� telegram é€šè®¯
//!
//! TODO å�‚è€ƒï¼š
//! https://course.rs/advance/errors.html - å½’ä¸€åŒ–ä¸�å�Œçš„é”™è¯¯ç±»å�‹
//!

use crate::sec::*;
use rusqlite::params;
use serde::{Deserialize, Serialize};
use std::str::FromStr;
use strum_macros::{Display, EnumString};

use chrono;

use r2d2_sqlite::{self, SqliteConnectionManager};

pub type Pool = r2d2::Pool<r2d2_sqlite::SqliteConnectionManager>;
type HandlerResult<T> = Result<T, Box<dyn std::error::Error + Send + Sync>>;
// type HandlerResult<T, E = Box<dyn std::error::Error + Send + Sync>> > = std::result::Result<T, E>;

/// æ•°æ�®æ�¥æº�åˆ†ç±»
#[derive(Debug, EnumString, Display, PartialEq, Clone, Serialize, Deserialize)]
#[strum(serialize_all = "lowercase")]
pub enum SourceCate {
    /// ç®¡ç�†å‘˜æ‰‹åŠ¨æ·»åŠ 
    Admin,
    /// æ�¥è‡ª Urfire æ•°æ�®
    /// æ•°æ�®æ�¥æº�ï¼šhttps://gitee.com/wdwdwd123/RateMySupervisor
    /// è¿™ä¸ªä»“åº“å�¯èƒ½ fork from https://github.com/pengp25/RateMySupervisor ä¸”æ›´æ–°ä¸€ç‚¹
    Urfire,
    /// æ�¥è‡ª SAFC çš„ telegram å¹³å�°æ•°æ�®
    Telegram,
    /// æ�¥è‡ª SAFC çš„ web å¹³å�°æ•°æ�®ï¼ˆæš‚æ— ï¼Œå¹³å�°æœªæ��ä¾›ï¼‰
    Web,
    /// æ�¥è‡ª pi-review.com æ•°æ�® æˆªæ­¢è‡³ 2023-09-26
    PiReview,
}

pub enum Obj {
    /// å¯¼å¸ˆ...ç±»å®¢ä½“
    Object(ObjTeacher),
    /// è¯„è®ºç±»å®¢ä½“
    Comment(ObjComment),
}

/// å¯¹åº”æ•°æ�®åº“ä¸­çš„ã€�å®¢ä½“è¡¨ï¼ˆä¸»è¦�æ˜¯å¯¼å¸ˆï¼‰ã€‘objects
/// å�ªæ˜¯ teacher-likeï¼Œå®¢ä½“è¡¨çš„ objectï¼Œä¸�ä¸€å®šå�ªæ˜¯æŒ‡å¯¼å¸ˆ
#[derive(Clone, Serialize, Deserialize, Debug)]
pub struct ObjTeacher {
    pub school_cate: String,
    pub university: String,
    pub department: String,
    pub supervisor: String,
    pub date: String,
    pub info: Option<String>,
    pub object_id: String, // æ­¤å®¢ä½“çš„ id
}

impl ObjTeacher {
    pub fn display_path(&self) -> String {
        format!(
            "ğŸ§­ {} ğŸ�« {} ğŸ�¢ {} ğŸ‘” {}",
            self.school_cate, self.university, self.department, self.supervisor
        )
    }
}

/// è¯„ä»·ç±»å�‹ï¼šnestï¼ˆè¯„ä»·çš„è¯„ä»·ï¼‰, teacher, course, student, unity, infoï¼ˆwiki_likeï¼‰
#[derive(Debug, EnumString, Display, PartialEq, Clone, Deserialize, Serialize)]
#[strum(serialize_all = "lowercase")]
pub enum CommentType {
    Nest,
    Teacher,
    Course,
    Student,
    Unity,
    Info,
}

/// å¯¹åº”æ•°æ�®åº“ä¸­çš„ã€�è¯„ä»·è¡¨ã€‘comments
#[derive(Clone, Serialize, Deserialize, Debug)]
pub struct ObjComment {
    pub object: String,
    pub description: String,
    pub date: String,
    pub source_cate: SourceCate,
    pub comment_type: CommentType,
    pub author_sign: Option<String>,
    pub id: String,
}

impl ObjComment {
    pub fn new_with_otp(
        object_id: String,
        comment: String,
        source_cate: SourceCate,
        comment_type: CommentType,
        otp: String,
    ) -> Self {
        let date = get_current_date();
        let id = hash_comment_id(&object_id, &comment, &date);
        let author_sign = Some(hash_author_sign(&id, &otp));
        ObjComment {
            object: object_id,
            description: comment,
            date,
            source_cate,
            comment_type,
            author_sign,
            id,
        }
    }
}

pub struct SAFCdb {
    pool: Pool,
}

impl Clone for SAFCdb {
    fn clone(&self) -> Self {
        Self {
            pool: self.pool.clone(),
        }
    }
}

impl Default for SAFCdb {
    fn default() -> Self {
        Self::new()
    }
}

impl SAFCdb {
    pub fn new() -> Self {
        // TODO æ£€æŸ¥å½“å‰�æ–‡ä»¶æ˜¯å�¦å­˜åœ¨
        let db_path = std::env::var("SAFC_DB_PATH").unwrap_or_else(|_| {
            log::warn!("SAFC_DB_PATH æœªè®¾ç½®ï¼Œé»˜è®¤è®¾ç½®ä¸ºï¼š./db.sqlite");
            "db.sqlite".to_string()
        });

        Self::new_with_path(db_path)
    }

    pub fn new_with_path(db_path: String) -> Self {
        let manager = SqliteConnectionManager::file(db_path);
        let pool = Pool::new(manager).unwrap();
        SAFCdb { pool }
    }

    pub fn find_school_cate(&self) -> HandlerResult<Vec<String>> {
        let conn = self.pool.clone().get()?;
        let mut stmt = conn.prepare("SELECT DISTINCT school_cate FROM objects")?;
        let rows = stmt.query_map([], |row| row.get::<usize, String>(0))?;

        // rows.collect::<Result<Vec<_>, _>>() // ? å�¯ä»¥å°†é”™è¯¯è¿›è¡Œéš�å¼�çš„å¼ºåˆ¶è½¬æ�¢
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    pub fn find_university(&self, s_c: &String) -> HandlerResult<Vec<String>> {
        let conn = self.pool.clone().get()?;

        let mut stmt =
            conn.prepare("SELECT DISTINCT university FROM objects WHERE school_cate=(?1)")?;
        let rows = stmt.query_map([s_c], |row| row.get(0))?;

        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    pub fn find_department(&self, s_c: &String, university: &String) -> HandlerResult<Vec<String>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare(
            "SELECT DISTINCT department FROM objects WHERE \
        school_cate=(?1) AND university=(?2)",
        )?;
        let rows = stmt.query_map([s_c, university], |row| row.get::<_, String>(0))?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    pub fn find_supervisor(
        &self,
        s_c: &String,
        university: &String,
        department: &String,
    ) -> HandlerResult<Vec<String>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare(
            "SELECT DISTINCT supervisor FROM objects WHERE \
        school_cate=(?1) AND university=(?2)  AND department=(?3)",
        )?;
        let rows = stmt.query_map([s_c, university, department], |row| row.get::<_, String>(0))?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    /// æ¨¡ç³Šæ�œç´¢
    /// ç™¾åˆ†å�·ï¼ˆ%ï¼‰ä»£è¡¨é›¶ä¸ªã€�ä¸€ä¸ªæˆ–å¤šä¸ªå­—ç¬¦ã€‚ä¸‹åˆ’çº¿ï¼ˆ_ï¼‰ä»£è¡¨ä¸€ä¸ªå�•ä¸€çš„å­—ç¬¦ã€‚è¿™äº›ç¬¦å�·å�¯ä»¥è¢«ç»„å�ˆä½¿ç”¨ã€‚
    ///
    /// è¿”å›�æ�œåˆ°çš„ [`ObjTeacher`] åˆ—è¡¨ï¼Œå�¯ä¸ºç©º
    pub fn find_supervisor_like(&self, s: &String) -> HandlerResult<Vec<ObjTeacher>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare(
            "SELECT * FROM objects WHERE \
            supervisor LIKE (?1)",
        )?;
        // let rows = stmt.query_map([s], |row| row.get(0))?;
        let rows = stmt.query_map([s], |row| {
            Ok(ObjTeacher {
                school_cate: row.get::<_, String>(0)?,
                university: row.get::<_, String>(1)?,
                department: row.get::<_, String>(2)?,
                supervisor: row.get::<_, String>(3)?,
                date: row.get::<_, String>(4)?,
                info: row.get::<_, String>(5).ok(),
                object_id: row.get::<_, String>(6)?,
            })
        })?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    /// æŸ¥æ‰¾å®¢ä½“ ç”¨è·¯å¾„çš„æ–¹å¼�
    /// ã€�å®¢ä½“è¡¨ã€‘objects  _å­¦æ ¡ç±»åˆ« < å­¦æ ¡ < å­¦é™¢ < å¯¼å¸ˆ - _æ—¥æœŸ - _ä¿¡æ�¯ - object (key)
    /// - school_cate TEXT NOT NULL,
    /// - university TEXT NOT NULL,
    /// - department TEXT NOT NULL,
    /// - supervisor TEXT NOT NULL,
    /// - date TEXT NOT NULL,
    /// - info TEXT,
    /// - object TEXT NOT NULL,
    /// - PRIMARY KEY (object)
    pub fn find_object_with_path(
        &self,
        university: &String,
        department: &String,
        supervisor: &String,
    ) -> HandlerResult<Option<ObjTeacher>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare(
            "SELECT * FROM objects WHERE \
        supervisor=(?1) AND university=(?2) AND department=(?3)",
        )?;

        let rows = stmt.query_map([supervisor, university, department], |row| {
            Ok(ObjTeacher {
                school_cate: row.get::<_, String>(0)?,
                university: row.get::<_, String>(1)?,
                department: row.get::<_, String>(2)?,
                supervisor: row.get::<_, String>(3)?,
                date: row.get::<_, String>(4)?,
                info: row.get::<_, String>(5).ok(),
                object_id: row.get::<_, String>(6)?,
            })
        })?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?.first().cloned())
    }

    /// æŸ¥æ‰¾å®¢ä½“ ç”¨ id çš„æ–¹å¼�
    /// ã€�å®¢ä½“è¡¨ã€‘objects  _å­¦æ ¡ç±»åˆ« < å­¦æ ¡ < å­¦é™¢ < å¯¼å¸ˆ - _æ—¥æœŸ - _ä¿¡æ�¯ - object (key)
    pub fn find_objteacher_with_id(&self, object_id: &str) -> HandlerResult<Option<ObjTeacher>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare("SELECT * FROM objects WHERE object=(?1)")?;

        let rows = stmt.query_map([object_id], |row| {
            Ok(ObjTeacher {
                school_cate: row.get::<_, String>(0)?,
                university: row.get::<_, String>(1)?,
                department: row.get::<_, String>(2)?,
                supervisor: row.get::<_, String>(3)?,
                date: row.get::<_, String>(4)?,
                info: row.get::<_, String>(5).ok(),
                object_id: row.get::<_, String>(6)?,
            })
        })?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?.first().cloned())
    }

    /// object æ˜¯å�¦å­˜åœ¨
    /// TODO é‡�æ�„ï¼šæ•°æ�®åº“åŠ å…¥å®¢ä½“çš„ç§�ç±»
    pub fn if_object_exists(&self, object: &str) -> HandlerResult<Option<CommentType>> {
        let conn = self.pool.clone().get()?;

        let exists1: bool = conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM objects WHERE object = ?1)",
            [object],
            |row| row.get(0),
        )?;

        if exists1 {
            return Ok(Some(CommentType::Teacher));
        }

        let exists2: bool = conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM comments WHERE id = ?1)",
            [object],
            |row| row.get(0),
        )?;

        if exists2 {
            return Ok(Some(CommentType::Nest));
        }

        Ok(None)
    }

    /// é€šè¿‡è¯„ä»·å¯¹è±¡ `object_id` æŸ¥æ‰¾è¯„ä»·
    ///
    /// ã€�è¯„ä»·è¡¨ã€‘comments : object < è¯„ä»· - æ—¥æœŸ - _æ�¥æº�åˆ†ç±» - _è¯„ä»·ç±»å�‹ - å�‘å¸ƒäººç­¾å�� - è¯„ä»· id (key)
    /// - object TEXT NOT NULL,
    /// - description TEXT NOT NULL,
    /// - date TEXT NOT NULL,
    /// - source_cate TEXT NOT NULL,
    /// - type TEXT NOT NULL,
    /// - author_sign TEXT,
    /// - id TEXT NOT NULL,
    ///
    /// è¿”å›� [`ObjComment`]
    pub fn find_comment(&self, object_id: &String) -> HandlerResult<Vec<ObjComment>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare("SELECT * FROM comments WHERE object=? ")?;
        let rows = stmt.query_map([object_id], |row| {
            Ok(ObjComment {
                object: row.get::<_, String>(0)?,
                description: row.get::<_, String>(1)?,
                date: row.get::<_, String>(2)?,
                source_cate: SourceCate::from_str(row.get::<_, String>(3)?.as_str()).unwrap(),
                comment_type: CommentType::from_str(row.get::<_, String>(4)?.as_str()).unwrap(),
                author_sign: row.get::<_, String>(5).ok(),
                id: row.get::<_, String>(6)?,
            })
        })?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    /// æŸ¥æ‰¾è¯„ä»· - like æ–¹å¼�
    pub fn find_comment_like(&self, s: &String) -> HandlerResult<Vec<ObjComment>> {
        let conn = self.pool.clone().get()?;

        let mut stmt = conn.prepare("SELECT * FROM comments WHERE description LIKE ? ")?;
        let rows = stmt.query_map([s], |row| {
            Ok(ObjComment {
                object: row.get::<_, String>(0)?,
                description: row.get::<_, String>(1)?,
                date: row.get::<_, String>(2)?,
                source_cate: SourceCate::from_str(row.get::<_, String>(3)?.as_str()).unwrap(),
                comment_type: CommentType::from_str(row.get::<_, String>(4)?.as_str()).unwrap(),
                author_sign: match row.get::<_, String>(5) {
                    Ok(s) => Some(s),
                    Err(_) => None,
                },
                id: row.get::<_, String>(6)?,
            })
        })?;
        Ok(rows.collect::<Result<Vec<_>, _>>()?)
    }

    /// å¢�åŠ è¯„ä»·å®¢ä½“ï¼Œæœ‰ä¸€äº›å€¼åœ¨å‡½æ•°å†…è®¡ç®—
    pub fn add_object(&self, obj_teacher: &ObjTeacher) -> HandlerResult<()> {
        let conn = self.pool.clone().get()?;
        conn.execute(
            "INSERT INTO objects (school_cate, university, department, supervisor, date, info, object) 
        VALUES (?, ?, ?, ?, ?, ?, ?)",
            params![
                obj_teacher.school_cate,
                obj_teacher.university,
                obj_teacher.department,
                obj_teacher.supervisor,
                obj_teacher.date,
                obj_teacher.info,
                obj_teacher.object_id
            ],
        )?;

        Ok(())
    }

    /// å¢�åŠ è¯„ä»·
    pub fn add_comment(&self, obj_comment: &ObjComment) -> HandlerResult<()> {
        let conn = self.pool.clone().get()?;
        conn.execute(
            "INSERT INTO comments
        (object, description, date, source_cate, type, author_sign, id)
        VALUES (?, ?, ?, ?, ?, ?, ?)",
            params![
                obj_comment.object,
                obj_comment.description,
                obj_comment.date,
                obj_comment.source_cate.to_string(),
                obj_comment.comment_type.to_string(),
                obj_comment.author_sign,
                obj_comment.id
            ],
        )?;

        Ok(())
    }

    /// ç»Ÿè®¡æ•°æ�®åº“çš„ä¿¡æ�¯
    /// æ€»æ�¡ç›®æ•°ï¼Œæœ€è¿‘ä¸€æœˆæ–°å¢�çš„æ�¡ç›®æ•°...
    pub fn db_status(&self) -> HandlerResult<String> {
        let conn = self.pool.clone().get()?;

        let o_count =
            conn.query_row::<i32, _, _>("SELECT COUNT(*) FROM objects", [], |row| row.get(0))?;

        let c_count =
            conn.query_row::<i32, _, _>("SELECT COUNT(*) FROM comments", [], |row| row.get(0))?;

        let start = chrono::Local::now() - chrono::Duration::days(31);
        let m_new = conn.query_row::<i32, _, _>(
            "SELECT COUNT(*) FROM comments WHERE date > ?",
            [start.format("%Y-%m-%d").to_string()],
            |row| row.get(0),
        )?;

        let o_new = conn.query_row::<i32, _, _>(
            "SELECT COUNT(*) FROM objects WHERE date > ?",
            [start.format("%Y-%m-%d").to_string()],
            |row| row.get(0),
        )?;

        Ok(format!(
            "è¯„ä»·æ€»æ•°ï¼š{}, å®�ä½“å®¢ä½“æ€»æ•°ï¼š{}, æœˆæ–°å¢�å®¢ä½“æ•°ï¼š{}, æœˆå¢�è¯„ä»·æ•°ï¼š{}",
            c_count, o_count, o_new, m_new
        ))
    }
}

impl ObjComment {}

pub fn get_current_date() -> String {
    chrono::Local::now().format("%Y-%m-%d").to_string()
}

#[test]
fn test_find_object() {
    let db = SAFCdb::new();
    let s = &"ä¹ __".to_string();
    let result = db.find_supervisor_like(s);
    println!("{:#?}", result);
}

#[test]
fn test_if_object_exists() {
    let db = SAFCdb::new();
    let result = db.if_object_exists("835cc322b7691485");
    println!("{:#?}", result);
}

#[test]
fn test_find_comment_like() {
    let db = SAFCdb::new();
    let comments = db.find_comment_like(&"%ä¹ å¤§å¤§%".to_string());
    println!("{:#?}", comments);
}

#[test]
fn db_status_test() {
    let db = SAFCdb::new();
    println!("{:#?}", db.db_status());
}

#[test]
fn my_test() {
    let db = SAFCdb::new();
    println!("{:#?}", db.find_school_cate().unwrap());
    println!("{:#?}", db.find_university(&"985".to_string()).unwrap());
    println!(
        "{:#?}",
        db.find_department(&"985".to_string(), &"æ¸…å��å¤§å­¦".to_string())
            .unwrap()
    );
    println!("{:#?}", db.find_objteacher_with_id("918863e1af3b1e67"));
}

#[test]
fn my_test2() {
    assert_eq!("admin".to_owned(), SourceCate::Admin.to_string());
    assert_eq!("nest".to_owned(), CommentType::Nest.to_string());
}
