package vn.kienutt.training;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.time.*;
import java.util.*;

public final class TrainingDb extends SQLiteOpenHelper {
  private final Context context;

  public TrainingDb(Context c) {
    super(c, "training.db", null, 1);
    context = c.getApplicationContext();
  }

  @Override
  public void onConfigure(SQLiteDatabase db) {
    db.setForeignKeyConstraintsEnabled(true);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    try (InputStream in = context.getAssets().open("schema.sql")) {
      String sql = new String(read(in), StandardCharsets.UTF_8);
      for (String s : sql.split(";")) if (!s.trim().isEmpty()) db.execSQL(s);
    } catch (IOException e) {
      throw new IllegalStateException("Không đọc được schema", e);
    }
  }

  private static byte[] read(InputStream in) throws IOException {
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    byte[] b = new byte[4096];
    int n;
    while ((n = in.read(b)) != -1) out.write(b, 0, n);
    return out.toByteArray();
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    throw new IllegalStateException("Cần viết migration giữ dữ liệu trước khi tăng phiên bản");
  }

  public static final class Row {
    public final long id;
    public final String[] values;
    public final String label;

    Row(long id, String[] values, String label) {
      this.id = id;
      this.values = values;
      this.label = label;
    }
  }

  private long count(SQLiteDatabase db, String sql, String... args) {
    try (Cursor c = db.rawQuery(sql, args)) {
      c.moveToFirst();
      return c.getLong(0);
    }
  }

  private long id(SQLiteDatabase db, String table, String code) {
    try (Cursor c = db.rawQuery("SELECT id FROM " + table + " WHERE code=?", new String[] {code})) {
      if (!c.moveToFirst()) throw new IllegalArgumentException("Không tìm thấy mã " + code);
      return c.getLong(0);
    }
  }

  private String text(String s) {
    s = s.trim();
    if (s.isEmpty() || s.length() > 100)
      throw new IllegalArgumentException("Không để trống, tối đa 100 ký tự");
    return s;
  }

  private int number(String s, int min, int max) {
    int n = Integer.parseInt(s);
    if (n < min || n > max) throw new IllegalArgumentException("Số phải từ " + min + " đến " + max);
    return n;
  }

  private void put(SQLiteDatabase db, String table, long id, ContentValues v) {
    if (id == 0) db.insertOrThrow(table, null, v);
    else if (db.update(table, v, "id=?", new String[] {"" + id}) != 1)
      throw new IllegalArgumentException("Bản ghi không tồn tại");
  }

