package controllers

import (
	m "skripsi/skripsiBackend/models"

	"database/sql"

	"github.com/go-sql-driver/mysql"

	"github.com/revel/revel"
)

type Soal struct {
	*revel.Controller
}

// only for TESTING
func (c Soal) GetSoalById(id int) revel.Result {
	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))

	if err != nil {
		// revel.ERROR.Println("Koneksi database gagal:", err)
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	rows, err := db.Query("SELECT * FROM soal WHERE id=?", id)

	if err != nil {

		return c.RenderText("Query gagal")
	}
	defer rows.Close()

	soals := []m.Soal{}
	for rows.Next() {
		soal := m.Soal{}
		pilihanjawabans := []m.PilihanJawaban{}
		if err := rows.Scan(&soal.ID, &soal.Soal, &soal.ID_Materi, &soal.Kesulitan, &soal.ID_jenis_soal, &soal.Images); err != nil {
			// revel.ERROR.Println("Baca data gagal:", err)
			return c.RenderText("Baca data gagal")
		}
		pilihanRows, err := db.Query("SELECT * FROM pilihan_jawaban WHERE ID_soal = ?", soal.ID)
		if err != nil {
			return c.RenderText("Query gagal")
		}
		defer pilihanRows.Close()

		for pilihanRows.Next() {
			pilihan := m.PilihanJawaban{}
			if err := pilihanRows.Scan(&pilihan.ID, &pilihan.ID_soal, &pilihan.Pilihan, &pilihan.IsTrue, &pilihan.IgnoreCase); err != nil {
				return c.RenderText("Baca data pilihan jawaban gagal")
			}
			pilihanjawabans = append(pilihanjawabans, pilihan)
		}
		soal.PilihanJawaban = pilihanjawabans
		soals = append(soals, soal)
	}

	return c.RenderJSON(soals)
}

func (c Soal) Index(ID_materi int, limit int) revel.Result {
	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))

	if err != nil {
		// revel.ERROR.Println("Koneksi database gagal:", err)
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	var rows *sql.Rows

	if ID_materi > 0 {
		rows, err = db.Query("SELECT * FROM soal LEFT JOIN materi ON materi.ID = soal.ID_materi  WHERE ID_materi = ? ORDER BY RAND() LIMIT ? ", ID_materi, limit)

	} else if limit > 0 {
		rows, err = db.Query("SELECT * FROM soal  LEFT JOIN materi ON materi.ID = soal.ID_materi ORDER BY RAND()  LIMIT ?", limit)

	} else {
		rows, err = db.Query("SELECT * FROM soal LEFT JOIN materi ON materi.ID = soal.ID_materi ORDER BY RAND() ")
	}
	if err != nil {

		return c.RenderText("Query gagal")
	}
	defer rows.Close()

	soals := []m.Soal{}
	for rows.Next() {
		soal := m.Soal{}

		pilihanjawabans := []m.PilihanJawaban{}
		if err := rows.Scan(&soal.ID, &soal.Soal, &soal.ID_Materi, &soal.Kesulitan, &soal.ID_jenis_soal, &soal.Images, &soal.Materi.ID, &soal.Materi.Nama, &soal.Materi.Deskripsi, &soal.Materi.Kode_MK); err != nil {
			return c.RenderText("Baca data soal gagal")
		}

		pilihanRows, err := db.Query("SELECT * FROM pilihan_jawaban WHERE ID_soal = ?", soal.ID)
		if err != nil {
			return c.RenderText("Query gagal")
		}
		defer pilihanRows.Close()

		for pilihanRows.Next() {
			pilihan := m.PilihanJawaban{}
			if err := pilihanRows.Scan(&pilihan.ID, &pilihan.ID_soal, &pilihan.Pilihan, &pilihan.IsTrue, &pilihan.IgnoreCase); err != nil {
				return c.RenderText("Baca data pilihan jawaban gagal")
			}
			pilihanjawabans = append(pilihanjawabans, pilihan)
		}
		soal.PilihanJawaban = pilihanjawabans

		soals = append(soals, soal)
	}

	return c.RenderJSON(soals)
}

