module api_modules.classroom_compliance.api_export;

import handy_httpd;
import ddbc;
import streams;
import std.datetime;
import std.conv;
import slf4d;

import api_modules.auth : User, getUserOrThrow;
import db;
import data_utils;

void getFullExport(ref HttpRequestContext ctx) {
    Connection conn = getDb();
    scope(exit) conn.close();
    User user = getUserOrThrow(ctx, conn);

    const query = "
    SELECT
        c.id AS class_id,
        c.number AS class_number,
        c.school_year AS school_year,
        s.id AS student_id,
        s.name AS student_name,
        s.desk_number AS desk_number,
        s.removed AS student_removed,
        e.date AS entry_date,
        e.created_at AS entry_created_at,
        e.absent AS absent,
        e.phone_compliant AS phone_compliant,
        e.behavior_rating AS behavior_rating,
        e.comment AS comment
    FROM classroom_compliance_class c
    LEFT JOIN classroom_compliance_student s
        ON s.class_id = c.id
    LEFT JOIN classroom_compliance_entry e
        ON e.class_id = c.id AND e.student_id = s.id
    WHERE c.user_id = ?
    ";

    ctx.response.addHeader("Content-Type", "text/csv");
    ctx.response.addHeader("Content-Disposition", "attachment; filename=export.csv");
    ctx.response.flushHeaders();

    PreparedStatement stmt = conn.prepareStatement(query);
    scope(exit) stmt.close();
    stmt.setUlong(1, user.id);
    ResultSet rs = stmt.executeQuery();
    scope(exit) rs.close();
    auto s = csvOutputStreamFor(ctx.response.outputStream);

    const CSVColumnDef[] columns = [
        CSVColumnDef("Class ID", (r, i) => r.getUlong(i).to!string),
        CSVColumnDef("Class Number", (r, i) => r.getUint(i).to!string),
        CSVColumnDef("School Year", (r, i) => r.getString(i)),
        CSVColumnDef("Student ID", (r, i) => r.getUlong(i).to!string),
        CSVColumnDef("Student Name", (r, i) => r.getString(i)),
        CSVColumnDef("Desk Number", (r, i) => r.getUint(i).to!string),
        CSVColumnDef("Student Removed", (r, i) => r.getBoolean(i).to!string),
        CSVColumnDef("Entry Date", (r, i) => r.getDate(i).toISOExtString()),
        CSVColumnDef("Entry Created At", (r, i) => formatCreationTimestamp(r.getUlong(i))),
        CSVColumnDef("Absence", (r, i) => r.getBoolean(i) ? "Absent" : "Present"),
        CSVColumnDef("Phone Compliant", &formatPhoneCompliance),
        CSVColumnDef("Behavior Rating", &formatBehaviorRating),
        CSVColumnDef("Comment", &formatComment)
    ];
    // Write headers first.
    for (size_t i = 0; i < columns.length; i++) {
        s.writeCell(columns[i].header);
        if (i + 1 < columns.length) s.writeComma();
    }
    s.writeLine();

    foreach (DataSetReader r; rs) {
        for (size_t i = 0; i < columns.length; i++) {
            int rsIdx = cast(int) i + 1;
            s.writeCell(columns[i].mapper(r, rsIdx));
            if (i + 1 < columns.length) s.writeComma();
        }
        s.writeLine();
    }
}

private string formatCreationTimestamp(ulong unixMillis) {
    ulong seconds = unixMillis / 1000;
    ulong millis = unixMillis % 1000;
    SysTime t = SysTime.fromUnixTime(seconds);
    t = SysTime(DateTime(t.year, t.month, t.day, t.hour, t.minute, t.second), msecs(millis));
    return t.toISOExtString();
}

private string formatPhoneCompliance(DataSetReader r, int i) {
    if (r.isNull(i)) return "N/A";
    return r.getBoolean(i) ? "Compliant" : "Non-compliant";
}

private string formatBehaviorRating(DataSetReader r, int i) {
    if (r.isNull(i)) return "N/A";
    ubyte score = r.getUbyte(i);
    if (score == 3) return "Good";
    if (score == 2) return "Mediocre";
    return "Poor";
}

private string formatComment(DataSetReader r, int i) {
    if (r.isNull(i)) return "";
    string c = r.getString(i);
    if (c.length > 0) return c;
    return "";
}

private struct CSVColumnDef {
    const string header;
    string function(DataSetReader, int) mapper;
}

private struct CSVOutputStream(S) if (isByteOutputStream!S) {
    private S stream;

    this(S stream) {
        this.stream = stream;
    }

    StreamResult writeToStream(ubyte[] buffer) {
        return this.stream.writeToStream(buffer);
    }

    void writeElement(string s) {
        StreamResult r = writeToStream(cast(ubyte[]) s);
        if (r.hasError) {
            throw new HttpStatusException(HttpStatus.INTERNAL_SERVER_ERROR, cast(string) r.error.message);
        } else if (r.count != s.length) {
            throw new HttpStatusException(HttpStatus.INTERNAL_SERVER_ERROR, "Couldn't write all data to stream.");
        }
    }

    void writeCell(string s) {
        import std.string;
        import std.regex;
        import std.algorithm : canFind, any;
        const pattern = ctRegex!`\"`;
        if (canFind(s, "\"")) {
            s = replaceAll(s, pattern, "\"\"");
        }
        const escapeableChars = ["\"", ",", ",", "\\", "\n"];
        bool shouldQuote = escapeableChars.any!(c => canFind(s, c));
        if (shouldQuote) {
            s = "\"" ~ s ~ "\"";
        }
        writeElement(s);
    }

    void writeComma() {
        writeElement(",");
    }

    void writeLine() {
        writeElement("\r\n");
    }
}

private CSVOutputStream!(S) csvOutputStreamFor(S)(S stream) if (isByteOutputStream!S) {
    return CSVOutputStream!(S)(stream);
}