"""
CSV export — Activation Calendar format.
Supports custom export templates: client > user > global > built-in default.
Template data stored in PostgreSQL export_templates table.
"""

import csv
import io
import logging

from quart import Blueprint, make_response, jsonify, request

from ..auth.middleware import auth_required, get_user_id
from ..sheets.manager import load_sheet_data, get_sheet_client_id
from ..db.pool import get_pool

logger = logging.getLogger(__name__)

export_bp = Blueprint('export', __name__, url_prefix='/api/sheets')

# Internal field names as they appear in sheet row data
INTERNAL_FIELDS = [
    'Number', 'Title', 'Status', 'Category', 'Media', 'Sub-media',
    'Format', 'Supply date', 'Live date', 'Language', 'Country',
    'Quantity', 'Destination', 'End date', 'Special instructions',
]

# Fields cleared on export unless template explicitly maps them
_CLEAR_BY_DEFAULT = {'Number', 'Destination', 'End date', 'Special instructions'}

_DEFAULT_TEMPLATE = [
    {'header': 'Number',               'field': 'Number'},
    {'header': 'Title',                'field': 'Title'},
    {'header': 'Status',               'field': 'Status'},
    {'header': 'Category',             'field': 'Category'},
    {'header': 'Media',                'field': 'Media'},
    {'header': 'Sub media',            'field': 'Sub-media'},
    {'header': 'Destination',          'field': 'Destination'},
    {'header': 'Format',               'field': 'Format'},
    {'header': 'Supply date',          'field': 'Supply date'},
    {'header': 'Live date',            'field': 'Live date'},
    {'header': 'End date',             'field': 'End date'},
    {'header': 'Special instructions', 'field': 'Special instructions'},
    {'header': 'Language',             'field': 'Language'},
    {'header': 'Country',              'field': 'Country'},
    {'header': 'Quantity',             'field': 'Quantity'},
]


async def load_export_template(client_id: str = None, user_id: str = None) -> list:
    """
    Priority: client template → user template → global template → built-in default.
    """
    pool = get_pool()
    async with pool.acquire() as conn:
        if client_id:
            row = await conn.fetchrow(
                'SELECT columns FROM export_templates WHERE scope = $1', f'client:{client_id}'
            )
            if row:
                return row['columns']

        if user_id:
            row = await conn.fetchrow(
                'SELECT columns FROM export_templates WHERE scope = $1', f'user:{user_id}'
            )
            if row:
                return row['columns']

        row = await conn.fetchrow(
            "SELECT columns FROM export_templates WHERE scope = 'global'"
        )
        if row:
            return row['columns']

    return _DEFAULT_TEMPLATE


async def save_export_template(template: list, client_id: str = None, user_id: str = None):
    if client_id:
        scope = f'client:{client_id}'
    elif user_id:
        scope = f'user:{user_id}'
    else:
        scope = 'global'

    pool = get_pool()
    async with pool.acquire() as conn:
        await conn.execute('''
            INSERT INTO export_templates (scope, columns)
            VALUES ($1, $2)
            ON CONFLICT (scope) DO UPDATE SET columns = $2, updated_at = NOW()
        ''', scope, template)


async def delete_export_template(client_id: str = None, user_id: str = None):
    if client_id:
        scope = f'client:{client_id}'
    elif user_id:
        scope = f'user:{user_id}'
    else:
        scope = 'global'

    pool = get_pool()
    async with pool.acquire() as conn:
        await conn.execute('DELETE FROM export_templates WHERE scope = $1', scope)


async def has_export_template(client_id: str = None, user_id: str = None) -> bool:
    if client_id:
        scope = f'client:{client_id}'
    elif user_id:
        scope = f'user:{user_id}'
    else:
        scope = 'global'

    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow('SELECT 1 FROM export_templates WHERE scope = $1', scope)
        return row is not None


