use std::collections::HashMap;
use std::path::Path;
use rusqlite::{Connection, OptionalExtension};
use crate::users::db::{User, UserData};

pub struct UserSqliteDB {
    conn: Connection,
}

impl UserSqliteDB {
    pub fn open(path: impl AsRef<Path>) -> rusqlite::Result<Self> {
        let conn = Connection::open(path)?;

        conn.execute("CREATE TABLE IF NOT EXISTS users (\
            id INTEGER PRIMARY KEY,\
            name TEXT NOT NULL,\
            password TEXT\
        )", ())?;
        conn.execute("CREATE TABLE IF NOT EXISTS user_kv (\
            id INTEGER PRIMARY KEY,\
            user INTEGER,\
            key TEXT NOT NULL,\
            value TEXT NOT NULL,\
            FOREIGN KEY(user) REFERENCES users(id)\
        )", ())?;
        conn.execute("CREATE TABLE IF NOT EXISTS user_roles (\
            id INTEGER PRIMARY KEY,\
            user INTEGER,\
            role TEXT NOT NULL,\
            FOREIGN KEY(user) REFERENCES users(id)\
        )", ())?;

        Ok(Self { conn })
    }

    pub fn get(&self, uid: &str) -> rusqlite::Result<Option<User>> {
        let sqlite_user = self.conn.query_row("SELECT id, name, password FROM users WHERE name = ?1", [uid], |row| {
            Ok(SqliteUser {
                id: row.get(0)?,
                name: row.get(1)?,
                password: row.get(2)?,
            })
        }).optional()?;
        if let Some(SqliteUser { name, password, .. }) = sqlite_user {
            let mut kv_stmt = self.conn.prepare("SELECT key, value FROM user_kv WHERE user_id = :id")?;
            let kv: HashMap<String, String> = kv_stmt
                .query_map(&[(":id", &name)], |row| Ok((row.get(0)?, row.get(1)?)))?
                .filter_map(|fields| fields.ok())
                .collect();

            let mut roles_stmt = self.conn.prepare("SELECT role FROM user_roles WHERE user_id = :id")?;
            let roles: Vec<String> = roles_stmt
                .query_map(&[(":id", &name)], |row| row.get(0))?
                .filter_map(|fields| fields.ok())
                .collect();

            let passwd = if password.is_empty() { None } else { Some(password) };
            Ok(Some(User {
                id: name,
                userdata: UserData {
                    roles,
                    passwd,
                    kv,
                }
            }))
        } else {
            Ok(None)
        }
    }

    pub fn put(&self, uid: &str, user: &SqliteUser) -> rusqlite::Result<()> {
        todo!()
    }
}

pub struct SqliteUser {
    id: i32,
    name: String,
    password: String,
}

struct SqliteUserKv {
    id: i32,
    user_id: i32,
    key: String,
    value: String,
}

struct SqliteRoles {
    id: i32,
    user_id: i32,
    role: String,
}