import io
import base64
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

config = getConfig('.')
DBPools(config.databases)
dbname = get_module_dbname('cms')

async with db.sqlorContext(dbname) as sor:
    rows = await sor.R('cms_content', {'sort': 'sort_order asc, created_at desc'})

    wb = Workbook()
    ws = wb.active
    ws.title = '内容列表'

    # Headers
    headers = ['标题', '类型', '摘要', '状态', '标签', '排序', '创建时间', '发布时间']
    header_font = Font(bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    thin_border = Border(
        left=Side(style='thin'), right=Side(style='thin'),
        top=Side(style='thin'), bottom=Side(style='thin')
    )

    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')
        cell.border = thin_border

    # Mappings
    type_map = {'banner': 'Banner', 'product': '产品', 'case': '案例', 'news': '新闻'}
    status_map = {'draft': '草稿', 'pending': '待审批', 'approved': '已审批', 'published': '已发布', 'archived': '已归档'}

    # Data rows
    for i, row in enumerate(rows, 2):
        ws.cell(row=i, column=1, value=row.get('title', ''))
        ws.cell(row=i, column=2, value=type_map.get(row.get('content_type', ''), row.get('content_type', '')))
        ws.cell(row=i, column=3, value=row.get('summary_text', '')[:200] if row.get('summary_text') else '')
        ws.cell(row=i, column=4, value=status_map.get(row.get('status', ''), row.get('status', '')))
        ws.cell(row=i, column=5, value=row.get('tags', ''))
        ws.cell(row=i, column=6, value=row.get('sort_order', 0))
        ws.cell(row=i, column=7, value=str(row.get('created_at', ''))[:19])
        ws.cell(row=i, column=8, value=str(row.get('published_at', ''))[:19] if row.get('published_at') else '')
        for col in range(1, 9):
            ws.cell(row=i, column=col).border = thin_border

    # Auto-width
    col_widths = [30, 10, 40, 10, 20, 8, 20, 20]
    for i, w in enumerate(col_widths, 1):
        col_letter = chr(64 + i) if i <= 26 else 'A'
        ws.column_dimensions[col_letter].width = w

    # Save to buffer and encode
    buf = io.BytesIO()
    wb.save(buf)
    b64 = base64.b64encode(buf.getvalue()).decode()

    filename = f'cms_content_{curDateString()}.xlsx'
    return {'status': 'ok', 'filename': filename, 'data': b64, 'total': len(rows)}