8da149b84e
- Add asyncpg connection pool (db/pool.py) with JSONB codec registration - Add schema.sql with users, clients, dropdown_categories, export_templates, sheets tables - Add migrate_json.py one-time migration script for existing JSON data - Rewrite user_store, sheets/manager, api/clients, api/dropdowns, api/export as async DB-backed - Update all callers (auth, sheets, admin, ai_command, export) to await async functions - Add postgres:16-alpine service to docker-compose with named volume and health check - App container depends_on postgres; DATABASE_URL injected via env - Schema applied automatically on startup; global categories seeded if DB is empty Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
143 lines
4.7 KiB
Python
143 lines
4.7 KiB
Python
"""
|
|
Sheet management — PostgreSQL-backed.
|
|
All functions are async.
|
|
"""
|
|
|
|
import logging
|
|
import re
|
|
from datetime import datetime, timezone
|
|
from typing import List, Optional, Dict
|
|
import time
|
|
import random
|
|
|
|
from ..db.pool import get_pool
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
async def get_user_sheets(user_id: str) -> List[Dict]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
rows = await conn.fetch(
|
|
'SELECT id, name, client_id, item_count, created_at, modified_at '
|
|
'FROM sheets WHERE user_id = $1 ORDER BY modified_at DESC',
|
|
user_id
|
|
)
|
|
return [_row_to_meta(r, user_id) for r in rows]
|
|
|
|
|
|
async def create_sheet(user_id: str, name: str, data: List[dict] = None, client_id: str = '') -> Dict:
|
|
if data is None:
|
|
data = []
|
|
sheet_id = str(int(time.time())) + str(random.randint(100, 999))
|
|
sheet_name = name or f"Untitled Sheet — {datetime.now().strftime('%Y-%m-%d %H:%M')}"
|
|
client_id_val = client_id or None
|
|
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow('''
|
|
INSERT INTO sheets (id, user_id, name, client_id, data, item_count)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING id, name, client_id, item_count, created_at, modified_at
|
|
''', sheet_id, user_id, sheet_name, client_id_val, data, len(data))
|
|
return _row_to_meta(row, user_id)
|
|
|
|
|
|
async def load_sheet_data(user_id: str, sheet_id: str) -> Optional[List[dict]]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
'SELECT data FROM sheets WHERE id = $1 AND user_id = $2',
|
|
sheet_id, user_id
|
|
)
|
|
if row is None:
|
|
return None
|
|
return row['data'] if row['data'] is not None else []
|
|
|
|
|
|
async def update_sheet(user_id: str, sheet_id: str, data: List[dict]) -> bool:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
result = await conn.execute('''
|
|
UPDATE sheets SET data = $3, item_count = $4, modified_at = NOW()
|
|
WHERE id = $1 AND user_id = $2
|
|
''', sheet_id, user_id, data, len(data))
|
|
return result != 'UPDATE 0'
|
|
|
|
|
|
async def delete_sheet(user_id: str, sheet_id: str):
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
await conn.execute(
|
|
'DELETE FROM sheets WHERE id = $1 AND user_id = $2',
|
|
sheet_id, user_id
|
|
)
|
|
|
|
|
|
async def rename_sheet(user_id: str, sheet_id: str, new_name: str) -> bool:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
result = await conn.execute('''
|
|
UPDATE sheets SET name = $3, modified_at = NOW()
|
|
WHERE id = $1 AND user_id = $2
|
|
''', sheet_id, user_id, new_name)
|
|
return result != 'UPDATE 0'
|
|
|
|
|
|
async def duplicate_sheet(user_id: str, sheet_id: str) -> Optional[Dict]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
'SELECT name, data FROM sheets WHERE id = $1 AND user_id = $2',
|
|
sheet_id, user_id
|
|
)
|
|
if row is None:
|
|
return None
|
|
return await create_sheet(user_id, f"Copy of {row['name']}", row['data'])
|
|
|
|
|
|
async def get_sheet_client_id(user_id: str, sheet_id: str) -> Optional[str]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
'SELECT client_id FROM sheets WHERE id = $1 AND user_id = $2',
|
|
sheet_id, user_id
|
|
)
|
|
if row is None:
|
|
return None
|
|
return row['client_id']
|
|
|
|
|
|
async def set_sheet_client_id(user_id: str, sheet_id: str, client_id: str):
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
await conn.execute('''
|
|
UPDATE sheets SET client_id = $3, modified_at = NOW()
|
|
WHERE id = $1 AND user_id = $2
|
|
''', sheet_id, user_id, client_id or None)
|
|
|
|
|
|
def generate_next_id(data: List[dict]) -> str:
|
|
"""Generate the next DEL-NNN id. Remains sync — operates on in-memory data."""
|
|
max_id = 0
|
|
for row in data:
|
|
num_str = row.get('Number', '').replace('DEL-', '')
|
|
try:
|
|
n = int(num_str)
|
|
if n > max_id:
|
|
max_id = n
|
|
except (ValueError, AttributeError):
|
|
pass
|
|
return f"DEL-{str(max_id + 1).zfill(3)}"
|
|
|
|
|
|
def _row_to_meta(row, user_id: str) -> Dict:
|
|
return {
|
|
'id': row['id'],
|
|
'name': row['name'],
|
|
'client_id': row['client_id'],
|
|
'itemCount': row['item_count'],
|
|
'user': user_id,
|
|
'created': row['created_at'].isoformat() if row['created_at'] else None,
|
|
'modified': row['modified_at'].isoformat() if row['modified_at'] else None,
|
|
}
|