module api_modules.classroom_compliance.api_entry;

import handy_httpd;
import handy_httpd.components.optional;
import ddbc;
import std.datetime;
import std.json;
import std.algorithm : map;
import std.array;
import slf4d;

import api_modules.auth;
import api_modules.classroom_compliance.model;
import api_modules.classroom_compliance.util;
import api_modules.classroom_compliance.score;
import db;
import data_utils;

struct EntriesTableEntry {
    ulong id;
    Date date;
    ulong createdAt;
    bool absent;
    string comment;
    string[] checklistItems;
    Optional!bool phoneCompliant;
    Optional!bool classroomReadiness;
    Optional!ubyte behaviorRating;

    JSONValue toJsonObj() const {
        JSONValue obj = JSONValue.emptyObject;
        obj.object["id"] = JSONValue(id);
        obj.object["date"] = JSONValue(date.toISOExtString());
        obj.object["createdAt"] = JSONValue(createdAt);
        obj.object["absent"] = JSONValue(absent);
        obj.object["comment"] = JSONValue(comment);
        obj.object["checklistItems"] = JSONValue.emptyArray;
        foreach (ck; checklistItems) {
            obj.object["checklistItems"].array ~= JSONValue(ck);
        }
        if (absent) {
            obj.object["phoneCompliant"] = JSONValue(null);
            obj.object["classroomReadiness"] = JSONValue(null);
            obj.object["behaviorRating"] = JSONValue(null);
        } else {
            JSONValue pcValue = phoneCompliant.isNull ? JSONValue(null) : JSONValue(phoneCompliant.value);
            JSONValue crValue = classroomReadiness.isNull ? JSONValue(null) : JSONValue(classroomReadiness.value);
            JSONValue bValue = behaviorRating.isNull ? JSONValue(null) : JSONValue(behaviorRating.value);
            obj.object["phoneCompliant"] = pcValue;
            obj.object["classroomReadiness"] = crValue;
            obj.object["behaviorRating"] = bValue;
        }
        return obj;
    }
}

struct EntriesTableStudentResponse {
    ulong id;
    ulong classId;
    string name;
    ushort deskNumber;
    bool removed;
    EntriesTableEntry[string] entries;
    Optional!double score;

    JSONValue toJsonObj() const {
        JSONValue obj = JSONValue.emptyObject;
        obj.object["id"] = JSONValue(id);
        obj.object["classId"] = JSONValue(classId);
        obj.object["name"] = JSONValue(name);
        obj.object["deskNumber"] = JSONValue(deskNumber);
        obj.object["removed"] = JSONValue(removed);
        JSONValue entriesSet = JSONValue.emptyObject;
        foreach (dateStr, entry; entries) {
            entriesSet.object[dateStr] = entry.toJsonObj();
        }
        obj.object["entries"] = entriesSet;
        if (score.isNull) {
            obj.object["score"] = JSONValue(null);
        } else {
            obj.object["score"] = JSONValue(score.value);
        }
        return obj;
    }
}

struct EntriesTableResponse {
    EntriesTableStudentResponse[] students;
    string[] dates;

    JSONValue toJsonObj() const {
        JSONValue obj = JSONValue.emptyObject;
        obj.object["students"] = JSONValue(students.map!(s => s.toJsonObj()).array);
        obj.object["dates"] = JSONValue(dates);
        return obj;
    }
}

/**
 * Main endpoint that supplies data for the app's "entries" table, which shows
 * all data about all students in a class, usually for a selected week. Here,
 * we need to provide a list of students which will be treated as rows by the
 * table, and then for each student, an entry object for each date in the
 * requested date range.
 * Params:
 *   ctx = The request context.
 */
