You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
86 lines
2.7 KiB
Python
86 lines
2.7 KiB
Python
import psycopg2.extras
|
|
from datetime import datetime, timedelta
|
|
from config import get_local_db_connection
|
|
|
|
# ───────── DB HELPER ───────── #
|
|
def db_get_videos(username: str = None, start=None, end=None):
|
|
"""
|
|
Fetch videos, optionally filtered by username and created_at date range.
|
|
`start` / `end` can be date or datetime (UTC). End is inclusive by day.
|
|
"""
|
|
conn, cur = get_local_db_connection()
|
|
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
|
|
|
|
query = """
|
|
SELECT video_id, username, site AS platform,
|
|
filepath, size, duration, gender,
|
|
created_at, updated_at, thumbnail
|
|
FROM videos
|
|
WHERE status = 'active'
|
|
"""
|
|
params = []
|
|
|
|
if username:
|
|
query += " AND username = %s"
|
|
params.append(username)
|
|
|
|
if start is not None:
|
|
# Normalize to datetime midnight if date
|
|
if hasattr(start, "hour") is False:
|
|
start = datetime.combine(start, datetime.min.time())
|
|
query += " AND created_at >= %s"
|
|
params.append(start)
|
|
|
|
if end is not None:
|
|
# Make end inclusive by bumping 1 day and using '<'
|
|
if hasattr(end, "hour") is False:
|
|
end = datetime.combine(end, datetime.min.time())
|
|
end_exclusive = end + timedelta(days=1)
|
|
query += " AND created_at < %s"
|
|
params.append(end_exclusive)
|
|
|
|
query += " ORDER BY created_at DESC"
|
|
|
|
cur.execute(query, params)
|
|
rows = cur.fetchall()
|
|
cur.close(); conn.close()
|
|
return [dict(r) for r in rows]
|
|
|
|
def db_get_video(video_id: str):
|
|
conn, cur = get_local_db_connection()
|
|
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
|
|
cur.execute("""
|
|
SELECT
|
|
video_id, username, site AS platform,
|
|
filepath, size, duration, gender,
|
|
created_at, updated_at, thumbnail
|
|
FROM videos
|
|
WHERE video_id = %s
|
|
""", (video_id,))
|
|
row = cur.fetchone()
|
|
cur.close(); conn.close()
|
|
return dict(row) if row else None
|
|
|
|
def db_get_recent(page: int, per_page: int):
|
|
offset = (page - 1) * per_page
|
|
conn, cur = get_local_db_connection()
|
|
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
|
|
|
|
cur.execute("""
|
|
SELECT
|
|
video_id, username, site AS platform,
|
|
filepath, size, duration, gender,
|
|
created_at, updated_at, thumbnail
|
|
FROM videos
|
|
WHERE status != 'missing'
|
|
ORDER BY created_at DESC NULLS LAST, updated_at DESC NULLS LAST
|
|
LIMIT %s OFFSET %s
|
|
""", (per_page, offset))
|
|
rows = [dict(r) for r in cur.fetchall()]
|
|
|
|
cur.execute("SELECT COUNT(*) FROM videos WHERE status != 'missing'")
|
|
total = cur.fetchone()[0]
|
|
|
|
cur.close(); conn.close()
|
|
return rows, total
|