utils/streamDb.js

const { getDb, scheduleSave } = require('./db')

const FRESH_DAYS = 30
const domainCache = new Map()

function parseUrl(url) {
  try {
    const u = new URL(url)
    return { domain: u.origin, path: u.pathname + u.search + u.hash }
  } catch {
    return { domain: '', path: url }
  }
}

async function getDomainId(db, domain) {
  if (domainCache.has(domain)) return domainCache.get(domain)

  const stmt = db.prepare(`SELECT id FROM domains WHERE domain = ?`)
  stmt.bind([domain])
  const found = stmt.step()
  if (found) {
    const id = stmt.getAsObject().id
    stmt.free()
    domainCache.set(domain, id)
    return id
  }
  stmt.free()

  try {
    db.run(`INSERT INTO domains (domain) VALUES (?)`, [domain])
  } catch {
    const retryStmt = db.prepare(`SELECT id FROM domains WHERE domain = ?`)
    retryStmt.bind([domain])
    retryStmt.step()
    const id = retryStmt.getAsObject().id
    retryStmt.free()
    domainCache.set(domain, id)
    return id
  }
  const r = db.exec('SELECT last_insert_rowid()')
  const id = r[0].values[0][0]
  domainCache.set(domain, id)
  return id
}

async function getFreshStreams(tmdbId, season, episode) {
  const db = await getDb()
  let where = `sr.tmdb_id = ? AND sr.is_alive = 1 AND sr.last_seen > datetime('now', '-' || ? || ' days')`
  const params = [String(tmdbId), FRESH_DAYS]

  if (season != null && episode != null) {
    where += ` AND sr.season = ? AND sr.episode = ?`
    params.push(season, episode)
  } else {
    where += ` AND sr.season IS NULL AND sr.episode IS NULL`
  }

  const stmt = db.prepare(`
    SELECT sr.*, d.domain || sr.url_path as url
    FROM stream_results sr
    JOIN domains d ON d.id = sr.domain_id
    WHERE ${where}
    ORDER BY sr.quality DESC
  `)
  stmt.bind(params)
  const results = []
  while (stmt.step()) {
    results.push(stmt.getAsObject())
  }
  stmt.free()
  return results
}

async function getAllStreams(tmdbId, season, episode) {
  const db = await getDb()
  let where = `sr.tmdb_id = ? AND sr.is_alive = 1`
  const params = [String(tmdbId)]

  if (season != null && episode != null) {
    where += ` AND sr.season = ? AND sr.episode = ?`
    params.push(season, episode)
  } else {
    where += ` AND sr.season IS NULL AND sr.episode IS NULL`
  }

  const stmt = db.prepare(`
    SELECT sr.*, d.domain || sr.url_path as url
    FROM stream_results sr
    JOIN domains d ON d.id = sr.domain_id
    WHERE ${where}
    ORDER BY sr.last_seen DESC
  `)
  stmt.bind(params)
  const results = []
  while (stmt.step()) {
    results.push(stmt.getAsObject())
  }
  stmt.free()
  return results
}

async function logStreams(tmdbId, provider, contentType, streams, title, season, episode) {
  const db = await getDb()
  const now = new Date().toISOString()
  const seasonVal = season != null ? season : null
  const episodeVal = episode != null ? episode : null

  const checkStmt = db.prepare(`
    SELECT sr.id FROM stream_results sr
    JOIN domains d ON d.id = sr.domain_id
    WHERE d.domain = ? AND sr.url_path = ? AND sr.provider = ? AND sr.tmdb_id = ?
      AND sr.season IS ? AND sr.episode IS ?
  `)
  const insertStmt = db.prepare(`
    INSERT INTO stream_results (domain_id, url_path, provider, content_type, tmdb_id, season, episode, title, quality, language, created_at, last_seen)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `)
  const updateStmt = db.prepare(`
    UPDATE stream_results SET last_seen = ?, is_alive = 1
    WHERE domain_id = ? AND url_path = ? AND provider = ? AND tmdb_id = ?
      AND season IS ? AND episode IS ?
  `)

  for (const s of streams) {
    const { domain, path } = parseUrl(s.url)
    const domainId = await getDomainId(db, domain)

    checkStmt.bind([domain, path, provider, String(tmdbId), seasonVal, episodeVal])
    const exists = checkStmt.step()

    if (exists) {
      updateStmt.run([now, domainId, path, provider, String(tmdbId), seasonVal, episodeVal])
    } else {
      insertStmt.run([
        domainId,
        path,
        provider,
        contentType,
        String(tmdbId),
        seasonVal,
        episodeVal,
        title || s.title || null,
        s.quality || null,
        s.language || null,
        now,
        now,
      ])
    }
  }
  checkStmt.free()
  insertStmt.free()
  updateStmt.free()
  scheduleSave()
}

