# -*- coding: utf-8 -*-
"""
Export raspunsuri din SQLite in CSV si Excel, per CHESTIONAR.

Rulare:  python export.py
         -> creeaza export/<chestionar>/raspunsuri.csv si .xlsx pentru fiecare
"""
import csv
import io
import os

import db
import forms


def _matrix(form):
    cols = db.columns(form)                 # [(slug, eticheta), ...]
    headers = [label for _, label in cols]
    keys = [slug for slug, _ in cols]
    rows = [[r.get(k, "") for k in keys] for r in db.fetch_all(form)]
    return headers, rows


def to_csv_bytes(form, delimiter=";", bom=True):
    """CSV ca bytes. ';'+BOM = Excel RO; ','+fara BOM = pd.read_csv direct."""
    headers, rows = _matrix(form)
    buf = io.StringIO()
    w = csv.writer(buf, delimiter=delimiter)
    w.writerow(headers)
    w.writerows(rows)
    text = ("" if bom else "") + buf.getvalue()
    return text.encode("utf-8")


def to_xlsx_bytes(form):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment

    headers, rows = _matrix(form)
    wb = Workbook()
    ws = wb.active
    ws.title = "Raspunsuri"

    head_fill = PatternFill("solid", fgColor="1E1E1E")
    head_font = Font(color="F14C4C", bold=True)
    for c, h in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=c, value=h)
        cell.fill = head_fill
        cell.font = head_font
        cell.alignment = Alignment(vertical="top", wrap_text=True)
    for r, row in enumerate(rows, start=2):
        for c, val in enumerate(row, start=1):
            ws.cell(row=r, column=c, value=val)
    for c, h in enumerate(headers, start=1):
        letter = ws.cell(row=1, column=c).column_letter
        ws.column_dimensions[letter].width = min(max(12, len(h) // 2 + 8), 45)
    ws.freeze_panes = "A2"

    out = io.BytesIO()
    wb.save(out)
    return out.getvalue()


def main():
    for form in forms.all_forms():
        d = os.path.join("export", form["id"])
        os.makedirs(d, exist_ok=True)
        with open(os.path.join(d, "raspunsuri.csv"), "wb") as f:
            f.write(to_csv_bytes(form))
        with open(os.path.join(d, "raspunsuri.xlsx"), "wb") as f:
            f.write(to_xlsx_bytes(form))
        print("%-12s -> %s/  (%d raspunsuri)" % (form["id"], d, db.count(form)))


if __name__ == "__main__":
    main()