use rusqlite::named_params;

use crate::{
    database::{
        db::{Db, DbError},
        macros::NewId,
    },
    types::{
        sepawa::{
            Agenda, AgendaId, NewAgenda, NewPartner, NewPortrait, Partner, PartnerDetailsId,
            PartnerId, Portrait, PortraitId,
        },
        user::FromRow,
    },
};

#[derive(Clone, Debug)]
pub struct SepawaRepository {
    pub(crate) db: Db,
}

impl SepawaRepository {
    pub(crate) fn create_portrait(&self, new_portrait: NewPortrait) -> Result<(), DbError> {
        let portrait_id = PortraitId::new_id();
        let sql = r#"
            INSERT INTO portrait
                (portrait_id, sort_number, title, name, image_id)
            VALUES
                (:portrait_id, :sort_number, :title, :name, :image_id)"#;

        let params = named_params! {
            ":portrait_id": portrait_id,
            ":sort_number": new_portrait.sort_number,
            ":title": new_portrait.title,
            ":name": new_portrait.name,
            ":image_id": new_portrait.image_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn get_portraits(&self) -> Result<Vec<Portrait>, DbError> {
        let sql = r#"
            SELECT
                portrait_id,
                sort_number,
                title,
                name,
                image.image_id,
                image.image_name
            FROM
                portrait
                INNER JOIN image ON portrait.image_id = image.image_id"#;

        let conn = self.db.connection()?;

        let mut stmt = conn.prepare(sql)?;

        let portraits: Vec<Portrait> = stmt
            .query_map([], Portrait::try_from_row)?
            .filter_map(|res| res.ok())
            .collect();

        Ok(portraits)
    }

    pub(crate) fn update_portrait(&self, portrait: Portrait) -> Result<(), DbError> {
        let sql = r#"
            UPDATE portrait
                SET
                    sort_number = :sort_number,
                    title = :title,
                    name = :name,
                    image_id = :image_id
                WHERE
                    portrait_id = :portrait_id"#;

        let params = named_params! {
            ":portrait_id": portrait.portrait_id,
            ":sort_number": portrait.sort_number,
            ":title": portrait.title,
            ":name": portrait.name,
            ":image_id": portrait.image_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn delete_portrait(&self, portrait_id: PortraitId) -> Result<(), DbError> {
        let sql = r#"
            DELETE FROM portrait WHERE portrait_id = :portrait_id"#;

        let params = named_params! {
            ":portrait_id": portrait_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn create_partner(&self, new_partner: NewPartner) -> Result<(), DbError> {
        let partner_id = PartnerId::new_id();
        let sql = r#"
            INSERT INTO partner 
                (partner_id, company_name, logo_image_id)
            VALUES
                (:partner_id, :company_name, :logo_image_id)"#;

        let params = named_params! {
            ":partner_id": partner_id,
            ":company_name": new_partner.company_name,
            ":logo_image_id": new_partner.logo_image_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        if let Some(new_partner_details) = new_partner.partner_details {
            let partner_details_id = PartnerDetailsId::new_id();
            let sql = r#"
                INSERT INTO partner_details
                    (partner_details_id, partner_id, sector, activity, location, website, contact, member_date, right_picture_id, left_picture_id, right_text)
                VALUES
                    (:partner_details_id, :partner_id, :sector, :activity, :location, :website, :contact, :member_date, :right_picture_id, :left_picture_id, :right_text)
                "#;

            let params = named_params! {
                ":partner_details_id": partner_details_id,
                ":partner_id": partner_id,
                ":sector": new_partner_details.sector,
                ":activity": new_partner_details.activity,
                ":location": new_partner_details.location,
                ":website": new_partner_details.website,
                ":contact": new_partner_details.contact,
                ":member_date": new_partner_details.member_date,
                ":right_picture_id": new_partner_details.right_picture_id,
                ":left_picture_id": new_partner_details.left_picture_id,
                ":right_text": new_partner_details.right_text
            };

            tx.execute(sql, params)?;
        }

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn update_partner(&self, partner: Partner) -> Result<(), DbError> {
        let sql = r#"
            UPDATE partner
                SET
                    company_name = :company_name,
                    logo_image_id = :logo_image_id
                WHERE
                    partner_id = :partner_id"#;

        let params = named_params! {
            ":partner_id": partner.partner_id,
            ":company_name": partner.company_name,
            ":logo_image_id": partner.logo_image_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        if let Some(partner_details) = partner.partner_details {
            let sql = r#"
                UPDATE partner_details
                    SET
                        partner_id = :partner_id,
                        sector = :sector,
                        activity = :activity,
                        location = :location,
                        website = :website,
                        contact = :contact,
                        member_date = :member_date,
                        right_picture_id = :right_picture_id,
                        left_picture_id = :left_picture_id,
                        right_text = :right_text
                    WHERE
                        partner_details_id = :partner_details_id
                "#;

            let params = named_params! {
                ":partner_details_id": partner_details.partner_details_id,
                ":partner_id": partner_details.partner_id,
                ":sector": partner_details.sector,
                ":activity": partner_details.activity,
                ":location": partner_details.location,
                ":website": partner_details.website,
                ":contact": partner_details.contact,
                ":member_date": partner_details.member_date,
                ":right_picture_id": partner_details.right_picture_id,
                ":left_picture_id": partner_details.left_picture_id,
                ":right_text": partner_details.right_text
            };

            tx.execute(sql, params)?;
        }

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn delete_partner(&self, partner_id: PartnerId) -> Result<(), DbError> {
        let sql = r#"
            DELETE FROM partner WHERE partner_id = :partner_id
        "#;

        let params = named_params! {
            ":partner_id": partner_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn get_partners(&self) -> Result<Vec<Partner>, DbError> {
        let sql = r#"
            SELECT
                partner.partner_id,
                company_name,
                logo.image_id as logo_image_id,
                logo.image_name as logo_image_name,
                partner_details_id,
                sector,
                activity,
                location,
                website,
                contact,
                member_date,
                r.image_id as right_picture_id,
                r.image_name as right_picture_name,
                l.image_id as left_picture_id,
                l.image_name as left_picture_name,
                right_text
            FROM
                partner
                LEFT JOIN partner_details ON partner.partner_id = partner_details.partner_id
                INNER JOIN image as logo ON partner.logo_image_id = logo.image_id
                LEFT JOIN image as r ON partner_details.right_picture_id = r.image_id
                LEFT JOIN image as l on partner_details.left_picture_id = l.image_id
            "#;

        let conn = self.db.connection()?;

        let mut stmt = conn.prepare(sql)?;

        let partners: Vec<Partner> = stmt
            .query_map([], Partner::try_from_row)?
            .filter_map(Result::ok)
            .collect();

        Ok(partners)
    }

    pub(crate) fn create_agenda(&self, new_agenda: NewAgenda) -> Result<(), DbError> {
        let agenda_id = AgendaId::new_id();

        let sql = r#"
            INSERT INTO agenda
                (agenda_id, sort_date, date, title, entry)
            VALUES
                (:agenda_id, :sort_date, :date, :title, :entry)
            "#;

        let params = named_params! {
            ":agenda_id": agenda_id,
            ":sort_date": new_agenda.sort_date,
            ":date": new_agenda.date,
            ":title": new_agenda.title,
            ":entry": new_agenda.entry
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn update_agenda(&self, agenda: Agenda) -> Result<(), DbError> {
        let sql = r#"
            UPDATE agenda
                SET
                    sort_date = :sort_date,
                    date = :date,
                    title = :title,
                    entry = :entry
                WHERE
                    agenda_id = :agenda_id"#;

        let params = named_params! {
            ":agenda_id": agenda.agenda_id,
            ":sort_date": agenda.sort_date,
            ":title": agenda.title,
            ":entry": agenda.entry
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn delete_agenda(&self, agenda_id: AgendaId) -> Result<(), DbError> {
        let sql = r#"DELETE FROM agenda WHERE agenda_id = :agenda_id"#;

        let params = named_params! {
            ":agenda_id": agenda_id
        };

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(())
    }

    pub(crate) fn get_agenda_entries(&self) -> Result<Vec<Agenda>, DbError> {
        let sql = r#"
            SELECT
                agenda_id,
                sort_date,
                date,
                title,
                entry
            FROM
                agenda
            "#;

        let conn = self.db.connection()?;

        let mut stmt = conn.prepare(sql)?;

        let agenda_entries: Vec<Agenda> = stmt
            .query_map([], Agenda::try_from_row)?
            .filter_map(|res| res.ok())
            .collect();

        Ok(agenda_entries)
    }
}