  public void save(int mode, long selected, String[] raw) {
    String[] a = raw.clone();
    for (int i = 0; i < a.length; i++) a[i] = a[i].trim();
    SQLiteDatabase db = getWritableDatabase();
    db.beginTransaction();
    try {
      ContentValues v = new ContentValues();
      if (mode == 0) {
        v.put("code", text(a[0]));
        v.put("name", text(a[1]));
        put(db, "students", selected, v);
      } else if (mode == 1) {
        v.put("code", text(a[0]));
        v.put("name", text(a[1]));
        v.put("credits", number(a[2], 1, 6));
        put(db, "courses", selected, v);
      } else if (mode == 2) {
        long course = id(db, "courses", a[1]);
        int cap = number(a[2], 1, 200),
            weekday = number(a[3], 1, 7),
            start = number(a[4], 0, 1439),
            end = number(a[5], 1, 1440);
        if (end <= start) throw new IllegalArgumentException("Giờ kết thúc phải sau giờ bắt đầu");
        String teacher = text(a[6]), room = text(a[7]);
        if (count(db, "SELECT COUNT(*) FROM enrollments WHERE section_id=?", "" + selected) > cap)
          throw new IllegalArgumentException("Sức chứa nhỏ hơn số đã đăng ký");
        if (count(
                db,
                TrainingSql.RESOURCE_CONFLICT,
                "" + selected,
                "" + weekday,
                "" + end,
                "" + start,
                teacher,
                room)
            > 0) throw new IllegalArgumentException("Trùng lịch giảng viên hoặc phòng");
        try (Cursor c =
            db.rawQuery(
                "SELECT student_id FROM enrollments WHERE section_id=?",
                new String[] {"" + selected})) {
          while (c.moveToNext()) {
            String student = c.getString(0);
            if (count(
                    db,
                    TrainingSql.STUDENT_CONFLICT,
                    student,
                    "" + selected,
                    "" + weekday,
                    "" + end,
                    "" + start)
                > 0)
              throw new IllegalArgumentException("Lịch mới trùng lịch của sinh viên đã đăng ký");
            if (count(
                    db,
                    "SELECT COUNT(*) FROM enrollments e JOIN sections s ON s.id=e.section_id WHERE"
                        + " e.student_id=? AND s.id<>? AND s.course_id=?",
                    student,
                    "" + selected,
                    "" + course)
                > 0)
              throw new IllegalArgumentException("Sinh viên đã học cùng học phần ở lớp khác");
          }
        }
        v.put("code", text(a[0]));
        v.put("course_id", course);
        v.put("capacity", cap);
        v.put("weekday", weekday);
        v.put("start_min", start);
        v.put("end_min", end);
        v.put("teacher", teacher);
        v.put("room", room);
        put(db, "sections", selected, v);
      } else if (mode == 3) {
        if (selected > 0 && db.delete("enrollments", "id=?", new String[] {"" + selected}) != 1)
          throw new IllegalArgumentException("Đăng ký không tồn tại");
        long student = id(db, "students", a[0]), section = id(db, "sections", a[1]);
        long course;
        int cap, weekday, start, end;
        try (Cursor c =
            db.rawQuery(
                "SELECT course_id,capacity,weekday,start_min,end_min FROM sections WHERE id=?",
                new String[] {"" + section})) {
          c.moveToFirst();
          course = c.getLong(0);
          cap = c.getInt(1);
          weekday = c.getInt(2);
          start = c.getInt(3);
          end = c.getInt(4);
        }
        if (count(db, "SELECT COUNT(*) FROM enrollments WHERE section_id=?", "" + section) >= cap)
          throw new IllegalArgumentException("Lớp đã đủ chỗ");
        if (count(db, TrainingSql.SAME_COURSE, "" + student, "" + course) > 0)
          throw new IllegalArgumentException("Đã đăng ký học phần này");
        if (count(
                db,
                TrainingSql.STUDENT_CONFLICT,
                "" + student,
                "" + section,
                "" + weekday,
                "" + end,
                "" + start)
            > 0) throw new IllegalArgumentException("Sinh viên trùng lịch học");
        if (selected > 0) v.put("id", selected);
        v.put("student_id", student);
        v.put("section_id", section);
        if (a[2].isEmpty()) v.putNull("grade_tenths");
        else {
          int grade = new java.math.BigDecimal(a[2]).movePointRight(1).intValueExact();
          if (grade < 0 || grade > 100)
            throw new IllegalArgumentException("Điểm 0 đến 10, tối đa 1 chữ số thập phân");
          v.put("grade_tenths", grade);
        }
        db.insertOrThrow("enrollments", null, v);
      } else throw new IllegalArgumentException("Mục không hợp lệ");
      db.setTransactionSuccessful();
    } finally {
      db.endTransaction();
    }
  }

  public void remove(int mode, long selected) {
    String[] tables = {"students", "courses", "sections", "enrollments"};
    if (mode < 0 || mode > 3) throw new IllegalArgumentException("Mục sai");
    if (getWritableDatabase().delete(tables[mode], "id=?", new String[] {"" + selected}) != 1)
      throw new IllegalArgumentException("Không tìm thấy bản ghi");
  }

  public List<Row> list(int mode) {
    SQLiteDatabase db = getReadableDatabase();
    String[] queries = {
      "SELECT id,code,name FROM students ORDER BY code",
      "SELECT id,code,name,credits FROM courses ORDER BY code",
      "SELECT s.id,s.code,c.code,s.capacity,s.weekday,s.start_min,s.end_min,s.teacher,s.room FROM"
          + " sections s JOIN courses c ON c.id=s.course_id ORDER BY s.code",
      "SELECT e.id,st.code,s.code,CASE WHEN e.grade_tenths IS NULL THEN '' ELSE"
          + " printf('%.1f',e.grade_tenths/10.0) END FROM enrollments e JOIN students st ON"
          + " st.id=e.student_id JOIN sections s ON s.id=e.section_id ORDER BY st.code,s.code"
    };
    List<Row> result = new ArrayList<>();
    try (Cursor c = db.rawQuery(queries[mode], null)) {
      while (c.moveToNext()) {
        String[] values = new String[c.getColumnCount() - 1];
        for (int i = 0; i < values.length; i++) values[i] = c.getString(i + 1);
        String label = String.join(" · ", values);
        if (mode == 0) {
          try (Cursor g = db.rawQuery(TrainingSql.GPA, new String[] {c.getString(0)})) {
            g.moveToFirst();
            label +=
                "\nĐiểm TB hệ 10 (đã có điểm): "
                    + (g.isNull(0)
                        ? "chưa có"
                        : String.format(java.util.Locale.ROOT, "%.2f", g.getDouble(0)));
          }
        }
        result.add(new Row(c.getLong(0), values, label));
      }
    }
    return result;
  }
}