import sqlite3
import os
from bs4 import BeautifulSoup
from datetime import datetime
import markdown
import re
import html
#import subprocesses
from contextlib import contextmanager
import sys

from scul_flavored_markdown import SculFlavoredMarkdown, count_markdown_words

#===============================
# Configuration and Utilities
#===============================

db_loc              = os.path.abspath(os.path.join('/', 'var', 'www', 'html', 'database'))
db_path    = os.path.abspath(os.path.join(db_loc, 'db.db'))

def sanitize_filename(filename: str) -> str:
    return re.sub(r'[^\w\-_\. ]', '_', filename)

def rinse(tuple_input: tuple) -> str:
    while True:
        input_prompt = f"What one? {', '.join(tuple_input[:-1])} or {tuple_input[-1]}? "
        user_input = input(input_prompt).strip().lower()
        if not user_input:
            print("Input cannot be empty. Please try again.")
            continue
        if user_input in tuple_input:
            return user_input
        print(f"Please input either {', '.join(tuple_input[:-1])} or {tuple_input[-1]}.")

#===============================
# Database Management
#===============================

@contextmanager
def db_connection(db_path: str):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
    finally:
        conn.close()

def execute_single_query(conn, query, params=None):
    try:
        cursor = conn.cursor()
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        result = cursor.fetchone()
        return dict(result) if result else None
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return None

def insert_row(conn, table, data):
    columns = ', '.join(data.keys())
    placeholders = ', '.join(['?'] * len(data))
    query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
    try:
        with conn:
            conn.execute(query, tuple(data.values()))
        return True
    except sqlite3.Error as e:
        print(f"Insert error: {e}")
        return False

value_in_column = lambda conn, table, column, target: bool(execute_single_query(conn, f"SELECT 1 FROM {table} WHERE {column} = ?", (target,)))

def update_row(conn, table, where_column, where_value, update_columns):
    if not update_columns:
        return None
    
    set_clause = ", ".join([f"{col} = ?" for col in update_columns.keys()])
    query = f"UPDATE {table} SET {set_clause} WHERE {where_column} = ?"
    print(query)

    values = list(update_columns.values()) + [where_value]

    try:
        cursor = conn.cursor()
        cursor.execute(query, values)

        send_query(conn, f"SELECT * FROM {table} WHERE {where_column} = '{where_value}'")
        rowcount = cursor.rowcount
        print(f"Number of rows affected: {rowcount}")
        return rowcount

        if conn.in_transaction:
            print("Committing changes to the database.")
            conn.commit()

    except sqlite3.Error as e:
        print(f"Update error: {e}")
        return None

def execute_query(conn, query, params=None):
    try:
        if conn.row_factory != sqlite3.Row:
            conn.row_factory = sqlite3.Row
        with conn:
            cursor = conn.cursor()
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            return [dict(row) for row in cursor.fetchall()]
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return []

def send_query(conn, query, params=None):
    try:
        with conn:
            results = execute_query(conn, query, params)
            if results:
                for row in results:
                    for column, value in row.items():
                        print(f"{column}: \n\t{value} \n")
                    print("\n-----\n")
            else:
                print("No results returned.")
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return []

#===============================
# HTML and Content Management
#===============================

def write_page_html(markdown_content: str):
    try:
        soup = BeautifulSoup("", 'html.parser')
        md = markdown.Markdown(extensions=[SculFlavoredMarkdown(), 'footnotes'])
        # If markdown_content is a file object, read it first
        if hasattr(markdown_content, 'read'):
            markdown_content = markdown_content.read()
        html_content = md.convert(markdown_content)
        soup.append(BeautifulSoup(html_content, 'html.parser'))
        return str(soup)
    except Exception as e:
        print(f"Error in write_page_html: {str(e)}")
        raise

def write_preview_html(html_content: str):
    try:
        soup = BeautifulSoup(html_content, 'html.parser')
        
        for a_tag in soup.find_all('a'):
            span_tag = soup.new_tag('span', **{'class': 'false-external-link'})
            span_tag.string = a_tag.get_text()
            a_tag.replace_with(span_tag)

        for h_tag in soup.find_all(['h1', 'h2', 'h3', 'h4', 'h5', 'h6']):
            span_tag = soup.new_tag('span', **{'class': 'preview-header'})
            span_tag.string = h_tag.get_text()
            h_tag.replace_with(span_tag)
        
        for tag in soup.find_all():
            if tag.name not in ['span', 'sup', 'sub']:
                tag.unwrap()
        
        clean_html = ''.join(str(tag) for tag in soup.contents)
        return clean_html
    
    except Exception as e:
        print(f"Error in write_preview_html: {str(e)}")
        raise

#===============================
# Main Functions
#===============================

def main():
    print("Functions include: call, send_query, write_page_html")

if __name__ == "__main__":
    main()