void getEntries(ref HttpRequestContext ctx) {
    Connection conn = getDb();
    scope(exit) conn.close();
    User user = getUserOrThrow(ctx, conn);
    auto cls = getClassOrThrow(ctx, conn, user);
    DateRange dateRange = parseDateRangeParams(ctx);

    // First prepare a list of all students, including ones which don't have any entries.
    ClassroomComplianceStudent[] students = findAll(
        conn,
        "SELECT * FROM classroom_compliance_student WHERE class_id = ? ORDER BY name ASC",
        &ClassroomComplianceStudent.parse,
        cls.id
    );
    EntriesTableStudentResponse[] studentObjects = students.map!(s => EntriesTableStudentResponse(
        s.id,
        s.classId,
        s.name,
        s.deskNumber,
        s.removed,
        null,
        Optional!double.empty
    )).array;

    const entriesQuery = "
    SELECT
        entry.id,
        entry.date,
        entry.created_at,
        entry.absent,
        entry.comment,
        (
            SELECT STRING_AGG(ck.item, '|||')
            FROM classroom_compliance_entry_comment_checklist ck
            WHERE ck.entry_id = entry.id

        ),
        entry.phone_compliant,
        entry.classroom_readiness,
        entry.behavior_rating,
        student.id,
        student.name,
        student.desk_number,
        student.removed,
        student.class_id
    FROM classroom_compliance_entry entry
    LEFT JOIN classroom_compliance_student student
        ON student.id = entry.student_id
    WHERE
        entry.class_id = ?
        AND entry.date >= ?
        AND entry.date <= ?
    ORDER BY
        student.id ASC,
        entry.date ASC
    ";
    PreparedStatement ps = conn.prepareStatement(entriesQuery);
    scope(exit) ps.close();
    ps.setUlong(1, cls.id);
    ps.setDate(2, dateRange.from);
    ps.setDate(3, dateRange.to);
    ResultSet rs = ps.executeQuery();
    scope(exit) rs.close();
    foreach (DataSetReader r; rs) {
        // Parse the basic data from the query.
        const absent = r.getBoolean(4);
        Optional!bool phoneCompliant = absent
            ? Optional!bool.empty
            : Optional!bool.of(r.getBoolean(7));
        if (r.isNull(7)) phoneCompliant.isNull = true;
        Optional!bool classroomReadiness = absent
            ? Optional!bool.empty
            : Optional!bool.of(r.getBoolean(8));
        if (r.isNull(8)) classroomReadiness.isNull = true;
        Optional!ubyte behaviorRating = absent
            ? Optional!ubyte.empty
            : Optional!ubyte.of(r.getUbyte(9));
        if (r.isNull(9)) behaviorRating.isNull = true;
        import std.string : split;
        EntriesTableEntry entryData = EntriesTableEntry(
            r.getUlong(1),
            r.getDate(2),
            r.getUlong(3),
            r.getBoolean(4),
            r.getString(5),
            r.getString(6).split("|||"),
            phoneCompliant,
            classroomReadiness,
            behaviorRating
        );
        ClassroomComplianceStudent student = ClassroomComplianceStudent(
            r.getUlong(10),
            r.getString(11),
            r.getUlong(14),
            r.getUshort(12),
            r.getBoolean(13)
        );
        string dateStr = entryData.date.toISOExtString();

        // Find the student object this entry belongs to, then add it to their list.
        bool studentFound = false;
        foreach (ref studentObj; studentObjects) {
            if (studentObj.id == student.id) {
                studentObj.entries[dateStr] = entryData;
                studentFound = true;
                break;
            }
        }
        if (!studentFound) {
            // The student isn't in our list of original students from the
            // class, so it's a student who has since moved to another class.
            // Their data should still be shown, so add the student here.
            studentObjects ~= EntriesTableStudentResponse(
                student.id,
                student.classId,
                student.name,
                student.deskNumber,
                student.removed,
                [dateStr: entryData],
                Optional!double.empty
            );
        }
    }

    // Find scores for each student for this timeframe.
    Optional!double[ulong] scores = getScores(conn, cls.id, dateRange.to);
    foreach (studentId, score; scores) {
        bool studentFound = false;
        foreach (ref studentObj; studentObjects) {
            if (studentObj.id == studentId) {
                studentObj.score = score;
                studentFound = true;
                break;
            }
        }
        if (!studentFound) {
            throw new Exception("Failed to find student for which a score was calculated.");
        }
    }

    // Prepare the final response to the client:
    EntriesTableResponse response;
    Date d = dateRange.from;
    while (d <= dateRange.to) {
        string dateStr = d.toISOExtString();
        response.dates ~= dateStr;
        d += days(1);
    }
    response.students = studentObjects;
    JSONValue responseObj = response.toJsonObj();
    // Go back and add null to any dates any student is missing an entry for.
    foreach (ref studentObj; responseObj.object["students"].array) {
        foreach (dateStr; response.dates) {
            if (dateStr !in studentObj.object["entries"].object) {
                studentObj.object["entries"].object[dateStr] = JSONValue(null);
            }
        }
    }
    ctx.response.writeBodyString(responseObj.toJSON(), "application/json");
}

