Spaces:
Sleeping
Sleeping
| import os | |
| import asyncpg | |
| DB_CONFIG = { | |
| "user": os.getenv("DB_USER", "postgres"), | |
| "password": os.getenv("DB_PASSWORD", "password"), | |
| "database": os.getenv("DB_NAME", "mindless"), | |
| "host": os.getenv("DB_HOST", "localhost"), | |
| "port": int(os.getenv("DB_PORT", 5432)), | |
| } | |
| CREATE_TABLES_SQL = """ | |
| CREATE TABLE IF NOT EXISTS analytics_events ( | |
| id SERIAL PRIMARY KEY, | |
| session_id TEXT NOT NULL, | |
| user_id INTEGER, | |
| event_type TEXT NOT NULL, | |
| page TEXT, | |
| element TEXT, | |
| value TEXT, | |
| monetary_value NUMERIC(10,2), | |
| timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| ALTER TABLE analytics_events ADD COLUMN IF NOT EXISTS user_id INTEGER; | |
| ALTER TABLE analytics_events ADD COLUMN IF NOT EXISTS monetary_value NUMERIC(10,2); | |
| CREATE INDEX IF NOT EXISTS idx_events_type ON analytics_events (event_type); | |
| CREATE INDEX IF NOT EXISTS idx_events_ts ON analytics_events (timestamp); | |
| CREATE INDEX IF NOT EXISTS idx_events_session ON analytics_events (session_id); | |
| CREATE INDEX IF NOT EXISTS idx_events_user ON analytics_events (user_id); | |
| CREATE TABLE IF NOT EXISTS users ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT, | |
| email TEXT UNIQUE NOT NULL, | |
| password_hash TEXT NOT NULL, | |
| role TEXT NOT NULL DEFAULT 'user', | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE TABLE IF NOT EXISTS orders ( | |
| id SERIAL PRIMARY KEY, | |
| order_id TEXT UNIQUE NOT NULL, | |
| user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, | |
| items JSONB NOT NULL, | |
| delivery_info JSONB NOT NULL, | |
| payment_method TEXT NOT NULL, | |
| status TEXT NOT NULL DEFAULT 'Order Placed', | |
| total NUMERIC(10,2) NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_orders_user ON orders (user_id); | |
| CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status); | |
| CREATE INDEX IF NOT EXISTS idx_orders_created ON orders (created_at DESC); | |
| CREATE TABLE IF NOT EXISTS products ( | |
| id SERIAL PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| description TEXT NOT NULL DEFAULT '', | |
| price NUMERIC(10,2) NOT NULL, | |
| image JSONB NOT NULL DEFAULT '[]'::jsonb, | |
| category TEXT, | |
| sub_category TEXT, | |
| sizes JSONB NOT NULL DEFAULT '[]'::jsonb, | |
| bestseller BOOLEAN NOT NULL DEFAULT FALSE, | |
| date BIGINT NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_products_category ON products (category); | |
| CREATE INDEX IF NOT EXISTS idx_products_bestseller ON products (bestseller); | |
| ALTER TABLE products ADD COLUMN IF NOT EXISTS stock INTEGER NOT NULL DEFAULT 100; | |
| CREATE TABLE IF NOT EXISTS audit_log ( | |
| id SERIAL PRIMARY KEY, | |
| actor_id INTEGER, | |
| actor_email TEXT, | |
| action TEXT NOT NULL, | |
| target TEXT, | |
| detail JSONB, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_audit_actor ON audit_log (actor_id); | |
| CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log (created_at DESC); | |
| """ | |
| _pool: asyncpg.Pool | None = None | |
| async def init_asyncpg_pool() -> None: | |
| """Create the asyncpg pool and ensure shop-side tables exist.""" | |
| global _pool | |
| _pool = await asyncpg.create_pool(**DB_CONFIG) | |
| async with _pool.acquire() as conn: | |
| await conn.execute(CREATE_TABLES_SQL) | |
| print("asyncpg pool ready: users, orders, analytics_events, products tables verified.") | |
| async def close_asyncpg_pool() -> None: | |
| global _pool | |
| if _pool is not None: | |
| await _pool.close() | |
| _pool = None | |
| print("asyncpg pool closed.") | |
| async def get_asyncpg_conn(): | |
| """FastAPI dependency: yields a pooled asyncpg connection.""" | |
| if _pool is None: | |
| raise RuntimeError("asyncpg pool not initialized. Call init_asyncpg_pool first.") | |
| async with _pool.acquire() as conn: | |
| yield conn | |