#!/usr/bin/env python3
# Teton Intake - rebuild one of our data files from a Department of War export.
#
# Usage:   python make_csv.py EXPORT.xlsx OUTPUT.csv [DATE_FORMAT]
# Example: python make_csv.py "Export_Peterson Space Force Base.xlsx" peterson.csv
# Needs:   Python 3.8 or later and the package openpyxl (pip install openpyxl).
#
# The script makes three changes and nothing else:
#   1. "Sample Date" is rewritten as YYYY-MM-DD.
#   2. Rows are sorted. Every value is compared as text, character by character.
#   3. A first column "row" numbers the sorted rows, starting at 1.
# No value is changed, no row is removed, empty cells stay empty.
#
# DATE_FORMAT describes how your export writes dates. Ours wrote day.month.year
# (15.6.2022), which is the default "%d.%m.%Y". If your export writes
# month/day/year, pass "%m/%d/%Y".

import csv
import hashlib
import sys
from datetime import datetime

from openpyxl import load_workbook

COLUMNS = [
    "Component ID", "Component", "State", "Installation Name",
    "Installation Type", "Lab Report Id", "Laboratory Sample ID",
    "Sample Date", "Analysis Method", "CAS Number", "Analyte Abbreviation",
    "Final Result", "Limit of Detection", "Final Units",
    "Final Qualification", "Treatment System", "Pre Or Post Treatment",
]
SORT_FIRST = ["Sample Date", "Lab Report Id", "Laboratory Sample ID",
              "Analyte Abbreviation"]
SORT_ORDER = SORT_FIRST + [c for c in COLUMNS if c not in SORT_FIRST]


def main():
    if len(sys.argv) not in (3, 4):
        sys.exit(__doc__ or "usage: make_csv.py EXPORT.xlsx OUTPUT.csv [DATE_FORMAT]")
    source, target = sys.argv[1], sys.argv[2]
    date_format = sys.argv[3] if len(sys.argv) == 4 else "%d.%m.%Y"

    sheet = load_workbook(source, read_only=True).worksheets[0]
    rows = sheet.iter_rows(values_only=True)
    header = list(next(rows))
    if header != COLUMNS:
        sys.exit("Unexpected columns: %r" % (header,))

    records = []
    for number, cells in enumerate(rows, start=2):
        cells = list(cells) + [None] * (len(COLUMNS) - len(cells))
        record = {}
        for name, value in zip(COLUMNS, cells):
            if value is None:
                value = ""
            if not isinstance(value, str):
                sys.exit("Row %d, column %s: expected text, found %r"
                         % (number, name, value))
            record[name] = value
        record["Sample Date"] = datetime.strptime(
            record["Sample Date"], date_format).strftime("%Y-%m-%d")
        records.append(record)

    records.sort(key=lambda r: tuple(r[c] for c in SORT_ORDER))

    with open(target, "w", encoding="utf-8", newline="") as handle:
        writer = csv.writer(handle, lineterminator="\n")
        writer.writerow(["row"] + COLUMNS)
        for number, record in enumerate(records, start=1):
            writer.writerow([number] + [record[c] for c in COLUMNS])

    with open(target, "rb") as handle:
        digest = hashlib.sha256(handle.read()).hexdigest()
    print("%d rows written to %s" % (len(records), target))
    print("SHA-256: %s" % digest)


if __name__ == "__main__":
    main()