/**
 * Endpoint for the user to save changes to any entries they've edited. The
 * user provides a JSON payload containing the updated entries, and we go
 * through and perform updates to the database to match the desired state.
 * Params:
 *   ctx = The request context.
 */
void saveEntries(ref HttpRequestContext ctx) {
    Connection conn = getDb();
    conn.setAutoCommit(false);
    scope(exit) conn.close();
    User user = getUserOrThrow(ctx, conn);
    auto cls = getClassOrThrow(ctx, conn, user);
    if (cls.archived) throw new HttpStatusException(HttpStatus.FORBIDDEN, "Class is archived.");
    JSONValue bodyContent = ctx.request.readBodyAsJson();
    try {
        foreach (JSONValue studentObj; bodyContent.object["students"].array) {
            ulong studentId = studentObj.object["id"].integer();
            JSONValue entries = studentObj.object["entries"];
            foreach (string dateStr, JSONValue entry; entries.object) {
                if (entry.isNull) {
                    deleteEntry(conn, cls.id, studentId, dateStr);
                    continue;
                }

                Optional!ClassroomComplianceEntry existingEntry = findOne(
                    conn,
                    "SELECT id, class_id, student_id, date, created_at,
                    absent, comment,
                    (
                        SELECT STRING_AGG(ck.item, '|||')
                        FROM classroom_compliance_entry_comment_checklist ck
                        WHERE ck.entry_id = id
                    ),
                    phone_compliant, classroom_readiness, behavior_rating
                    FROM classroom_compliance_entry
                    WHERE class_id = ? AND student_id = ? AND date = ?",
                    &ClassroomComplianceEntry.parse,
                    cls.id, studentId, dateStr
                );

                ulong entryId = entry.object["id"].integer();
                bool creatingNewEntry = entryId == 0;

                if (creatingNewEntry) {
                    if (!existingEntry.isNull) {
                        ctx.response.status = HttpStatus.BAD_REQUEST;
                        ctx.response.writeBodyString("Cannot create a new entry when one already exists.");
                        return;
                    }

                    insertNewEntry(conn, cls.id, studentId, dateStr, entry);
                } else {
                    if (existingEntry.isNull) {
                        ctx.response.status = HttpStatus.BAD_REQUEST;
                        ctx.response.writeBodyString("Cannot update an entry which doesn't exist.");
                        return;
                    }
                    updateEntry(conn, cls.id, studentId, dateStr, entryId, entry);
                }
            }
        }
        conn.commit();
    } catch (HttpStatusException e) {
        conn.rollback();
        ctx.response.status = e.status;
        ctx.response.writeBodyString(e.message);
    } catch (JSONException e) {
        conn.rollback();
        ctx.response.status = HttpStatus.BAD_REQUEST;
        ctx.response.writeBodyString("Invalid JSON payload.");
        warn(e);
    } catch (Exception e) {
        conn.rollback();
        ctx.response.status = HttpStatus.INTERNAL_SERVER_ERROR;
        ctx.response.writeBodyString("An internal server error occurred: " ~ e.msg);
        error(e);
    }
}

private void deleteEntry(
    Connection conn,
    ulong classId,
    ulong studentId,
    string dateStr
) {
    update(
        conn,
        "DELETE FROM classroom_compliance_entry WHERE class_id = ? AND student_id = ? AND date = ?",
        classId, studentId, dateStr
    );
    infoF!"Deleted entry for student %s on %s"(studentId, dateStr);
}

