init_db.py

"""
init_db.py — Creates the users table if it does not exist.
Works with both Neon PostgreSQL (production) and SQLite (local dev).
"""

from db import get_conn, USE_POSTGRES
import logging

logger = logging.getLogger(__name__)


def init_db():
    conn = get_conn()
    try:
        cursor = conn.cursor()

        if USE_POSTGRES:
            # PostgreSQL schema
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id         SERIAL PRIMARY KEY,
                    username   TEXT UNIQUE NOT NULL,
                    email      TEXT,
                    password   TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
        else:
            # SQLite schema
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id         INTEGER PRIMARY KEY AUTOINCREMENT,
                    username   TEXT UNIQUE NOT NULL,
                    email      TEXT,
                    password   TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            # Migration safety for existing SQLite DBs
            try:
                cursor.execute('ALTER TABLE users ADD COLUMN email TEXT')
            except Exception:
                pass  # column already exists

        conn.commit()
        logger.info("Database initialized successfully.")
    except Exception as e:
        try:
            conn.rollback()
        except Exception:
            pass
        logger.warning(f"Database init warning: {e}")
    finally:
        try:
            conn.close()
        except Exception:
            pass


if __name__ == '__main__':
    init_db()
    print("Database initialized.")