"""
Client management API — PostgreSQL-backed.
"""

import logging
import time
import random
from datetime import datetime, timezone

from quart import Blueprint, jsonify, request

from ..auth.middleware import auth_required, admin_required
from ..db.pool import get_pool

logger = logging.getLogger(__name__)

clients_bp = Blueprint('clients', __name__, url_prefix='/api/clients')


async def load_clients() -> list:
    pool = get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch('SELECT * FROM clients ORDER BY name')
        return [_row_to_dict(r) for r in rows]


async def get_client_by_id(client_id: str) -> dict | None:
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow('SELECT * FROM clients WHERE id = $1', client_id)
        return _row_to_dict(row) if row else None


async def set_client_custom_dropdowns(client_id: str, value: bool):
    pool = get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            'UPDATE clients SET has_custom_dropdowns = $2 WHERE id = $1',
            client_id, value
        )


def _row_to_dict(row) -> dict:
    return {
        'id': row['id'],
        'name': row['name'],
        'hasCustomDropdowns': row['has_custom_dropdowns'],
        'created': row['created_at'].isoformat() if row['created_at'] else None,
    }


@clients_bp.route('', methods=['GET'])
@auth_required
async def list_clients():
    return jsonify({'clients': await load_clients()})


@clients_bp.route('', methods=['POST'])
@admin_required
async def create_client():
    body = await request.get_json() or {}
    name = body.get('name', '').strip()
    if not name:
        return jsonify({'error': 'name_required', 'message': 'Client name is required'}), 400

    client_id = f"client_{int(time.time())}{random.randint(100, 999)}"
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow('''
            INSERT INTO clients (id, name, has_custom_dropdowns)
            VALUES ($1, $2, FALSE)
            RETURNING *
        ''', client_id, name)
    return jsonify({'client': _row_to_dict(row)}), 201


@clients_bp.route('/<client_id>', methods=['DELETE'])
@admin_required
async def delete_client(client_id: str):
    pool = get_pool()
    async with pool.acquire() as conn:
        # Cascades to dropdown_categories via FK; export templates by scope
        await conn.execute('DELETE FROM clients WHERE id = $1', client_id)
        await conn.execute("DELETE FROM export_templates WHERE scope = $1", f'client:{client_id}')
    return jsonify({'success': True})


@clients_bp.route('/<client_id>', methods=['PATCH'])
@admin_required
async def update_client(client_id: str):
    body = await request.get_json() or {}
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow('SELECT * FROM clients WHERE id = $1', client_id)
        if not row:
            return jsonify({'error': 'not_found'}), 404
        name = body.get('name', row['name']).strip() or row['name']
        row = await conn.fetchrow(
            'UPDATE clients SET name = $2 WHERE id = $1 RETURNING *', client_id, name
        )
    return jsonify({'client': _row_to_dict(row)})