"""Local SQLite database: schema, save aur read functions.

Website sirf is DB se data padhti hai. DB ko scraper.py bharta hai.
"""

from contextlib import closing
import os
import re
import sqlite3

DB_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'cars.db')

SCHEMA = '''
CREATE TABLE IF NOT EXISTS brands (
    name        TEXT PRIMARY KEY,
    source_url  TEXT NOT NULL,
    last_synced DATETIME
);

CREATE TABLE IF NOT EXISTS cars (
    model_no        TEXT PRIMARY KEY,          -- e.g. MARUTI-BALENO
    brand           TEXT NOT NULL REFERENCES brands(name),
    car_name        TEXT NOT NULL,
    price           TEXT,                      -- '₹ 5.99 - 9.99 Lakh'
    min_price       REAL,                      -- lakh me
    max_price       REAL,
    fuel_type       TEXT,
    mileage         TEXT,
    engine          TEXT,
    body_type       TEXT,
    seating         TEXT,
    transmission    TEXT,
    safety_rating   TEXT DEFAULT '-',
    image           TEXT,
    source_url      TEXT,
    description     TEXT,
    expert_verdict  TEXT,
    expert_rating   TEXT,
    user_rating     REAL,
    review_count    INTEGER,
    mileage_summary TEXT,
    brochure_url    TEXT,
    last_updated    DATETIME,                  -- basic data sync
    details_updated DATETIME                   -- full details sync
);
CREATE INDEX IF NOT EXISTS idx_cars_price ON cars(min_price, max_price);

CREATE TABLE IF NOT EXISTS variants (
    id         INTEGER PRIMARY KEY,
    model_no   TEXT NOT NULL REFERENCES cars(model_no) ON DELETE CASCADE,
    name       TEXT,
    price      TEXT,
    price_lakh REAL,
    on_road    TEXT,
    spec       TEXT,
    waiting    TEXT
);
CREATE INDEX IF NOT EXISTS idx_variants_model ON variants(model_no);

-- Specifications aur features dono (kind = 'spec' | 'feature')
CREATE TABLE IF NOT EXISTS specs (
    id       INTEGER PRIMARY KEY,
    model_no TEXT NOT NULL REFERENCES cars(model_no) ON DELETE CASCADE,
    kind     TEXT NOT NULL,
    section  TEXT,
    name     TEXT,
    value    TEXT
);
CREATE INDEX IF NOT EXISTS idx_specs_model ON specs(model_no, kind);

-- Chhote text lists (kind = 'pro' | 'con' | 'highlight' | 'update' | 'colour')
CREATE TABLE IF NOT EXISTS notes (
    id       INTEGER PRIMARY KEY,
    model_no TEXT NOT NULL REFERENCES cars(model_no) ON DELETE CASCADE,
    kind     TEXT NOT NULL,
    text     TEXT
);
CREATE INDEX IF NOT EXISTS idx_notes_model ON notes(model_no, kind);

CREATE TABLE IF NOT EXISTS expert_reviews (
    id       INTEGER PRIMARY KEY,
    model_no TEXT NOT NULL REFERENCES cars(model_no) ON DELETE CASCADE,
    title    TEXT,
    rating   TEXT,
    text     TEXT
);

CREATE TABLE IF NOT EXISTS mileage (
    id           INTEGER PRIMARY KEY,
    model_no     TEXT NOT NULL REFERENCES cars(model_no) ON DELETE CASCADE,
    fuel         TEXT,
    transmission TEXT,
    mileage      TEXT
);

CREATE TABLE IF NOT EXISTS posts (
    id          INTEGER PRIMARY KEY,
    slug        TEXT UNIQUE NOT NULL,
    title       TEXT NOT NULL,
    excerpt     TEXT,
    content     TEXT,                      -- plain text, blank lines = paragraphs
    cover_image TEXT,
    author      TEXT DEFAULT 'CarCompare Team',
    tag         TEXT,
    published   INTEGER NOT NULL DEFAULT 1,
    source_url  TEXT,
    imported_at DATETIME,
    created_at  DATETIME DEFAULT (datetime('now')),
    updated_at  DATETIME DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_posts_published ON posts(published, created_at);
'''

