//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** @author  John Miller
 *  @version 1.6
 *  @date    Sun Nov 24 18:11:03 EST 2019
 *  @see     LICENSE (MIT style license file).
 *
 *  @see `TASchedule` for applications and period code definitions
 */

package apps.database

// import scalation.math.StrO.StrNum

import scalation.columnar_db.RelationSQL

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `TAScheduleTab` object contains table definitions for the `TASchedule` app.
 *  "*" indicates section has been assigned TAs
 */
object TAScheduleTab
{
    /** The course sections table - for courses to be assigned TAs
     *  crn:      course reference number (section id)
     *  dept:     department
     *  cno:      course number
     *  title:    course title
     *  schedule  lecture:  LAS => lab/break-out, LEC => lecture
     *  period1:  period 1 @see `TASchedule` for details - ADDED 
     *  period2:  period 2 - 0 => don't care             - ADDED
     *  iname:    instructor name
     *  max_enr:  class size (max seats)
     *  cur_enr:  avail:    number of seats still available
     *  @see https://apps.reg.uga.edu/soc/SOCspring.pdf
     *  CRN, Subject, Course_Number, Title, Schedule, Period1, Period2, Instructor, Min_Credit_Hours, Max_Credit_Hours, Max_Enrollment, Current_Enrollment
     */
    val section    = RelationSQL ("section", Seq ("crn", "dept", "cno", "title", "schedule", "period1", "period2", "iname", "hours1", "hours2", "max_enr", "cur_enr"),
    Seq (Vector [Any] (14820, "CSCI", 1100,  "Topics in Computing", "LEC", "0", "0", "?", 3, 3, 44, 31),
         Vector [Any] (16885, "CSCI", 4810,  "Computer Graphics", "LEC", "0", "0", "?", 4, 4, 50, 43)),
    0, "ISISSSSSIIII", Seq((null,null,-1)))

    /** The course sections table - for courses taken by TAs that are not in the section table
     *  crn:      course reference number (section id)
     *  cno:      course number
     *  lecture:  0 => lab/break-out, 1 => lecture
     *  period1:  period 1 @see `TASchedule` for details
     *  period2:  period 2
     *  iname:    instructor name
     *  size:     class size (max seats)
     *  avail:    number of seats still available
     *  @see https://apps.reg.uga.edu/soc/SOCspring.pdf
     *  prefix -- BIOS 10, ETAP 20, GRSC 30, IDIS 40, LLED 50, MATH/PHIL 60, [skip UNIV 70 - 2900 has many sections - TA can switch sections ]
     */
    val section2   = RelationSQL ("section2", Seq ("crn", "cno", "lecture", "period1", "period2", "iname", "size", "avail"),
    Seq (Vector [Any] (62233, 2250, 1, 61,  0, "?", 60, 15),             // NCRR MTWR => 08:00 - 09:00
         Vector [Any] (65878, 7770, 1, 92,  0, "?", 30, 15)),
    0, "IIIIISII", Seq((null,null,-1)))

    /** The teaching assistants table
     *  tname:  last name of TA
     *  fname:  first name of TA
     *  level:  MS 0, PhD 1, PhD @ MS -1, IoR 2, GSRA 3
     *  gla:    qualified: Score and LLED 7769 or GRSC 7770 (0 => no, 1 => yes)
     */
    val ta = RelationSQL ("ta", Seq ("tname", "fname", "limit", "level"),
    Seq (Vector [Any] ("?", "?", 1, 1),
         Vector [Any] ("?", "?", 1, 2)),
    0, "SSII", Seq((null,null,-1)))

    /** The class schdedule for the teaching assistants table
     *  tname:    last name of TA
     *  periods:  the perioids for which the TA is taking classes
     *            71,72,73,74,75,76,77,78,,1,2,3,4,5,6,7,8,9,10
     *  cnos:     the course numbers as string, e.g., "6370, 6470"
     */
    val ta_sched   = RelationSQL ("ta_sched", Seq ("tname", "fname", "periods", "cnos"),
    Seq (Vector [Any] ("?", "?", "", "9000,9300"),
         Vector [Any] ("?", "?", "TR72,TR75,W3,M7", "6360,6950,7007,8955,9000")),
    -1, "SSSS", Seq((null,null,-1)))

    /** The preferred teaching assistants per faculty member
     */
    val i_pref     = RelationSQL ("i_pref", Seq ("iname", "tname1", "tname2"),
    Seq (Vector [Any] ("?", "?", "?"),
         Vector [Any] ("?", "?", "?")),
    0, "SSS", Seq((null,null,-1)))

    /** The assignment of teaching assistants to course sections (crn) table: randomly populate or load from csv file
     */
    val ta_assign  = RelationSQL ("ta_assign", Seq ("tname", "fname", "cno", "crn", "hours", "iname"),
    Seq (Vector [Any] ("?", "?", 2670, 1, 17.8, "?"),
         Vector [Any] ("?", "?", 1730, 0, 13.3, "?")),
    -1, "SSIIDS", Seq((null,null,-1)))

  /** The transripts for the teaching assistants indicating the courses that have taken (future enhancement)
   */
  //  val transcript = RelationSQL ("transcript", Seq ("tid", "cno"),
  //                   Seq (),
  //                   -1, "II", null)

} // TAScheduleTab object

import TAScheduleTab._

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `ShowTables` object prints all the tables defined in the `TAScheduleTab` object.
 *  > runMain apps.database.ShowTables
 */
object ShowTables extends App
{
    println (s"section: rows = ${section.repr.rows}")
    section.show ()
    println (s"section2: rows = ${section2.repr.rows}")
    section2.show ()
    println (s"ta: rows = ${ta.repr.rows}")
    ta.show ()
    println (s"ta_sched: rows = ${ta_sched.repr.rows}")
    ta_sched.show ()
    println (s"i_pref: rows = ${i_pref.repr.rows}")
    i_pref.show ()
    println (s"ta_assign: rows = ${ta_assign.repr.rows}")
    ta_assign.show ()

    val detail = ta_assign.join (Seq ("iname", "cno"), Seq ("iname", "cno"), section)
                          .select ("tname", "fname", "cno", "hours", "iname", "crn2", "period1") 
    println (s"detail: rows = ${detail.repr.rows}")
    detail.show ()

    (ta_assign.select ("tname") minus detail.select ("tname")).show ()

} // ShowTables object