"""Database-backed bike catalogue, site copy, source configuration and sync history."""
from contextlib import closing
import json
import sqlite3
import db

CONTENT = {
    'site_name': ('Brand', 'Site name', 'CarCompare'),
    'footer_text': ('Brand', 'Footer introduction', 'For the everyday drive. And the roads less travelled. Find a car or bike that feels like you.'),
    'home_eyebrow': ('Homepage', 'Eyebrow', 'THE NEXT CHAPTER STARTS HERE'),
    'home_title': ('Homepage', 'Headline', 'Life moves.'),
    'home_accent': ('Homepage', 'Headline accent', 'Find your drive.'),
    'home_intro': ('Homepage', 'Introduction', 'Big plans. Everyday journeys. Discover the car or bike that fits your life — and your budget.'),
    'spotlight_id': ('Homepage', 'Spotlight car ID (blank for top rated)', ''),
    'brand_heading': ('Homepage', 'Brand heading', 'A favourite brand? Start here.'),
    'picks_eyebrow': ('Homepage', 'Top picks eyebrow', 'THE SHORTLIST'),
    'picks_heading': ('Homepage', 'Top picks heading', 'Rated highly. Worth a look.'),
    'bike_banner_eyebrow': ('Homepage', 'Bike banner eyebrow', 'MORE OF A TWO-WHEEL PERSON?'),
    'bike_banner_title': ('Homepage', 'Bike banner title', 'Same curiosity. A different ride.'),
    'bike_banner_text': ('Homepage', 'Bike banner text', 'Discover commuters, cruisers, street bikes, and electric scooters.'),
    'bike_eyebrow': ('Bikes', 'Eyebrow', 'TWO WHEELS. ENDLESS POSSIBILITIES.'),
    'bike_title': ('Bikes', 'Headline', 'Find your kind of'),
    'bike_accent': ('Bikes', 'Headline accent', 'freedom.'),
    'bike_intro': ('Bikes', 'Introduction', 'Daily commutes, city escapes, and everything in between. Start with the way you want to ride.'),
    'journal_eyebrow': ('Journal', 'Eyebrow', 'THE CARCOMPARE JOURNAL'),
    'journal_title': ('Journal', 'Headline', 'The open-road'),
    'journal_accent': ('Journal', 'Headline accent', 'journal.'),
    'journal_intro': ('Journal', 'Introduction', 'Fresh perspectives. Practical guides. A little inspiration for your next move.'),
    'journal_teaser': ('Journal', 'Homepage heading', 'Good reads. Better decisions.'),
    'journal_banner_eyebrow': ('Journal', 'Bike page banner eyebrow', 'READ BEFORE YOU RIDE'),
    'journal_banner_title': ('Journal', 'Bike page banner title', 'A little research. A better decision.'),
    'journal_banner_text': ('Journal', 'Bike page banner text', 'Explore our buying guides and ownership explainers.'),
}

SOURCE_DEFAULTS = [
    ('car', 'Maruti Suzuki', 'carwale', 'https://www.carwale.com/maruti-suzuki-cars/'),
    ('car', 'Mahindra', 'carwale', 'https://www.carwale.com/mahindra-cars/'),
    ('car', 'Tata', 'carwale', 'https://www.carwale.com/tata-cars/'),
    ('car', 'Hyundai', 'carwale', 'https://www.carwale.com/hyundai-cars/'),
    ('bike', 'TVS', 'bikewale', 'https://www.bikewale.com/tvs-bikes/'),
    ('bike', 'Royal Enfield', 'bikewale', 'https://www.bikewale.com/royalenfield-bikes/'),
    ('bike', 'Yamaha', 'bikewale', 'https://www.bikewale.com/yamaha-bikes/'),
]