NOTE_KINDS = {
    'pros': 'pro',
    'cons': 'con',
    'highlights': 'highlight',
    'latest_updates': 'update',
    'colours': 'colour',
}
CHILD_TABLES = ('variants', 'specs', 'notes', 'expert_reviews', 'mileage')


def connect():
  conn = sqlite3.connect(DB_FILE, timeout=30)
  conn.row_factory = sqlite3.Row
  conn.execute('PRAGMA foreign_keys = ON')
  return conn


def init_db():
  with closing(connect()) as conn:
    conn.execute('PRAGMA journal_mode = WAL')  # web reads + scraper writes saath
    conn.executescript(SCHEMA)
    columns = {row[1] for row in conn.execute('PRAGMA table_info(posts)')}
    if 'source_url' not in columns:
      conn.execute('ALTER TABLE posts ADD COLUMN source_url TEXT')
    if 'imported_at' not in columns:
      conn.execute('ALTER TABLE posts ADD COLUMN imported_at DATETIME')
    conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_posts_source_url ON posts(source_url) WHERE source_url IS NOT NULL AND source_url <> ''")


# ---------------------------------------------------------
# WRITE
# ---------------------------------------------------------
def save_brand(name, source_url, cars):
  """Brand page ka basic data (list of car dicts) upsert karta hai."""
  with closing(connect()) as conn, conn:
    conn.execute(
        '''INSERT INTO brands (name, source_url, last_synced)
           VALUES (?, ?, datetime('now'))
           ON CONFLICT(name) DO UPDATE SET
               source_url=excluded.source_url, last_synced=excluded.last_synced''',
        (name, source_url),
    )
    conn.executemany(
        '''INSERT INTO cars (model_no, brand, car_name, price, min_price, max_price,
                             fuel_type, mileage, engine, body_type, seating,
                             transmission, image, source_url, last_updated)
           VALUES (:model_no, :brand, :car_name, :price, :min_price, :max_price,
                   :fuel_type, :mileage, :engine, :body_type, :seating,
                   :transmission, :image, :source_url, datetime('now'))
           ON CONFLICT(model_no) DO UPDATE SET
               car_name=excluded.car_name, price=excluded.price,
               min_price=excluded.min_price, max_price=excluded.max_price,
               fuel_type=excluded.fuel_type, mileage=excluded.mileage,
               engine=excluded.engine, body_type=excluded.body_type,
               seating=excluded.seating, transmission=excluded.transmission,
               image=excluded.image, source_url=excluded.source_url,
               last_updated=excluded.last_updated''',
        cars,
    )


def save_details(model_no, d):
  """Ek car ki full details save karta hai (purani child rows replace)."""
  with closing(connect()) as conn, conn:
    conn.execute(
        '''UPDATE cars SET description=?, expert_verdict=?, expert_rating=?,
               user_rating=?, review_count=?, safety_rating=?, mileage_summary=?,
               brochure_url=?, details_updated=datetime('now')
           WHERE model_no=?''',
        (
            d['description'], d['expert_verdict'], d['expert_rating'],
            d['user_rating'], d['review_count'], d['safety_rating'],
            d['mileage_summary'], d['brochure_url'], model_no,
        ),
    )
    for table in CHILD_TABLES:
      conn.execute(f'DELETE FROM {table} WHERE model_no=?', (model_no,))

    conn.executemany(
        '''INSERT INTO variants (model_no, name, price, price_lakh, on_road, spec, waiting)
           VALUES (?, ?, ?, ?, ?, ?, ?)''',
        [
            (model_no, v['name'], v['price'], v['price_lakh'], v['on_road'],
             v['spec'], v['waiting'])
            for v in d['variants']
        ],
    )
    conn.executemany(
        'INSERT INTO specs (model_no, kind, section, name, value) VALUES (?, ?, ?, ?, ?)',
        [
            (model_no, kind, section, name, value)
            for kind, key in (('spec', 'specifications'), ('feature', 'features'))
            for section, items in d[key].items()
            for name, value in items
        ],
    )
    conn.executemany(
        'INSERT INTO notes (model_no, kind, text) VALUES (?, ?, ?)',
        [
            (model_no, kind, text)
            for key, kind in NOTE_KINDS.items()
            for text in d[key]
            if text
        ],
    )
    conn.executemany(
        'INSERT INTO expert_reviews (model_no, title, rating, text) VALUES (?, ?, ?, ?)',
        [(model_no, s['title'], s['rating'], s['text']) for s in d['expert_sections']],
    )
    conn.executemany(
        'INSERT INTO mileage (model_no, fuel, transmission, mileage) VALUES (?, ?, ?, ?)',
        [(model_no, *(list(row) + [None] * 3)[:3]) for row in d['mileage_table']],
    )