func (c Soal) GetAll(ID_materi, limit int) revel.Result {
	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))
	if err != nil {
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	// Memastikan batas limit tidak melebihi 0
	if limit <= 0 {
		return c.RenderText("Batas limit harus lebih dari 0")
	}

	mediumExistQuery := "SELECT COUNT(*) FROM soal WHERE Kesulitan = 2 AND ID_materi = ?"
	hardExistQuery := "SELECT COUNT(*) FROM soal WHERE Kesulitan = 3 AND ID_materi = ?"

	var mediumCount, hardCount int
	err = db.QueryRow(mediumExistQuery, ID_materi).Scan(&mediumCount)
	if err != nil {
		return c.RenderText("Pengecekan soal medium gagal")
	}

	err = db.QueryRow(hardExistQuery, ID_materi).Scan(&hardCount)
	if err != nil {
		return c.RenderText("Pengecekan soal hard gagal")
	}

	mediumLimit := limit - 2
	hardLimit := limit - 4
	if mediumCount < mediumLimit {
		mediumLimit = mediumCount
	}
	if hardCount < hardLimit {
		hardLimit = hardCount
	}

	query := `
        SELECT * FROM (
            SELECT *, 1 AS level FROM soal WHERE Kesulitan = 1 AND ID_materi = ? LIMIT ?
            UNION ALL
            SELECT *, 2 AS level FROM soal WHERE Kesulitan = 2 AND ID_materi = ? LIMIT ?
            UNION ALL
            SELECT *, 3 AS level FROM soal WHERE Kesulitan = 3 AND ID_materi = ? LIMIT ?
        ) AS all_soals
        ORDER BY RAND()
    `

	rows, err := db.Query(query, ID_materi, limit, ID_materi, mediumLimit, ID_materi, hardLimit)
	if err != nil {
		return c.RenderText("Query gagal")
	}
	defer rows.Close()

	// Memproses hasil query
	soals := []m.Soal{}
	for rows.Next() {
		soal := m.Soal{}
		pilihanjawabans := []m.PilihanJawaban{}

		if err := rows.Scan(&soal.ID, &soal.Soal, &soal.ID_Materi, &soal.Kesulitan, &soal.ID_jenis_soal, &soal.Images, &soal.Materi.ID, &soal.Materi.Nama, &soal.Materi.Deskripsi, &soal.Materi.Kode_MK); err != nil {
			return c.RenderText("Baca data soal gagal")
		}

		pilihanRows, err := db.Query("SELECT * FROM pilihan_jawaban WHERE ID_soal = ?", soal.ID)
		if err != nil {
			return c.RenderText("Query gagal")
		}
		defer pilihanRows.Close()

		// Memproses pilihan jawaban
		for pilihanRows.Next() {
			pilihan := m.PilihanJawaban{}
			if err := pilihanRows.Scan(&pilihan.ID, &pilihan.ID_soal, &pilihan.Pilihan, &pilihan.IsTrue, &pilihan.IgnoreCase); err != nil {
				return c.RenderText("Baca data pilihan jawaban gagal")
			}
			pilihanjawabans = append(pilihanjawabans, pilihan)
		}

		soal.PilihanJawaban = pilihanjawabans
		soals = append(soals, soal)
	}

	return c.RenderJSON(soals)
}

func (c Soal) Count(ID_materi int) revel.Result {
	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))
	if err != nil {
		// revel.ERROR.Println("Koneksi database gagal:", err)
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()
	var count int
	var query string

	if ID_materi <= 0 {
		query = "SELECT COUNT(*) FROM soal"

		err = db.QueryRow(query).Scan(&count)
	} else {
		query = "SELECT COUNT(*) FROM soal WHERE ID_materi=?"
		err = db.QueryRow(query, ID_materi).Scan(&count)
	}

	if err != nil {
		return c.RenderText("Query gagal")
	}

	return c.RenderJSON(count)
}

