"""
Database management module
"""
import sqlite3
import json
from pathlib import Path
from datetime import datetime


class Database:
    def __init__(self, db_path: Path):
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self._init_schema()
    
    def _init_schema(self):
        cursor = self.conn.cursor()
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS files (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                path TEXT UNIQUE NOT NULL,
                discovered_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS frontmatter (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                file_id INTEGER NOT NULL,
                content TEXT NOT NULL,
                read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                conformant INTEGER DEFAULT 0,
                FOREIGN KEY (file_id) REFERENCES files(id)
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS commits (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                file_id INTEGER NOT NULL,
                author_name TEXT,
                author_email TEXT,
                commit_hash TEXT,
                commit_tag TEXT,
                latest_hash TEXT,
                FOREIGN KEY (file_id) REFERENCES files(id)
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                tag TEXT UNIQUE NOT NULL
            )
        """)
        
        self.conn.commit()
    
    def add_file(self, path: str) -> int:
        cursor = self.conn.cursor()
        cursor.execute("INSERT OR IGNORE INTO files (path) VALUES (?)", (path,))
        self.conn.commit()
        cursor.execute("SELECT id FROM files WHERE path = ?", (path,))
        return cursor.fetchone()[0]
    
    def add_frontmatter(self, file_id: int, content: dict, conformant: bool = False) -> int:
        cursor = self.conn.cursor()
        cursor.execute(
            "INSERT INTO frontmatter (file_id, content, conformant) VALUES (?, ?, ?)",
            (file_id, json.dumps(content), 1 if conformant else 0)
        )
        self.conn.commit()
        return cursor.lastrowid
    
    def update_frontmatter_conformance(self, fm_id: int, conformant: bool):
        cursor = self.conn.cursor()
        cursor.execute(
            "UPDATE frontmatter SET conformant = ? WHERE id = ?",
            (1 if conformant else 0, fm_id)
        )
        self.conn.commit()
    
    def add_commit_info(self, file_id: int, author_name: str, author_email: str, 
                       commit_hash: str, commit_tag: str, latest_hash: str):
        cursor = self.conn.cursor()
        cursor.execute(
            """INSERT OR REPLACE INTO commits 
               (file_id, author_name, author_email, commit_hash, commit_tag, latest_hash)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (file_id, author_name, author_email, commit_hash, commit_tag, latest_hash)
        )
        self.conn.commit()
    
    def add_tag(self, tag: str):
        cursor = self.conn.cursor()
        cursor.execute("INSERT OR IGNORE INTO tags (tag) VALUES (?)", (tag,))
        self.conn.commit()
    
    def get_all_tags(self) -> list:
        cursor = self.conn.cursor()
        cursor.execute("SELECT tag FROM tags ORDER BY tag")
        return [row[0] for row in cursor.fetchall()]
    
    def get_file_id(self, path: str) -> int:
        cursor = self.conn.cursor()
        cursor.execute("SELECT id FROM files WHERE path = ?", (path,))
        row = cursor.fetchone()
        return row[0] if row else None
    
    def close(self):
        self.conn.close()