# ---------------------------------------------------------
# READ
# ---------------------------------------------------------
def brand_names():
  with closing(connect()) as conn:
    return [r['name'] for r in conn.execute('SELECT name FROM brands ORDER BY name')]


def last_sync_time():
  with closing(connect()) as conn:
    return conn.execute('SELECT MAX(last_synced) FROM brands').fetchone()[0]


def stats():
  with closing(connect()) as conn:
    return dict(conn.execute(
        '''SELECT COUNT(*) AS cars,
                  COUNT(details_updated) AS with_details,
                  (SELECT COUNT(*) FROM variants) AS variants
           FROM cars'''
    ).fetchone())


def cars_needing_details(limit=None):
  with closing(connect()) as conn:
    return conn.execute(
        'SELECT model_no, car_name, source_url FROM cars '
        'WHERE details_updated IS NULL LIMIT ?',
        (limit or -1,),
    ).fetchall()


def all_cars():
  with closing(connect()) as conn:
    return conn.execute('SELECT model_no, car_name, source_url FROM cars').fetchall()


def top_rated_cars(limit=4):
  with closing(connect()) as conn:
    rows = conn.execute(
        'SELECT * FROM cars WHERE user_rating IS NOT NULL '
        'ORDER BY user_rating DESC, review_count DESC LIMIT ?',
        (limit,),
    ).fetchall()
    return [dict(r) for r in rows]


def body_type_stats(limit=6):
  with closing(connect()) as conn:
    rows = conn.execute(
        "SELECT body_type, COUNT(*) AS n FROM cars WHERE body_type != '-' "
        'GROUP BY body_type ORDER BY n DESC LIMIT ?',
        (limit,),
    ).fetchall()
    return [dict(r) for r in rows]


FUEL_OPTIONS = ['Petrol', 'Diesel', 'CNG', 'Electric']
TRANSMISSION_OPTIONS = ['Manual', 'Automatic']
SORTS = {
    'relevance': None,  # budget ke sabse paas pehle
    'price_asc': 'min_price ASC',
    'price_desc': 'min_price DESC',
    'rating': 'COALESCE(user_rating, 0) DESC',
    'mileage': 'CAST(mileage AS REAL) DESC',
}


def filter_options():
  """Filters ke liye DB me maujood values."""
  with closing(connect()) as conn:
    bodies = [r[0] for r in conn.execute(
        "SELECT body_type FROM cars WHERE body_type != '-' "
        'GROUP BY body_type ORDER BY COUNT(*) DESC'
    )]
    low, high = conn.execute('SELECT MIN(min_price), MAX(max_price) FROM cars').fetchone()
  return {
      'brands': brand_names(),
      'bodies': bodies,
      'fuels': FUEL_OPTIONS,
      'transmissions': TRANSMISSION_OPTIONS,
      'min_price': int(low or 0),
      'max_price': int((high or 50) + 1),
  }