def init():
    with closing(db.connect()) as c, c:
        c.executescript('''
        CREATE TABLE IF NOT EXISTS bikes (
          model_no TEXT PRIMARY KEY, brand TEXT NOT NULL, name TEXT NOT NULL,
          category TEXT DEFAULT '', price TEXT DEFAULT '', min_price REAL, max_price REAL,
          image TEXT DEFAULT '', source_url TEXT DEFAULT '', description TEXT DEFAULT '',
          specs_json TEXT NOT NULL DEFAULT '{}', variants_json TEXT NOT NULL DEFAULT '[]',
          last_updated TEXT, details_updated TEXT);
        CREATE TABLE IF NOT EXISTS site_content (key TEXT PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS scrape_sources (
          id INTEGER PRIMARY KEY, kind TEXT NOT NULL, brand TEXT NOT NULL,
          provider TEXT NOT NULL, url TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1,
          last_synced TEXT, last_error TEXT, model_count INTEGER NOT NULL DEFAULT 0);
        CREATE TABLE IF NOT EXISTS sync_runs (
          id INTEGER PRIMARY KEY, kind TEXT NOT NULL, mode TEXT NOT NULL,
          status TEXT NOT NULL DEFAULT 'queued', started_at TEXT DEFAULT (datetime('now')),
          finished_at TEXT, models INTEGER DEFAULT 0, details INTEGER DEFAULT 0,
          errors_json TEXT NOT NULL DEFAULT '[]');
        CREATE UNIQUE INDEX IF NOT EXISTS one_active_sync ON sync_runs((1))
          WHERE status IN ('queued','running');
        CREATE TABLE IF NOT EXISTS media_cache (
          source_url TEXT PRIMARY KEY, local_path TEXT NOT NULL);
        CREATE TABLE IF NOT EXISTS blog_sources (
          id INTEGER PRIMARY KEY, provider TEXT NOT NULL DEFAULT 'rss',
          url TEXT NOT NULL UNIQUE, tag TEXT DEFAULT 'Imported', enabled INTEGER NOT NULL DEFAULT 1,
          last_synced TEXT, last_error TEXT, post_count INTEGER NOT NULL DEFAULT 0);
        ''')
        if 'heartbeat' not in {r[1] for r in c.execute('PRAGMA table_info(sync_runs)')}:
            c.execute('ALTER TABLE sync_runs ADD COLUMN heartbeat TEXT')
        c.executemany('INSERT OR IGNORE INTO site_content(key,value) VALUES (?,?)',
                      [(key, field[2]) for key, field in CONTENT.items()])
        # Seed only once: deleting a source does not make it reappear on restart.
        if not c.execute("SELECT 1 FROM site_content WHERE key='_sources_initialized'").fetchone():
            c.executemany('INSERT OR IGNORE INTO scrape_sources(kind,brand,provider,url) VALUES (?,?,?,?)', SOURCE_DEFAULTS)
            c.execute("INSERT INTO site_content VALUES ('_sources_initialized','1')")
        if not c.execute("SELECT 1 FROM site_content WHERE key='_blog_feeds_initialized'").fetchone():
            c.executemany('INSERT OR IGNORE INTO blog_sources(provider,url,tag) VALUES (?,?,?)', [
                ('rss', 'https://www.rushlane.com/feed', 'RushLane · Cars & bikes'),
                ('rss', 'https://www.indiacarnews.com/news/feed/', 'IndiaCarNews · Auto news'),
            ])
            c.execute("INSERT INTO site_content VALUES ('_blog_feeds_initialized','1')")
    import blog_writer
    blog_writer.init()

def content():
    with closing(db.connect()) as c:
        return dict(c.execute('SELECT key,value FROM site_content'))

def save_content(values):
    with closing(db.connect()) as c, c:
        c.executemany('UPDATE site_content SET value=? WHERE key=?',
                      [(str(values.get(k, '')).strip(), k) for k in CONTENT])

def sources(enabled=False, kind=None):
    with closing(db.connect()) as c:
        return [dict(r) for r in c.execute('SELECT * FROM scrape_sources WHERE (?=0 OR enabled=1) AND (? IS NULL OR kind=?) ORDER BY kind,brand', (enabled,kind,kind))]

def blog_sources(enabled=False):
    with closing(db.connect()) as c:
        return [dict(r) for r in c.execute(
            'SELECT * FROM blog_sources WHERE (?=0 OR enabled=1) ORDER BY id', (enabled,))]

def list_bikes(query='', category='', brand=''):
    with closing(db.connect()) as c:
        rows = c.execute('SELECT * FROM bikes WHERE (?="" OR category=?) AND (?="" OR brand=?) ORDER BY brand,min_price,name', (category,category,brand,brand)).fetchall()
        return [dict(r) for r in rows if query.casefold() in (r['brand']+' '+r['name']).casefold()]

