database.py

import sqlite3
import os
import logging
from pathlib import Path

# Setup logging
logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")
logger = logging.getLogger(__name__)

DB_PATH = os.getenv("DB_PATH", "leads.db")

def get_db_connection():
    """Returns a connection to the SQLite database."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initializes the database schema if it doesn't exist."""
    logger.info(f"Initializing database at {DB_PATH}")
    conn = get_db_connection()
    cursor = conn.cursor()

    # Leads table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS leads (
            id TEXT PRIMARY KEY,
            business_name TEXT,
            category TEXT,
            subcategory TEXT,
            country TEXT,
            region_state TEXT,
            city TEXT,
            address TEXT,
            phone TEXT,
            website_url TEXT,
            normalized_domain TEXT,
            email TEXT,
            email_confidence TEXT,
            email_source_url TEXT,
            contact_page_url TEXT,
            source_type TEXT,
            source_url TEXT,
            google_place_id TEXT,
            latitude REAL,
            longitude REAL,
            rating REAL,
            review_count INTEGER,
            opening_hours TEXT,
            social_instagram TEXT,
            social_facebook TEXT,
            social_linkedin TEXT,
            website_platform TEXT,
            has_ssl INTEGER,
            is_mobile_friendly_estimate INTEGER,
            page_speed_estimate INTEGER,
            has_online_booking INTEGER,
            has_clear_cta INTEGER,
            has_reviews_section INTEGER,
            has_services_page INTEGER,
            has_pricing INTEGER,
            has_before_after_gallery INTEGER,
            has_modern_design_estimate INTEGER,
            website_quality_score INTEGER,
            redesign_opportunity_score INTEGER,
            lead_priority TEXT,
            problems_detected TEXT,
            suggested_offer TEXT,
            outreach_message TEXT,
            status TEXT DEFAULT 'discovered',
            created_at TEXT,
            updated_at TEXT,
            last_checked_at TEXT,
            do_not_contact INTEGER DEFAULT 0
        )
    """)

    # Emails table (to store multiple emails per lead if needed)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS emails (
            id TEXT PRIMARY KEY,
            lead_id TEXT,
            email_address TEXT,
            confidence TEXT,
            source_url TEXT,
            created_at TEXT,
            FOREIGN KEY(lead_id) REFERENCES leads(id)
        )
    """)

    # Crawl pages (log pages crawled)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS crawl_pages (
            id TEXT PRIMARY KEY,
            lead_id TEXT,
            url TEXT,
            status_code INTEGER,
            discovered_emails TEXT,
            crawled_at TEXT,
            FOREIGN KEY(lead_id) REFERENCES leads(id)
        )
    """)

    # Discovery sources
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS discovery_sources (
            id TEXT PRIMARY KEY,
            query TEXT,
            source_name TEXT,
            results_found INTEGER,
            executed_at TEXT
        )
    """)

    # Blacklist domains
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS blacklist_domains (
            domain TEXT PRIMARY KEY,
            reason TEXT,
            added_at TEXT
        )
    """)

    # Blacklist emails
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS blacklist_emails (
            email TEXT PRIMARY KEY,
            reason TEXT,
            added_at TEXT
        )
    """)

    # Run logs
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS run_logs (
            id TEXT PRIMARY KEY,
            command TEXT,
            started_at TEXT,
            completed_at TEXT,
            items_processed INTEGER,
            errors INTEGER
        )
    """)

    # Indexes
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_leads_domain ON leads(normalized_domain)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_leads_city_category ON leads(city, category)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status)")

    conn.commit()
    conn.close()
    logger.info("Database initialization complete.")

if __name__ == "__main__":
    init_db()