def search_cars(budget=None, tolerance=1.0, brands=(), bodies=(), fuels=(),
                transmissions=(), sort='relevance'):
  """Filters ke hisaab se cars. Budget diya ho to jin cars ki price range

  budget (± tolerance lakh) ko touch karti hai, saath me budget wale variants.
  """
  where, params = [], []
  if budget:
    low, high = budget - tolerance, budget + tolerance
    where.append('min_price <= ? AND max_price >= ?')
    params += [high, low]
  for column, values in (('brand', brands), ('body_type', bodies)):
    if values:
      where.append(f"{column} IN ({','.join('?' * len(values))})")
      params += list(values)
  # fuel_type 'Petrol/CNG' jaisa hota hai, isliye LIKE
  for column, values in (('fuel_type', fuels), ('transmission', transmissions)):
    if values:
      where.append('(' + ' OR '.join(f'{column} LIKE ?' for _ in values) + ')')
      params += [f'%{v}%' for v in values]

  order = SORTS.get(sort)
  if not order:
    order = 'ABS(min_price - ?)' if budget else 'brand, min_price'
    if budget:
      params.append(budget)

  sql = 'SELECT * FROM cars'
  if where:
    sql += ' WHERE ' + ' AND '.join(where)
  sql += f' ORDER BY {order}'

  with closing(connect()) as conn:
    rows = conn.execute(sql, params).fetchall()
    variants = []
    if budget:
      variants = conn.execute(
          'SELECT model_no, name, price FROM variants '
          'WHERE price_lakh BETWEEN ? AND ? ORDER BY price_lakh',
          (low, high),
      ).fetchall()

  variants_by_car = {}
  for v in variants:
    variants_by_car.setdefault(v['model_no'], []).append(dict(v))

  cars = []
  for row in rows:
    car = dict(row)
    car['budget_variants'] = variants_by_car.get(car['model_no'], [])
    cars.append(car)
  return cars


# Compare table ki rows: (label, car column ya 'spec:<spec ka naam>')
COMPARE_ROWS = [
    ('Price (Ex-showroom)', 'price'),
    ('Body Type', 'body_type'),
    ('Fuel', 'fuel_type'),
    ('Transmission', 'transmission'),
    ('Mileage', 'mileage'),
    ('Engine', 'engine'),
    ('Max Power', 'spec:Max Power'),
    ('Max Torque', 'spec:Max Torque'),
    ('Seating', 'seating'),
    ('Safety Rating', 'safety_rating'),
    ('Boot Space', 'spec:Boot Space'),
    ('Ground Clearance', 'spec:Ground Clearance Unladen'),
    ('Length', 'spec:Length'),
    ('Width', 'spec:Width'),
    ('Wheel Base', 'spec:Wheel Base'),
    ('Drive Type', 'spec:Drive Type'),
    ('Range (EV)', 'spec:Range'),
    ('Battery (EV)', 'spec:Battery Capacity'),
    ('User Rating', 'user_rating'),
    ('Expert Rating', 'expert_rating'),
]


def compare_cars(model_nos):
  """Side-by-side compare data: cars, key rows aur feature-wise values."""
  model_nos = list(dict.fromkeys(model_nos))[:4]
  if not model_nos:
    return [], [], {}
  marks = ','.join('?' * len(model_nos))
  with closing(connect()) as conn:
    found = {r['model_no']: dict(r) for r in conn.execute(
        f'SELECT * FROM cars WHERE model_no IN ({marks})', model_nos)}
    specs = conn.execute(
        'SELECT model_no, kind, section, name, value FROM specs '
        f'WHERE model_no IN ({marks}) ORDER BY id', model_nos).fetchall()
    variant_counts = dict(conn.execute(
        'SELECT model_no, COUNT(*) FROM variants '
        f'WHERE model_no IN ({marks}) GROUP BY model_no', model_nos).fetchall())

  cars = [found[m] for m in model_nos if m in found]
  spec_values = {car['model_no']: {} for car in cars}
  features = {}  # section -> name -> {model_no: value}
  for s in specs:
    if s['kind'] == 'spec':
      spec_values[s['model_no']].setdefault(s['name'], s['value'])
    elif not (s['section'] or '').startswith('Key features'):
      features.setdefault(s['section'], {}).setdefault(s['name'], {})[s['model_no']] = s['value']

  rows = []
  for label, key in COMPARE_ROWS:
    if key.startswith('spec:'):
      values = [spec_values[c['model_no']].get(key[5:]) for c in cars]
    else:
      values = [c.get(key) for c in cars]
    values = [v if v not in (None, '', '-') else None for v in values]
    if any(values):
      rows.append((label, values))
  rows.append(('Variants', [variant_counts.get(c['model_no'], 0) for c in cars]))

  feature_rows = {
      section: [(name, [by_car.get(c['model_no']) for c in cars])
                for name, by_car in items.items()]
      for section, items in features.items()
  }
  return cars, rows, feature_rows


