"""
Sheet CRUD API — PostgreSQL-backed.
All routes scoped to the authenticated user.
"""

import logging
from quart import Blueprint, jsonify, request

from ..auth.middleware import auth_required, get_user_id
from ..sheets.manager import (
    get_user_sheets, create_sheet, load_sheet_data,
    update_sheet, delete_sheet, rename_sheet, duplicate_sheet,
    generate_next_id, set_sheet_client_id,
)

logger = logging.getLogger(__name__)

sheets_bp = Blueprint('sheets', __name__, url_prefix='/api/sheets')


@sheets_bp.route('', methods=['GET'])
@auth_required
async def list_sheets():
    user_id = get_user_id()
    sheets = await get_user_sheets(user_id)
    return jsonify({'sheets': sheets})


@sheets_bp.route('', methods=['POST'])
@auth_required
async def create_new_sheet():
    user_id = get_user_id()
    body = await request.get_json() or {}
    name = body.get('name', '')
    data = body.get('data', [])
    client_id = body.get('client_id', '')
    sheet = await create_sheet(user_id, name, data, client_id)
    return jsonify({'sheet': sheet}), 201


@sheets_bp.route('/<sheet_id>/client', methods=['PATCH'])
@auth_required
async def update_sheet_client(sheet_id: str):
    user_id = get_user_id()
    body = await request.get_json() or {}
    client_id = body.get('client_id', '')
    await set_sheet_client_id(user_id, sheet_id, client_id)
    return jsonify({'success': True})


@sheets_bp.route('/<sheet_id>', methods=['GET'])
@auth_required
async def get_sheet(sheet_id: str):
    user_id = get_user_id()
    data = await load_sheet_data(user_id, sheet_id)
    if data is None:
        return jsonify({'error': 'not_found'}), 404
    return jsonify({'data': data})


@sheets_bp.route('/<sheet_id>', methods=['PUT'])
@auth_required
async def update_sheet_data(sheet_id: str):
    user_id = get_user_id()
    body = await request.get_json() or {}
    data = body.get('data', [])
    await update_sheet(user_id, sheet_id, data)
    return jsonify({'success': True})


@sheets_bp.route('/<sheet_id>', methods=['DELETE'])
@auth_required
async def delete_sheet_route(sheet_id: str):
    user_id = get_user_id()
    await delete_sheet(user_id, sheet_id)
    return jsonify({'success': True})


@sheets_bp.route('/<sheet_id>', methods=['PATCH'])
@auth_required
async def rename_sheet_route(sheet_id: str):
    user_id = get_user_id()
    body = await request.get_json() or {}
    name = body.get('name', '')
    success = await rename_sheet(user_id, sheet_id, name)
    if not success:
        return jsonify({'error': 'not_found'}), 404
    return jsonify({'success': True})


@sheets_bp.route('/<sheet_id>/duplicate', methods=['POST'])
@auth_required
async def duplicate_sheet_route(sheet_id: str):
    user_id = get_user_id()
    sheet = await duplicate_sheet(user_id, sheet_id)
    if sheet is None:
        return jsonify({'error': 'not_found'}), 404
    return jsonify({'sheet': sheet}), 201


@sheets_bp.route('/<sheet_id>/import', methods=['POST'])
@auth_required
async def import_deliverables(sheet_id: str):
    user_id = get_user_id()
    body = await request.get_json() or {}
    incoming = body.get('deliverables', [])
    mode = body.get('mode', 'append')

    existing = await load_sheet_data(user_id, sheet_id)
    if existing is None:
        return jsonify({'error': 'not_found'}), 404

    base = [] if mode == 'replace' else list(existing)

    for row in incoming:
        row['Number'] = generate_next_id(base)
        row.setdefault('Status', 'Booked')
        row.setdefault('Quantity', 1)
        for k in list(row.keys()):
            if k.startswith('_'):
                del row[k]
        base.append(row)

    await update_sheet(user_id, sheet_id, base)
    return jsonify({'success': True, 'imported': len(incoming), 'total': len(base)})