""" Dropdown data API — category / media type hierarchy. Data stored in PostgreSQL. Seeded from embedded SEED_CATEGORIES if DB is empty. """ import logging from quart import Blueprint, jsonify, request from ..db.pool import get_pool logger = logging.getLogger(__name__) dropdowns_bp = Blueprint('dropdowns', __name__, url_prefix='/api/dropdowns') # Seed data embedded as fallback (from Excel Grid (1).xlsx) SEED_CATEGORIES = [ {"name": "3D", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "A/B Testing", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Admin", "status": "Active", "mediaTypes": ["Management"]}, {"name": "Amazon page", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Animation", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "App Design", "status": "Active", "mediaTypes": ["Online advertising - .com"]}, {"name": "Artworking (Print)", "status": "Active", "mediaTypes": ["Literature", "Catalogue", "Press - Magazine", "Press - Newspaper", "POS - Print", "POS - Digital", "OOH - Print", "Direct mail - Email", "Direct mail - Print"]}, {"name": "Audio", "status": "Active", "mediaTypes": ["Broadcast - Radio"]}, {"name": "Augmented Reality", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Branday Adaptation", "status": "Active", "mediaTypes": ["Online advertising - Rich media"]}, {"name": "Branding", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "CMS", "status": "Active", "mediaTypes": ["Online advertising - .com"]}, {"name": "Campaign Print Complex", "status": "Active", "mediaTypes": ["Press - Newspaper"]}, {"name": "Campaign Print Simple", "status": "Active", "mediaTypes": ["Press - Magazine"]}, {"name": "Cinema", "status": "Active", "mediaTypes": ["Broadcast - TV", "Broadcast - Cinema", "Broadcast - Radio"]}, {"name": "Cinema Adaptation", "status": "Active", "mediaTypes": ["Broadcast - Cinema"]}, {"name": "Community Management", "status": "Active", "mediaTypes": ["Community management"]}, {"name": "Concept (Video)", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Copywriting", "status": "Active", "mediaTypes": ["Literature", "Transcreation", "Copywriting"]}, {"name": "Copywriting Newsletter", "status": "Active", "mediaTypes": ["Direct mail - Email", "Direct mail - Print"]}, {"name": "Copywriting Social", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Creative Development", "status": "Active", "mediaTypes": ["Literature", "Creative development"]}, {"name": "Creative Development Big Campaign", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Creative Development Small Campaign", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Creative Direction", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Creative Packaging Box", "status": "Active", "mediaTypes": ["Packaging - Print"]}, {"name": "DM", "status": "Active", "mediaTypes": ["Direct mail - Print"]}, {"name": "Digital Display (.com)", "status": "Active", "mediaTypes": ["Online advertising - Banner", "Online advertising - Static Image"]}, {"name": "Digital Display (Animation)", "status": "Active", "mediaTypes": ["POS - Digital", "Online advertising - Banner", "Online advertising - Rich media", "Online advertising - Push notifications", "Online advertising - .com"]}, {"name": "Digital Display (POS)", "status": "Active", "mediaTypes": ["Online advertising - Banner", "Online advertising - Static Image"]}, {"name": "Digital Display (Push Notification)", "status": "Active", "mediaTypes": ["Online advertising - Banner", "Online advertising - Static Image"]}, {"name": "Digital Display (Rich Media)", "status": "Active", "mediaTypes": ["Online advertising - Static Image"]}, {"name": "Digital Display (Static)", "status": "Active", "mediaTypes": ["Online advertising - Static Image"]}, {"name": "Display Static Adaptation Standard formats", "status": "Active", "mediaTypes": ["Online advertising - Static Image"]}, {"name": "Display Static Master Standard formats", "status": "Active", "mediaTypes": ["Online advertising - Static Image"]}, {"name": "E-commerce site", "status": "Active", "mediaTypes": ["Online advertising - .com"]}, {"name": "Email", "status": "Active", "mediaTypes": ["Direct mail - Email"]}, {"name": "Event", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Event Management", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Illustration", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Image Adaptation Social", "status": "Active", "mediaTypes": ["Social - Static Image"]}, {"name": "Image Animation", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Infographics", "status": "Active", "mediaTypes": ["Literature", "Online advertising - Banner", "Online advertising - Rich media", "Online advertising - Landing page", "Online advertising - Push notifications"]}, {"name": "Internal Comms", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Key Visual", "status": "Active", "mediaTypes": ["Literature", "Social - Static Image"]}, {"name": "Key Visual Adaptation", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Key Visual Design", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Logo creation", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Management", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Mechandise", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Non-Project Time", "status": "Active", "mediaTypes": ["Management"]}, {"name": "OOH (Digital)", "status": "Active", "mediaTypes": ["OOH - Digital"]}, {"name": "OOH (Print)", "status": "Active", "mediaTypes": ["OOH - Print"]}, {"name": "OOH Complex (Digital)", "status": "Active", "mediaTypes": ["OOH - Digital"]}, {"name": "OOH Complex (Print)", "status": "Active", "mediaTypes": ["OOH - Print"]}, {"name": "OOH Simple (Digital)", "status": "Active", "mediaTypes": ["OOH - Digital"]}, {"name": "OOH Simple (Print)", "status": "Active", "mediaTypes": ["OOH - Print"]}, {"name": "POS", "status": "Active", "mediaTypes": ["POS - Print", "POS - Digital"]}, {"name": "POS Complex", "status": "Active", "mediaTypes": ["POS - Print"]}, {"name": "POS Merchandising Complex (up to 10)", "status": "Active", "mediaTypes": ["Packaging - Print"]}, {"name": "POS Merchandising Simple (up to 5)", "status": "Active", "mediaTypes": ["Packaging - Print"]}, {"name": "POS Simple", "status": "Active", "mediaTypes": ["POS - Print"]}, {"name": "Packaging", "status": "Active", "mediaTypes": ["Packaging - Print"]}, {"name": "Packaging Box", "status": "Active", "mediaTypes": ["Packaging - Print"]}, {"name": "Paid Media", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Photography Shooting (10-20)", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Photography Shooting (20-40)", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Photography Shooting (up to 10)", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Photography Shooting Still Life", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Photoshoot", "status": "Active", "mediaTypes": ["Literature", "Photography"]}, {"name": "Presentations", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Presentations Template", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Print Design", "status": "Active", "mediaTypes": ["Literature", "Catalogue", "Press - Magazine", "Press - Newspaper", "POS - Print", "OOH - Print", "Direct mail - Print"]}, {"name": "Production", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Production (Post)", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Production (Pre)", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Programmatic", "status": "Active", "mediaTypes": ["Online advertising - Rich media"]}, {"name": "Project Management", "status": "Active", "mediaTypes": ["Management"]}, {"name": "Retouching", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Retouching Complex", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Retouching Simple", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "SEM", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "SEO", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Scoping", "status": "Active", "mediaTypes": ["Management"]}, {"name": "Seedtag Banner Adaptation", "status": "Active", "mediaTypes": ["Online advertising - Rich media"]}, {"name": "Sell Sheet", "status": "Active", "mediaTypes": ["Literature", "Catalogue", "Direct mail - Print"]}, {"name": "Signage", "status": "Active", "mediaTypes": ["POS - Print"]}, {"name": "Single Website Page Design", "status": "Active", "mediaTypes": ["Online advertising - Landing page"]}, {"name": "Skin Adaptation", "status": "Active", "mediaTypes": ["Online advertising - Rich media"]}, {"name": "Social (Animation)", "status": "Active", "mediaTypes": ["Social - Gif"]}, {"name": "Social (Static)", "status": "Active", "mediaTypes": ["Social - Static Image"]}, {"name": "Social (Video)", "status": "Active", "mediaTypes": ["Social - Video"]}, {"name": "Social Carousel (up to 5 images)", "status": "Active", "mediaTypes": ["Social - Static Image"]}, {"name": "Social Reporting", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Social Twitter Thread", "status": "Active", "mediaTypes": ["Social - Static Image"]}, {"name": "Sound", "status": "Active", "mediaTypes": ["Broadcast - Radio"]}, {"name": "Sound Editing", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Storyboarding", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Strategy", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Subtitling", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "TVC", "status": "Active", "mediaTypes": ["Broadcast - TV"]}, {"name": "Transcreation", "status": "Active", "mediaTypes": ["Transcreation"]}, {"name": "Typography", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Video (Edit)", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video (Shoot)", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 10s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 15s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 20s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 30s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 5s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Adaptation 60s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing 15s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing 1m", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing 20s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing 45s", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing Event", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Editing Stock Images", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Video Recording", "status": "Active", "mediaTypes": ["Online advertising - Video"]}, {"name": "Virtual Reality", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Voice Over", "status": "Active", "mediaTypes": ["Broadcast - Radio"]}, {"name": "Web", "status": "Active", "mediaTypes": ["Online advertising - Landing page"]}, {"name": "Web Analytics", "status": "Active", "mediaTypes": ["Literature"]}, {"name": "Web UI & UX", "status": "Active", "mediaTypes": ["Online advertising - .com"]}, {"name": "Website Design", "status": "Active", "mediaTypes": ["Online advertising - .com"]}, ] async def _load_dropdowns(client_id: str = None) -> list: """ Load categories from DB. If client_id is given, tries per-client rows first, falls back to global. """ pool = get_pool() async with pool.acquire() as conn: if client_id: rows = await conn.fetch( 'SELECT name, status, media_types FROM dropdown_categories WHERE client_id = $1 ORDER BY name', client_id ) if rows: return [_row_to_cat(r) for r in rows] # Global (client_id IS NULL) rows = await conn.fetch( 'SELECT name, status, media_types FROM dropdown_categories WHERE client_id IS NULL ORDER BY name' ) return [_row_to_cat(r) for r in rows] async def save_dropdowns(categories: list, client_id: str = None): """Replace all categories for the given scope (global if client_id is None).""" pool = get_pool() async with pool.acquire() as conn: async with conn.transaction(): if client_id: await conn.execute( 'DELETE FROM dropdown_categories WHERE client_id = $1', client_id ) else: await conn.execute( 'DELETE FROM dropdown_categories WHERE client_id IS NULL' ) for cat in categories: await conn.execute(''' INSERT INTO dropdown_categories (client_id, name, status, media_types) VALUES ($1, $2, $3, $4) ''', client_id, cat['name'], cat.get('status', 'Active'), cat.get('mediaTypes', [])) def _row_to_cat(row) -> dict: return { 'name': row['name'], 'status': row['status'], 'mediaTypes': row['media_types'] if row['media_types'] else [], } # ── Sync helpers (file parsing — no DB involved) ──────────────────────────── def detect_excel_mapping(file_bytes: bytes) -> dict: """ Read the first row of an .xlsx and auto-detect column mapping. Returns: {headers, mapping: {name_col, status_col, media_col}, sample} """ import openpyxl from io import BytesIO wb = openpyxl.load_workbook(BytesIO(file_bytes)) ws = wb.active header_row = [str(c.value or '').strip() for c in next(ws.iter_rows(min_row=1, max_row=1))] def _find(keywords, headers): for i, h in enumerate(headers): hl = h.lower() if any(k in hl for k in keywords): return i return None name_col = _find(['category', 'name', 'task', 'deliverable'], header_row) or 0 status_col = _find(['status', 'active', 'archived'], header_row) or 4 media_col = _find(['media', 'type', 'channel'], header_row) or 6 mapping = {'name_col': name_col, 'status_col': status_col, 'media_col': media_col} sample = [] for row in ws.iter_rows(min_row=2, max_row=6, values_only=True): if len(row) <= name_col or not row[name_col]: continue name = str(row[name_col]).strip() status_raw = str(row[status_col]).strip() if len(row) > status_col and row[status_col] else 'Active' status = 'Active' if 'active' in status_raw.lower() else 'Archived' media_raw = str(row[media_col]).strip() if len(row) > media_col and row[media_col] else '' media_types = [m.strip() for m in media_raw.split(',') if m.strip()] if media_raw else [] sample.append({'name': name, 'status': status, 'mediaTypes': media_types}) return {'headers': header_row, 'mapping': mapping, 'sample': sample} def parse_excel_dropdowns(file_bytes: bytes, mapping: dict = None) -> list: """Parse an .xlsx into [{name, status, mediaTypes}].""" import openpyxl from io import BytesIO wb = openpyxl.load_workbook(BytesIO(file_bytes)) ws = wb.active name_col = mapping['name_col'] if mapping else 0 status_col = mapping['status_col'] if mapping else 4 media_col = mapping['media_col'] if mapping else 6 categories = [] for row in ws.iter_rows(min_row=2, values_only=True): if len(row) <= name_col or not row[name_col]: continue name = str(row[name_col]).strip() status_raw = str(row[status_col]).strip() if len(row) > status_col and row[status_col] else 'Active' status = 'Active' if 'active' in status_raw.lower() else 'Archived' media_raw = str(row[media_col]).strip() if len(row) > media_col and row[media_col] else '' media_types = [m.strip() for m in media_raw.split(',') if m.strip()] if media_raw else [] categories.append({'name': name, 'status': status, 'mediaTypes': media_types}) return categories # ── Routes ─────────────────────────────────────────────────────────────────── @dropdowns_bp.route('/categories', methods=['GET']) async def get_categories(): client_id = request.args.get('client_id') or None categories = await _load_dropdowns(client_id) active_only = request.args.get('active', 'true').lower() == 'true' if active_only: categories = [c for c in categories if c.get('status') == 'Active'] return jsonify({'categories': categories}) @dropdowns_bp.route('/all', methods=['GET']) async def get_all(): """Full dropdown data including archived, for admin preview.""" client_id = request.args.get('client_id') or None return jsonify({'categories': await _load_dropdowns(client_id)})