def brand_stats():
  with closing(connect()) as conn:
    return [dict(r) for r in conn.execute(
        'SELECT b.name, b.source_url, b.last_synced, '
        '       COUNT(c.model_no) AS cars, COUNT(c.details_updated) AS with_details, '
        '       MIN(c.min_price) AS min_price, MAX(c.max_price) AS max_price '
        'FROM brands b LEFT JOIN cars c ON c.brand = b.name '
        'GROUP BY b.name ORDER BY b.name')]


def admin_car_list():
  with closing(connect()) as conn:
    return [dict(r) for r in conn.execute(
        'SELECT c.model_no, c.brand, c.car_name, c.price, c.body_type, '
        '       c.safety_rating, c.last_updated, c.details_updated, '
        '       (SELECT COUNT(*) FROM variants v WHERE v.model_no = c.model_no) AS variants, '
        '       (SELECT COUNT(*) FROM specs s WHERE s.model_no = c.model_no) AS specs '
        'FROM cars c ORDER BY c.brand, c.min_price')]


def delete_car(model_no):
  with closing(connect()) as conn, conn:
    conn.execute('DELETE FROM cars WHERE model_no=?', (model_no,))


def get_car(model_no):
  """(car dict, details dict) - details ka format template ke hisaab se."""
  with closing(connect()) as conn:
    car = conn.execute('SELECT * FROM cars WHERE model_no=?', (model_no,)).fetchone()
    if not car:
      return None, {}
    car = dict(car)
    if not car['details_updated']:
      return car, {}

    q = lambda sql: conn.execute(sql, (model_no,)).fetchall()
    notes = {kind: [] for kind in NOTE_KINDS.values()}
    for r in q('SELECT kind, text FROM notes WHERE model_no=? ORDER BY id'):
      notes.setdefault(r['kind'], []).append(r['text'])

    grouped = {'spec': {}, 'feature': {}}
    for r in q('SELECT kind, section, name, value FROM specs WHERE model_no=? ORDER BY id'):
      grouped[r['kind']].setdefault(r['section'], []).append((r['name'], r['value']))

    details = {
        'description': car['description'],
        'expert_verdict': car['expert_verdict'],
        'expert_rating': car['expert_rating'],
        'user_rating': car['user_rating'],
        'review_count': car['review_count'],
        'mileage_summary': car['mileage_summary'],
        'brochure_url': car['brochure_url'],
        'pros': notes['pro'],
        'cons': notes['con'],
        'highlights': notes['highlight'],
        'latest_updates': notes['update'],
        'colours': notes['colour'],
        'variants': [dict(r) for r in q('SELECT * FROM variants WHERE model_no=? ORDER BY id')],
        'specifications': grouped['spec'],
        'features': grouped['feature'],
        'expert_sections': [
            dict(r) for r in q('SELECT title, rating, text FROM expert_reviews WHERE model_no=? ORDER BY id')
        ],
        'mileage_table': [
            (r['fuel'], r['transmission'], r['mileage'])
            for r in q('SELECT * FROM mileage WHERE model_no=? ORDER BY id')
        ],
    }
  return car, details