private void insertNewEntry(
    Connection conn,
    ulong classId,
    ulong studentId,
    string dateStr,
    JSONValue payload
) {
    bool absent = payload.object["absent"].boolean;
    string comment = payload.object["comment"].str;
    if (comment is null) comment = "";
    string[] checklistItems;
    if ("checklistItems" in payload.object) {
        checklistItems = payload.object["checklistItems"].array
            .map!(v => v.str)
            .array;
    }
    Optional!bool classroomReadiness = Optional!bool.empty;
    Optional!ubyte behaviorRating = Optional!ubyte.empty;
    if (!absent) {
        classroomReadiness = Optional!bool.of(payload.object["classroomReadiness"].boolean);
        behaviorRating = Optional!ubyte.of(cast(ubyte) payload.object["behaviorRating"].integer);
    }
    // Do the main insert first.
    import std.variant;
    Variant newEntryId;
    const query = "
    INSERT INTO classroom_compliance_entry
    (class_id, student_id, date, absent, comment, classroom_readiness, behavior_rating)
    VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING id";
    PreparedStatement ps = conn.prepareStatement(query);
    scope(exit) ps.close();
    ps.setUlong(1, classId);
    ps.setUlong(2, studentId);
    ps.setString(3, dateStr);
    ps.setBoolean(4, absent);
    ps.setString(5, comment);
    if (absent) {
        ps.setNull(6);
        ps.setNull(7);
    } else {
        ps.setBoolean(6, classroomReadiness.value);
        ps.setUbyte(7, behaviorRating.value);
    }
    ps.executeUpdate(newEntryId);
    updateEntryCommentChecklistItems(conn, newEntryId.coerce!ulong, checklistItems);

    infoF!"Created new entry for student %d: %s"(studentId, payload);
}

private void updateEntry(
    Connection conn,
    ulong classId,
    ulong studentId,
    string dateStr,
    ulong entryId,
    JSONValue obj
) {
    bool absent = obj.object["absent"].boolean;
    string comment = obj.object["comment"].str;
    if (comment is null) comment = "";
    string[] checklistItems;
    if ("checklistItems" in obj.object) {
        checklistItems = obj.object["checklistItems"].array
            .map!(v => v.str)
            .array;
    }
    Optional!bool classroomReadiness = Optional!bool.empty;
    Optional!ubyte behaviorRating = Optional!ubyte.empty;
    if (!absent) {
        classroomReadiness = Optional!bool.of(obj.object["classroomReadiness"].boolean);
        behaviorRating = Optional!ubyte.of(cast(ubyte) obj.object["behaviorRating"].integer);
    }
    const query = "
    UPDATE classroom_compliance_entry
    SET absent = ?, comment = ?, classroom_readiness = ?, behavior_rating = ?
    WHERE class_id = ? AND student_id = ? AND date = ? AND id = ?
    ";
    PreparedStatement ps = conn.prepareStatement(query);
    scope(exit) ps.close();
    ps.setBoolean(1, absent);
    ps.setString(2, comment);
    if (absent) {
        ps.setNull(3);
        ps.setNull(4);
    } else {
        ps.setBoolean(3, classroomReadiness.value);
        ps.setUbyte(4, behaviorRating.value);
    }
    ps.setUlong(5, classId);
    ps.setUlong(6, studentId);
    ps.setString(7, dateStr);
    ps.setUlong(8, entryId);
    ps.executeUpdate();
    updateEntryCommentChecklistItems(conn, entryId, checklistItems);

    infoF!"Updated entry %d"(entryId);
}

private void updateEntryCommentChecklistItems(Connection conn, ulong entryId, string[] items) {
    PreparedStatement ps1 = conn.prepareStatement(
        "DELETE FROM classroom_compliance_entry_comment_checklist WHERE entry_id = ?"
    );
    scope(exit) ps1.close();
    ps1.setUlong(1, entryId);
    ps1.executeUpdate();
    const checklistItemsQuery = "
    INSERT INTO classroom_compliance_entry_comment_checklist (entry_id, item)
    VALUES (?, ?)
    ";
    PreparedStatement ps2 = conn.prepareStatement(checklistItemsQuery);
    scope(exit) ps2.close();
    foreach (item; items) {
        ps2.setUlong(1, entryId);
        ps2.setString(2, item);
        ps2.executeUpdate();
    }
}
