import logging
from dataclasses import dataclass

import sqlite3


logger = logging.getLogger(__name__) # Set the logger name, to the name of the module


class DBHandler:
    def con_factory(self) -> sqlite3.Connection:
        con = sqlite3.connect("blog.node5.net.db")
        return con

    def get_comments(self, url: str) -> list[dict]:
        with self.con_factory() as con:
            cur = con.cursor()
            comments_sql = '''
SELECT id, nickname, comment, page_url, visitor_url, (CASE WHEN show_contact THEN contact ELSE NULL END) as contact_info, created_at
FROM comment WHERE approved AND public AND page_url = ? ORDER BY created_at DESC;
'''
            cur.execute(comments_sql, (url,))
            comments = cur.fetchall()
            headers = list(map(lambda attr : attr[0], cur.description))
            comments_sql = comments_sql.replace('?', f"'{url}'")
            return comments, headers, comments_sql


    def post_comment(self, comment: str, page_url: str, visitor_url: str=None, nickname: str=None, contact: str=None,
                     show_contact: bool=True, public: bool=True):
        with self.con_factory() as con:
            cur = con.cursor()
            cur.execute(
                '''
                INSERT INTO comment (comment, page_url, visitor_url, nickname, contact, show_contact, public)
                VALUES (?, ?, ?, ?, ?, ?, ?);
                ''',
                (comment, page_url, visitor_url, nickname, contact, show_contact, public)
            )