# ---------------------------------------------------------
# BLOG
# ---------------------------------------------------------
def slugify(title):
  slug = re.sub(r'[^a-z0-9]+', '-', title.lower()).strip('-')
  return slug or 'post'


def unique_slug(title, post_id=None):
  base = slugify(title)
  slug = base
  with closing(connect()) as conn:
    n = 2
    while conn.execute(
        'SELECT 1 FROM posts WHERE slug=? AND id IS NOT ?', (slug, post_id or -1)
    ).fetchone():
      slug = f'{base}-{n}'
      n += 1
  return slug


def list_posts(published_only=True, limit=None, exclude_id=None):
  sql = 'SELECT * FROM posts'
  where, params = [], []
  if published_only:
    where.append('published=1')
  if exclude_id:
    where.append('id != ?')
    params.append(exclude_id)
  if where:
    sql += ' WHERE ' + ' AND '.join(where)
  sql += ' ORDER BY created_at DESC'
  if limit:
    sql += ' LIMIT ?'
    params.append(limit)
  with closing(connect()) as conn:
    return [dict(r) for r in conn.execute(sql, params)]


def get_post(slug):
  with closing(connect()) as conn:
    row = conn.execute('SELECT * FROM posts WHERE slug=?', (slug,)).fetchone()
    return dict(row) if row else None


def get_post_by_id(post_id):
  with closing(connect()) as conn:
    row = conn.execute('SELECT * FROM posts WHERE id=?', (post_id,)).fetchone()
    return dict(row) if row else None