def get_bike(model_no):
    with closing(db.connect()) as c:
        row = c.execute('SELECT * FROM bikes WHERE model_no=?',(model_no,)).fetchone()
    if not row: return None
    bike = dict(row)
    bike['specs'] = json.loads(bike['specs_json'])
    bike['variants'] = json.loads(bike['variants_json'])
    return bike

def compare_bikes(model_nos):
    bikes = [get_bike(model_no) for model_no in list(dict.fromkeys(model_nos))[:4]]
    bikes = [bike for bike in bikes if bike]
    rows = []
    for label in ['Price', 'Category', 'Engine / displacement', 'Mileage - ARAI', 'Transmission', 'Max Power', 'Max Torque', 'Top Speed', 'Kerb Weight', 'Fuel Tank Capacity']:
        values = []
        for bike in bikes:
            if label == 'Price': value = bike['price']
            elif label == 'Category': value = bike['category']
            else:
                pairs = [pair for group in bike['specs'].values() for pair in group]
                aliases = {'engine / displacement': {'engine / displacement', 'engine capacity', 'displacement', 'engine'}}
                expected = aliases.get(label.casefold(), {label.casefold()})
                value = next((item[1] for item in pairs if item[0].casefold() in expected), None)
            values.append(value or None)
        if any(values): rows.append((label, values))
    rows.append(('Variants', [len(bike['variants']) for bike in bikes]))
    return bikes, rows

def save_bike(bike, detailed=False):
    with closing(db.connect()) as c, c:
        c.execute('''INSERT INTO bikes(model_no,brand,name,category,price,min_price,max_price,image,source_url,last_updated)
            VALUES (:model_no,:brand,:name,:category,:price,:min_price,:max_price,:image,:source_url,datetime('now'))
            ON CONFLICT(model_no) DO UPDATE SET brand=excluded.brand,name=excluded.name,
            category=CASE WHEN excluded.category='' THEN bikes.category ELSE excluded.category END,
            price=excluded.price,min_price=excluded.min_price,max_price=excluded.max_price,
            image=excluded.image,source_url=excluded.source_url,last_updated=excluded.last_updated''', bike)
        if detailed:
            c.execute('UPDATE bikes SET description=?,specs_json=?,variants_json=?,details_updated=datetime("now") WHERE model_no=?',
                      (bike['description'],json.dumps(bike['specs']),json.dumps(bike['variants']),bike['model_no']))

def runs():
    with closing(db.connect()) as c,c:
        c.execute('''UPDATE sync_runs SET status='failed',finished_at=datetime('now'),
                  errors_json='["Import interrupted or stalled. Start a new sync to retry."]'
                  WHERE status IN ('queued','running') AND
                  COALESCE(heartbeat,started_at)<datetime('now','-15 minutes')''')
        rows = [dict(r) for r in c.execute('SELECT * FROM sync_runs ORDER BY id DESC LIMIT 12')]
    for row in rows: row['errors'] = json.loads(row['errors_json'])
    return rows

def start_run(kind, mode):
    runs()  # release a stale job lease after a stopped server or worker
    with closing(db.connect()) as c, c:
        try:
            return c.execute('INSERT INTO sync_runs(kind,mode) VALUES (?,?)',(kind,mode)).lastrowid
        except sqlite3.IntegrityError:
            return None

def finish_run(run_id, report):
    errors = report.get('errors',[])
    status = 'partial' if errors and (report['models'] or report['details']) else ('failed' if errors else 'complete')
    with closing(db.connect()) as c, c:
        c.execute('UPDATE sync_runs SET status=?,finished_at=datetime("now"),models=?,details=?,errors_json=? WHERE id=?',
                  (status,report['models'],report['details'],json.dumps(errors),run_id))

def progress(run_id, report):
    if not run_id: return
    with closing(db.connect()) as c,c:
        c.execute('UPDATE sync_runs SET heartbeat=datetime("now"),models=?,details=? WHERE id=? AND status="running"',
                  (report['models'],report['details'],run_id))