async function markDead(url, provider) {
  const db = await getDb()
  const { domain, path } = parseUrl(url)
  db.run(`
    DELETE FROM stream_results
    WHERE domain_id = (SELECT id FROM domains WHERE domain = ?)
      AND url_path = ? AND provider = ?
  `, [domain, path, provider || ''])
  scheduleSave()
}

async function markAlive(url, provider) {
  const db = await getDb()
  const { domain, path } = parseUrl(url)
  const now = new Date().toISOString()
  db.run(`
    UPDATE stream_results
    SET is_alive = 1, last_seen = ?, last_validated = ?
    WHERE domain_id = (SELECT id FROM domains WHERE domain = ?)
      AND url_path = ? AND provider = ?
  `, [now, now, domain, path, provider || ''])
  scheduleSave()
}

async function getProvidersForTmdb(tmdbId) {
  const db = await getDb()
  const stmt = db.prepare(`
    SELECT DISTINCT provider FROM stream_results
    WHERE tmdb_id = ? AND is_alive = 1
  `)
  stmt.bind([String(tmdbId)])
  const providers = []
  while (stmt.step()) {
    providers.push(stmt.getAsObject().provider)
  }
  stmt.free()
  return providers
}

async function getStats() {
  const db = await getDb()

  const total = db.exec(`SELECT COUNT(*) FROM stream_results WHERE is_alive = 1`)
  const totalStreams = total.length > 0 ? total[0].values[0][0] : 0

  const totalDomains = db.exec(`SELECT COUNT(*) FROM domains`)
  const domainsCount = totalDomains.length > 0 ? totalDomains[0].values[0][0] : 0

  const byProvider = db.exec(`
    SELECT provider,
      COUNT(*) as total,
      SUM(CASE WHEN is_alive = 1 THEN 1 ELSE 0 END) as alive,
      MAX(last_seen) as last_seen
    FROM stream_results
    GROUP BY provider
    ORDER BY total DESC
  `)

  const recentChecks = db.exec(`
    SELECT COUNT(*) FROM stream_checks
    WHERE checked_at > datetime('now', '-24 hours')
  `)
  const checks24h = recentChecks.length > 0 ? recentChecks[0].values[0][0] : 0

  const stale = db.exec(`
    SELECT COUNT(*) FROM stream_results
    WHERE is_alive = 1 AND last_seen < datetime('now', '-${FRESH_DAYS} days')
  `)
  const staleCount = stale.length > 0 ? stale[0].values[0][0] : 0

  const providers = []
  if (byProvider.length > 0) {
    for (const row of byProvider[0].values) {
      providers.push({
        provider: row[0],
        total: row[1],
        alive: row[2],
        last_seen: row[3],
      })
    }
  }

  return {
    totalStreams,
    domainsCount,
    staleCount,
    checks24h,
    providers,
  }
}

async function logCheck(url, result) {
  const db = await getDb()
  db.run(`
    INSERT INTO stream_checks (url, checked_at, response_ms, status_code, is_alive, error)
    VALUES (?, datetime('now'), ?, ?, ?, ?)
  `, [url, result.responseMs || null, result.statusCode || null, result.isAlive ? 1 : 0, result.error || null])
  scheduleSave()
}

async function deleteStale(maxAgeDays) {
  const db = await getDb()
  db.run(`
    DELETE FROM stream_results
    WHERE last_seen < datetime('now', '-' || ? || ' days')
      AND is_alive = 0
  `, [maxAgeDays || 90])
  scheduleSave()
}

async function pruneChecks(maxAgeDays) {
  const db = await getDb()
  db.run(`
    DELETE FROM stream_checks
    WHERE checked_at < datetime('now', '-' || ? || ' days')
  `, [maxAgeDays || 90])
  scheduleSave()
}

async function pruneOrphanDomains() {
  const db = await getDb()
  db.run(`
    DELETE FROM domains
    WHERE id NOT IN (SELECT DISTINCT domain_id FROM stream_results)
  `)
  scheduleSave()
}

module.exports = {
  getFreshStreams,
  getAllStreams,
  logStreams,
  markDead,
  markAlive,
  getProvidersForTmdb,
  getStats,
  logCheck,
  deleteStale,
  pruneChecks,
  pruneOrphanDomains,
}