def save_post(post_id, title, excerpt, content, cover_image, author, tag, published):
  """post_id None ho to naya post banata hai, warna existing update karta hai."""
  slug = unique_slug(title, post_id)
  with closing(connect()) as conn, conn:
    if post_id:
      conn.execute(
          '''UPDATE posts SET title=?, slug=?, excerpt=?, content=?, cover_image=?,
                 author=?, tag=?, published=?, updated_at=datetime('now')
             WHERE id=?''',
          (title, slug, excerpt, content, cover_image, author, tag, published, post_id),
      )
    else:
      conn.execute(
          '''INSERT INTO posts (slug, title, excerpt, content, cover_image, author, tag, published)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
          (slug, title, excerpt, content, cover_image, author, tag, published),
      )
  return slug


def save_scraped_post(source_url, title, excerpt, content, cover_image='', author='Source editorial', tag='Imported'):
  """Upsert an imported story by source URL; imports remain drafts for review."""
  source_url = (source_url or '').strip()
  title = (title or '').strip()
  if not source_url or not title:
    raise ValueError('Imported post is missing its source URL or title.')
  with closing(connect()) as conn, conn:
    row = conn.execute('SELECT id FROM posts WHERE source_url=?', (source_url,)).fetchone()
    if row:
      post_id = row['id']
      slug = unique_slug(title, post_id)
      conn.execute('''UPDATE posts SET title=?,slug=?,excerpt=?,content=?,cover_image=?,author=?,tag=?,
                      updated_at=datetime('now'),imported_at=datetime('now') WHERE id=?''',
                   (title, slug, excerpt or '', content or '', cover_image or '', author or 'Source editorial', tag or 'Imported', post_id))
      return post_id, slug, False
    slug = unique_slug(title)
    cur = conn.execute('''INSERT INTO posts
        (slug,title,excerpt,content,cover_image,author,tag,published,source_url,imported_at)
        VALUES (?,?,?,?,?,?,?,0,?,datetime('now'))''',
        (slug, title, excerpt or '', content or '', cover_image or '', author or 'Source editorial', tag or 'Imported', source_url))
    return cur.lastrowid, slug, True


def delete_post(post_id):
  with closing(connect()) as conn, conn:
    conn.execute('DELETE FROM posts WHERE id=?', (post_id,))


def toggle_publish(post_id):
  with closing(connect()) as conn, conn:
    conn.execute('UPDATE posts SET published = 1 - published WHERE id=?', (post_id,))


SAMPLE_POSTS = [
    dict(
        title='Budget 10 Lakh Me Best Family Cars 2025',
        tag='Buying Guide',
        cover_image='https://images.unsplash.com/photo-1568605117036-5fe5e7bab0b7?w=1200&q=80',
        excerpt='10 lakh ke budget me kaunsi cars sabse zyada value deti hain — space, safety '
                'aur running cost teeno ka balance dhundhte hue humne yeh list banayi hai.',
        content=(
            '10 lakh rupaye ka budget India me car khareedne ke liye ek sweet spot hai — is range '
            'me aapko achi safety, decent features aur reliable engines mil jaate hain.\n\n'
            'Family ke liye car choose karte waqt sabse pehle boot space aur rear seat legroom '
            'dekhna chahiye. Lambi drives par yeh do cheezein sabse zyada matter karti hain.\n\n'
            'Safety rating bhi ignore mat kijiye — Global NCAP ya Bharat NCAP ki 4-star aur usse '
            'upar wali cars ko priority dein, especially agar aap highway par zyada drive karte hain.\n\n'
            'Hamari filter tool use karke aap apne budget ke hisaab se sabhi qualifying cars ek '
            'saath dekh sakte hain aur unhe side-by-side compare bhi kar sakte hain.'
        ),
    ),
    dict(
        title='Petrol vs Diesel vs CNG: Kaunsa Fuel Aapke Liye Sahi Hai?',
        tag='Buying Guide',
        cover_image='https://images.unsplash.com/photo-1545558014-8692077e9b5c?w=1200&q=80',
        excerpt='Har fuel type ke apne trade-offs hain — running cost, resale value aur '
                'performance ke hisaab se yeh guide aapki confusion door karega.',
        content=(
            'Petrol cars sabse smooth aur refined driving experience deti hain, especially city '
            'traffic me. Inki resale value bhi generally acchi rehti hai.\n\n'
            'Diesel cars high mileage aur torque ke liye jaani jaati hain — agar aap monthly 1500 '
            'km se zyada drive karte hain to diesel ka running cost fayda lambe time me dikhta hai.\n\n'
            'CNG sabse kam running cost deta hai, lekin boot space thoda kam ho jaata hai tank ki '
            'wajah se aur service network bhi sab jagah utna strong nahi hai.\n\n'
            'Electric cars ab is list me tezi se jud rahi hain — agar aapke paas home charging ka '
            'option hai to yeh long-term me sabse saste chalti hain.'
        ),
    ),
    dict(
        title='Car Safety Rating Samajhna: NCAP Stars Ka Matlab',
        tag='Safety',
        cover_image='https://images.unsplash.com/photo-1503376780353-7e6692767b70?w=1200&q=80',
        excerpt='5-star safety rating dekhkar car khareedna kaafi nahi — jaaniye rating '
                'actually kya measure karti hai aur kya nahi.',
        content=(
            'NCAP crash test cars ko standardized frontal aur side-impact crashes me daal kar '
            'unki structural strength aur occupant protection measure karta hai.\n\n'
            'Adult Safety aur Child Safety alag-alag score hote hain — dono dekhna zaroori hai, '
            'kyunki ek car adult occupants ke liye strong ho sakti hai lekin child seat anchors '
            'weak ho sakte hain.\n\n'
            'Rating sirf ek starting point hai — dual airbags, ABS, ESC jaise features standard '
            'fitment me hain ya nahi, yeh bhi zaroor check karein.\n\n'
            'Hamare har car detail page par aapko available safety rating aur uske saath key '
            'safety features ki poori list milegi.'
        ),
    ),
]


def seed_posts_if_empty():
  with closing(connect()) as conn:
    count = conn.execute('SELECT COUNT(*) FROM posts').fetchone()[0]
  if count:
    return
  for p in SAMPLE_POSTS:
    save_post(None, p['title'], p['excerpt'], p['content'], p['cover_image'],
               'CarCompare Team', p['tag'], 1)