def detect_csv_template(file_bytes: bytes) -> dict:
    """Read CSV headers and auto-detect mapping to internal fields."""
    text = file_bytes.decode('utf-8-sig', errors='replace')
    reader = csv.reader(io.StringIO(text))
    headers = [h.strip() for h in (next(reader, [])) if h.strip()]

    def _match(h: str):
        hl = h.lower().replace('-', ' ').replace('_', ' ')
        candidates = {
            'number': 'Number', 'job no': 'Number', 'job number': 'Number',
            'title': 'Title', 'job title': 'Title', 'name': 'Title', 'campaign': 'Title',
            'status': 'Status',
            'category': 'Category', 'task': 'Category', 'deliverable': 'Category',
            'media': 'Media', 'media type': 'Media', 'channel': 'Media',
            'sub media': 'Sub-media', 'sub-media': 'Sub-media', 'submedia': 'Sub-media',
            'format': 'Format', 'size': 'Format', 'spec': 'Format',
            'supply': 'Supply date', 'supply date': 'Supply date', 'artwork': 'Supply date',
            'live': 'Live date', 'live date': 'Live date', 'go live': 'Live date',
            'end': 'End date', 'end date': 'End date', 'expiry': 'End date',
            'language': 'Language', 'lang': 'Language',
            'country': 'Country', 'market': 'Country', 'region': 'Country',
            'quantity': 'Quantity', 'qty': 'Quantity', 'units': 'Quantity',
            'destination': 'Destination',
            'special': 'Special instructions', 'instructions': 'Special instructions', 'notes': 'Special instructions',
        }
        for key, field in candidates.items():
            if key in hl:
                return field
        return None

    template = [{'header': h, 'field': _match(h)} for h in headers]
    return {'headers': headers, 'template': template}


def _build_csv(data: list, template: list) -> str:
    headers = [col['header'] for col in template]
    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=headers, extrasaction='ignore')
    writer.writeheader()
    for row in data:
        csv_row = {}
        for col in template:
            field = col.get('field')
            header = col['header']
            if not field or field in _CLEAR_BY_DEFAULT:
                csv_row[header] = ''
            elif field == 'Quantity':
                csv_row[header] = '1.00'
            else:
                csv_row[header] = row.get(field, '')
        writer.writerow(csv_row)
    return output.getvalue()


# ── Export endpoint ────────────────────────────────────────────────────────────

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

    client_id = await get_sheet_client_id(user_id, sheet_id)
    template = await load_export_template(client_id=client_id, user_id=user_id)
    csv_content = _build_csv(data, template)

    response = await make_response(csv_content)
    response.headers['Content-Type'] = 'text/csv'
    response.headers['Content-Disposition'] = f'attachment; filename="activation_calendar_{sheet_id}.csv"'
    return response


# ── User export template endpoints (any logged-in user) ───────────────────────

user_export_bp = Blueprint('user_export', __name__, url_prefix='/api/export')


@user_export_bp.route('/template', methods=['GET'])
@auth_required
async def get_user_template():
    user_id = get_user_id()
    has_custom = await has_export_template(user_id=user_id)
    template = await load_export_template(user_id=user_id)
    return jsonify({'template': template, 'hasCustom': has_custom, 'fields': INTERNAL_FIELDS})


@user_export_bp.route('/template/detect', methods=['POST'])
@auth_required
async def detect_user_template():
    files = await request.files
    file = files.get('file')
    if not file:
        return jsonify({'error': 'no_file'}), 400
    if not (file.filename or '').lower().endswith('.csv'):
        return jsonify({'error': 'Only .csv files accepted'}), 400
    try:
        result = detect_csv_template(file.read())
        result['fields'] = INTERNAL_FIELDS
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 400


@user_export_bp.route('/template', methods=['POST'])
@auth_required
async def save_user_template():
    user_id = get_user_id()
    body = await request.get_json() or {}
    template = body.get('template')
    if not template or not isinstance(template, list):
        return jsonify({'error': 'invalid_template'}), 400
    await save_export_template(template, user_id=user_id)
    return jsonify({'success': True, 'columns': len(template)})


@user_export_bp.route('/template', methods=['DELETE'])
@auth_required
async def delete_user_template():
    user_id = get_user_id()
    await delete_export_template(user_id=user_id)
    return jsonify({'success': True})