func (c Soal) Create(soal m.Soal) revel.Result {
	v := c.Validation
	soal.Validate(v)
	if v.HasErrors() {
		return c.RenderText("Validasi gagal")
	}

	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))
	if err != nil {
		// revel.ERROR.Println("Koneksi database gagal:", err)
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	_, err = db.Exec("INSERT INTO soal (soal, ID_materi, kesulitan, ID_jenis_soal, images) VALUES ( ?, ?, ?, ?, ?)", soal.Soal, soal.ID_Materi, soal.Kesulitan, soal.ID_jenis_soal, soal.Images)
	if err != nil {
		if mysqlError, ok := err.(*mysql.MySQLError); ok {
			if mysqlError.Number == 1062 {
				return c.RenderText("ID sudah ada")
			}
		}
		//  revel.ERROR.Println("Insert data gagal:", err)
		return c.RenderText("Insert data gagal")
	}
	var soalID int64
	err = db.QueryRow("SELECT LAST_INSERT_ID()").Scan(&soalID)

	if err != nil {
		return c.RenderText("Gagal mendapatkan ID terakhir")
	}

	pilihan := soal.PilihanJawaban

	for _, p := range pilihan {
		_, err = db.Exec("INSERT INTO pilihan_jawaban (ID_soal, pilihan, isTrue) VALUES (?, ?, ?)", soalID, p.Pilihan, p.IsTrue)
		if err != nil {
			return c.RenderText("Insert pilihan gagal")
		}

	}

	return c.RenderText("Data soal berhasil ditambahkan")
}

func (c Soal) Update(id int, soal m.Soal) revel.Result {
	v := c.Validation
	soal.Validate(v)
	if v.HasErrors() {
		return c.RenderText("Validasi gagal")
	}

	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))
	if err != nil {
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	_, err = db.Exec("UPDATE soal SET soal=?, ID_materi=?, kesulitan=?, ID_jenis_soal=?, images=? WHERE ID=?", soal.Soal, soal.ID_Materi, soal.Kesulitan, soal.ID_jenis_soal, soal.Images, id)
	if err != nil {
		// Handle error here
		return c.RenderText("Update data gagal")
	}

	rows, err := db.Query("SELECT * FROM pilihan_jawaban WHERE ID_soal=?", id)
	if err != nil {
		// Handle error here
		return c.RenderText("Gagal mengambil pilihan jawaban")
	}
	defer rows.Close()

	for rows.Next() {
		pilihanjawaban := m.PilihanJawaban{}
		if err := rows.Scan(&pilihanjawaban.ID, &pilihanjawaban.Pilihan, &pilihanjawaban.IsTrue, &pilihanjawaban.IgnoreCase); err != nil {

			return c.RenderText("Gagal memindai pilihan jawaban")
		}

		_, err := db.Exec("UPDATE pilihan_jawaban SET pilihan=?, isTrue=?, ignoreCase=? WHERE ID=?", &pilihanjawaban.Pilihan, &pilihanjawaban.IsTrue, &pilihanjawaban.IgnoreCase, &pilihanjawaban.ID)
		if err != nil {
			// Handle error here
			return c.RenderText("Update pilihan jawaban gagal")
		}

	}

	return c.RenderText("Data soal dan pilihan berhasil diupdate")
}

func (c Soal) Delete(id int) revel.Result {

	db, err := sql.Open("mysql", revel.Config.StringDefault("db.spec", ""))
	if err != nil {
		return c.RenderText("Koneksi database gagal")
	}
	defer db.Close()

	_, err = db.Exec("DELETE FROM soal WHERE ID=?", id)
	if err != nil {
		// Handle error here
		return c.RenderText("Delete data gagal")
	}

	return c.RenderText("Data soal berhasil dihapus")
}
