from api.resources import AuthenticatedResource

from tracker import db
from tracker.bangers.defined_tabs import DEFINED_FRUNCHES


class PublishedSpreadsheets(AuthenticatedResource):
    def get(self):
        rows = db.Session.execute(
            """
        select
          sheet_id,
          tab_id,
          created_at,
          array_length(regexp_split_to_array(id_list::text, '\,'), 1)
        from banger_charts
        where is_published
        order by created_at desc
        """
        ).fetchall()

        pivot = []
        pivot_data = {}
        tabIdsToName = {t.id: t.prefix for t in DEFINED_FRUNCHES}
        for sheet_id, tab_id, created_at, num_rows in rows:
            day = created_at.date()
            row = pivot_data.setdefault(
                sheet_id,
                {
                    "sheetId": sheet_id,
                    "sheetUrl": f"https://docs.google.com/spreadsheets/d/{sheet_id}",
                    "createdDay": day.isoformat(),
                    "tabs": [],
                },
            )
            tabName = tabIdsToName.get(tab_id) or tab_id
            row["tabs"].append(
                {
                    "tabId": tab_id,
                    "tabName": tabName,
                    "numRows": num_rows - 1,
                }
            )
            if len(row['tabs']) == 1:
                row['sheetName'] = tabName
            else:
                row['sheetName'] = "Bangers"
        return {"results": [r[1] for r in sorted(pivot_data.items())]}