import sqlite3
import functools


class GhosteryDB:
    def __init__(self, db_path: str = "trackerdb.db"):
        self.db_path = db_path
        self.conn = None
        self.conn = sqlite3.connect(self.db_path)

    def close(self):
        if self.conn:
            self.conn.close()

    def get_company_details(self, company_id: str) -> dict:
        cursor = self.conn.cursor()
        cursor.execute("SELECT * FROM companies WHERE id = ?", (company_id,))
        company = cursor.fetchone()
        return company

    def get_entity_details(self, entity_id: str) -> dict:
        cursor = self.conn.cursor()
        cursor.execute("SELECT * FROM trackers WHERE id = ?", (entity_id,))
        entity = cursor.fetchone()
        return entity

    @functools.lru_cache(maxsize=8192)
    def get_data_for_domain(
        self, domain: str
    ) -> tuple[str, dict, dict] | tuple[None, None, None]:
        cursor = self.conn.cursor()
        cursor.execute(
            """
            WITH RECURSIVE domain_parts(domain, remaining) AS (
                SELECT ?, ?
                UNION ALL
                SELECT substr(remaining, instr(remaining, '.') + 1),
                       substr(remaining, instr(remaining, '.') + 1)
                FROM domain_parts
                WHERE instr(remaining, '.') > 0
            )
            SELECT td.domain, td.tracker
            FROM tracker_domains td
            JOIN domain_parts dp ON td.domain = dp.domain
            ORDER BY length(td.domain) DESC
            LIMIT 1
        """,
            (domain, domain),
        )
        tracker_domain = cursor.fetchone()
        if tracker_domain:
            entity_id = tracker_domain[1]
            cursor.execute(
                "SELECT id, name, company_id, website_url, category_id FROM trackers WHERE id = ?",
                (entity_id,),
            )
            entity_id, entity_name, company_id, website, category_id = cursor.fetchone()
            category_name = cursor.execute(
                "SELECT name FROM categories WHERE id = ?", (category_id,)
            ).fetchone()[0]
            (
                company_name,
                company_description,
                company_website,
                company_privacy_url,
                company_country,
            ) = cursor.execute(
                "SELECT name, description, website_url, privacy_url, country FROM companies WHERE id = ?",
                (company_id,),
            ).fetchone()
            return (
                tracker_domain[0],
                {
                    "id": entity_id,
                    "name": entity_name,
                    "category": category_name,
                    "website": website,
                },
                {
                    "id": company_id,
                    "name": company_name,
                    "description": company_description,
                    "website": company_website,
                    "privacy_url": company_privacy_url,
                    "country": company_country,
                },
            )
        return None, None, None


# Example usage
if __name__ == "__main__":
    db = GhosteryDB()
    # print(db.get_company_details("google"))
    # print(db.get_entity_details("google_play_services"))
    # print(db.get_data_for_domain("google.com"))
    # print(db.get_data_for_domain("blah.adservice.google.com"))
    # print(db.get_data_for_domain("play.google.com"))
    print(db.get_data_for_domain("62.255.158.34.bc.googleusercontent.com"))
    db.close()