<?php
/**
 * @file
 * The install file for the System module
 * 
 * This file is responsible for installing all of the required tables
 * for FlightPath.
*/



/**
 * Implementation of hook_enable.
 * 
 * This is meant to be called during initial installation.  We will make all of the
 * database changes we need to make in order to have FlightPath up and running.
 */
function system_enable() {
  
  // Set up our default roles
  db_query("INSERT INTO roles (rid, name)
            VALUES (1, 'anonymous user'), 
                   (2, 'authenticated user')");
   
  // Add in the anonymous user into the users table (uid = 0)
  db_query("INSERT INTO users (user_id, cwid, user_name) VALUES (0, 0, 'anonymous')");

  // Let's figure out what the current schema value is for this system module...
  $info_contents = file_get_contents("modules/system/system.info");    
  // From the info_contents variable, split up and place into an array.
  $info_details_array = array();
  $lines = explode("\n", $info_contents);
  foreach ($lines as $line) {
    if (trim($line) == "") continue;
    $temp = explode("=", trim($line));
    $info_details_array[trim($temp[0])] = trim(substr($line, strlen($temp[0]) + 1)); 
  }  

  // Set up the modules table-- add the system module first.    
  db_query("INSERT INTO modules (`path`, `name`, `version`, `enabled`, `weight`, `type`, `schema`)
            VALUES ('modules/system', 'system', 'core', 1, '-999', 'module', '?') ", $info_details_array["schema"]);
    

  // Let's perform installations on the other modules we want enabled by default.
  $modules = array("admin", "advise", "comments", "student_search", "update_status", "content", "announcements", "tinymce", "course_search", "blank_degrees", "user", 
                    "batch", 'stats', 'smtp', 'prereqs', 'lassie', 'calendar', 'alerts', 'audit', 'engagements', 'notify', 'student_files', 'encryption',
                    'student_priority', 'student_profile', 'masquerade');
  foreach ($modules as $module) {
    
    $info_contents = file_get_contents("modules/$module/$module.info");    
    // From the info_contents variable, split up and place into an array.
    $info_details_array = array();
    $lines = explode("\n", $info_contents);
    foreach ($lines as $line) {
      if (trim($line) == "") continue;
      $temp = explode("=", trim($line));
      $info_details_array[trim($temp[0])] = trim(substr($line, strlen($temp[0]) + 1)); 
    }      
    
    $schema = 0;
    if (isset($info_details_array['schema'])) $schema = $info_details_array['schema'];
    
    system_enable_module(array("module" => $module, "path" => "modules/$module", "version" => "core", "schema" => $schema));
  }
  
  
  /*
  // Set up some blocks for the system module by default.
  db_query("INSERT INTO blocks (section, region, module, delta, weight)
            VALUES ('system_main', 'right_col', 'system', 'tools', 1),
                   ('system_main', 'right_col', 'system', 'admin_tools', 2), 
                   ('system_main', 'left_col', 'announcements', 'primary', 0), 
                   ('system_login', 'left_col', 'blocks', 'block_1', 0), 
                   ('system_login', 'right_col', 'system', 'login_form', 0),
                   ('system_login', 'top', 'blocks', 'block_2', 0)
                   
                   ");  
    
   */
   
  // Set up some basic permissions for authenticated user.
  db_query("INSERT INTO role_permissions (rid, perm)
            VALUES 
              (2, 'access_logged_in_content'),
              (2, 'view_comments') ");

  
  // Add some default variable values, to make things a little easier on first-run.
  variable_set("earliest_catalog_year", date("Y"));
  variable_set("current_catalog_year", date("Y"));
  variable_set("current_draft_catalog_year", date("Y"));
  
  
  // Do we have "clean URLs" enabled?
  $test = system_check_clean_urls();
  variable_set("clean_urls", $test);
  
}



function system_update($old_schema, $new_schema) {
  
  if (intval($old_schema) < 2) {
    // We need to make the "watchdog.message" field a longtext instead of just "text".
    db_query("ALTER TABLE watchdog MODIFY message LONGTEXT");
    fpm("Altered watchdog table to change 'message' to LONGTEXT.");
  }  

  
  if (intval($old_schema) < 3) {    
    db_query("ALTER TABLE advising_comments MODIFY `comment` LONGTEXT");

    db_query("ALTER TABLE courses MODIFY `title` LONGTEXT, 
                                  MODIFY `description` LONGTEXT, 
                                  MODIFY `data_entry_comment` LONGTEXT");

    db_query("ALTER TABLE draft_courses MODIFY `title` LONGTEXT, 
                                  MODIFY `description` LONGTEXT, 
                                  MODIFY `data_entry_comment` LONGTEXT");


    db_query("ALTER TABLE degree_tracks MODIFY `track_description` LONGTEXT");    
    db_query("ALTER TABLE draft_degree_tracks MODIFY `track_description` LONGTEXT");

    db_query("ALTER TABLE degrees MODIFY `public_note` LONGTEXT,
                                  MODIFY `semester_titles_csv` LONGTEXT,
                                  MODIFY `track_selection_config` LONGTEXT");

    db_query("ALTER TABLE draft_degrees MODIFY `public_note` LONGTEXT,
                                  MODIFY `semester_titles_csv` LONGTEXT,
                                  MODIFY `track_selection_config` LONGTEXT");

    db_query("ALTER TABLE `groups` MODIFY `public_note` LONGTEXT,
                                 MODIFY `definition` LONGTEXT,
                                 MODIFY `icon_filename` LONGTEXT,
                                 MODIFY `data_entry_comment` LONGTEXT");

    db_query("ALTER TABLE draft_groups MODIFY `public_note` LONGTEXT,
                                 MODIFY `definition` LONGTEXT,
                                 MODIFY `icon_filename` LONGTEXT,
                                 MODIFY `data_entry_comment` LONGTEXT");
                                 
    db_query("ALTER TABLE draft_instructions MODIFY `instruction` LONGTEXT");

    
    
    db_query("ALTER TABLE `variables` MODIFY `value` LONGTEXT");   
    db_query("ALTER TABLE `student_priority` MODIFY `results` LONGTEXT");
    db_query("ALTER TABLE `user_attributes` MODIFY `value` LONGTEXT");
    db_query("ALTER TABLE `user_settings` MODIFY `value` LONGTEXT");
    db_query("ALTER TABLE `transfer_courses` MODIFY `description` LONGTEXT");
    db_query("ALTER TABLE student_settings MODIFY `settings` LONGTEXT");    
    db_query("ALTER TABLE student_substitutions MODIFY `sub_remarks` LONGTEXT");
            

    db_query("ALTER TABLE menu_router MODIFY `access_arguments` LONGTEXT,
                                      MODIFY `page_arguments` LONGTEXT,
                                      MODIFY `description` LONGTEXT,
                                      MODIFY `page_settings` LONGTEXT");
            
    db_query("ALTER TABLE modules MODIFY `requires` LONGTEXT,
                                  MODIFY `info` LONGTEXT");
                                
                                 
    db_query("ALTER TABLE watchdog MODIFY `variables` LONGTEXT,
                                   MODIFY `location` LONGTEXT,
                                   MODIFY `referer` LONGTEXT");
                                 
           
           
                                     
    fpm("Altered various system tables to change TEXT to LONGTEXT.");
  }    
  
  if (intval($old_schema) < 4) {
    // We want to add the concept of a "advising_session_token" to the advising_sessions table.
    db_query("ALTER TABLE advising_sessions ADD COLUMN `advising_session_token` varchar(255) ");
    db_query("ALTER TABLE advising_sessions ADD INDEX (`advising_session_token`) ");

    // Assign a unique advising session "token" to every advising session/group of sessions saved at the same time.
    $res = db_query("SELECT distinct(posted), student_id FROM advising_sessions");
    while ($cur = db_fetch_array($res)) {
      $p = $cur['posted'];
      $student_id = $cur['student_id'];
      $token = sha1(mt_rand(0, 99999) . $p . $student_id);
      db_query("UPDATE advising_sessions SET advising_session_token = ? WHERE posted = ? AND student_id = ?", array($token, $p, $student_id));
    }


  } // schema 4
  
  if (intval($old_schema) < 5) {
    // Add "delete_flag" to advising_sessions table, and a key for it as well.
    db_query("ALTER TABLE advising_sessions ADD COLUMN `delete_flag` tinyint(4) DEFAULT 0 ");
    db_query("ALTER TABLE advising_sessions ADD INDEX (`delete_flag`) ");        
    
    
  } // schema 5

  if (intval($old_schema) < 6) {
    // Add extra indexes to watchdog table for easier reporting.
    db_query("ALTER TABLE `watchdog` ADD INDEX (`timestamp`) ");
    db_query("ALTER TABLE `watchdog` ADD INDEX (`is_student`) ");
    db_query("ALTER TABLE `watchdog` ADD INDEX (`is_faculty`) ");
  }


  if (intval($old_schema) < 7) {
    // Add extra index to tables...
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`is_empty`) ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`posted`) ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`faculty_id`) ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`catalog_year`) ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`is_draft`) ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`is_whatif`) ");

    db_query("ALTER TABLE `advising_comments` ADD INDEX (`faculty_id`) ");
    db_query("ALTER TABLE `advising_comments` ADD INDEX (`posted`) ");
    db_query("ALTER TABLE `advising_comments` ADD INDEX (`term_id`) ");
    
    
  }



  if (intval($old_schema) < 8) {
      
    db_query("ALTER TABLE advising_sessions ADD COLUMN `most_recent_session` tinyint(4) DEFAULT 0 ");
    db_query("ALTER TABLE `advising_sessions` ADD INDEX (`most_recent_session`) ");    
    
  }

  
  if (intval($old_schema) < 9) {
    
    db_query("ALTER TABLE student_courses ADD COLUMN `course_id` int(11) DEFAULT 0 ");
    db_query("ALTER TABLE `student_courses` ADD INDEX (`course_id`) ");    
        
  }


  if (intval($old_schema) < 10) {
    db_query("ALTER TABLE `courses` ADD INDEX (`exclude`) ");
    db_query("ALTER TABLE `courses` ADD INDEX (`delete_flag`) ");

    db_query("ALTER TABLE `draft_courses` ADD INDEX (`exclude`) ");
    db_query("ALTER TABLE `draft_courses` ADD INDEX (`delete_flag`) ");
    
  }


  if (intval($old_schema) < 11) {
      
    $dept_val = "";
    $res = db_query("SELECT * FROM faculty 
                     GROUP BY department_code
                     ORDER BY department");
    while ($cur = db_fetch_array($res)) {    
      $key = trim($cur["department_code"]);
      $value = trim($cur["department"]);
     
      if ($key == "") {
        $key = "UNSPECIFIED";
      }
              
      //$dept_options[$key] = $dispval;

      $dept_val .= $key . " ~ " . $value . "\n";
            
    }       
    
    variable_set('departments', $dept_val);
    
  }


  if (intval($old_schema) < 12) {
    // Adding indexs to student_degrees table.
    db_query("ALTER TABLE `student_degrees` ADD INDEX (`major_code`) ");
    db_query("ALTER TABLE `student_degrees` ADD INDEX (`delete_flag`) ");
    
    // Remove "department" column from faculty table.
    db_query("ALTER TABLE faculty DROP COLUMN `department`");
    
    
  }


  
      
} // update



/**
 * Implementation of hook_install.
 * 
 * This will create all of our tables.
 */ 
function system_install() {
  
  // Since this will ONLY be called during initial install, we do not
  // need to first check for existing tables; it is assumed that the database
  // is empty.
  
  // Therefore, let's place all of our create table statements in one query, for
  // simplicity.
    
  $q = "
CREATE TABLE `advised_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `advising_session_id` int(11) DEFAULT '0',
  `course_id` int(11) DEFAULT '0',
  `entry_value` varchar(20) DEFAULT '',
  `semester_num` int(11) DEFAULT '0',
  `group_id` varchar(50) DEFAULT '',
  `var_hours` decimal(8,4) DEFAULT '0.0000',
  `term_id` varchar(20) DEFAULT '',
  `degree_id` int(11) DEFAULT '0',  
  PRIMARY KEY (`id`),
  KEY `advid` (`advising_session_id`),
  KEY `course_id` (`course_id`),
  KEY `ev` (`entry_value`),
  KEY `degree_id` (`degree_id`),
  KEY `term_id` (`term_id`),
  KEY `semester_num` (`semester_num`)
);   ";

  
  db_query($q);

  $q = "  
  
CREATE TABLE `advising_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '0',
  `faculty_id` varchar(30) DEFAULT '0',
  `term_id` varchar(20) DEFAULT '',
  `comment` longtext DEFAULT NULL,
  `posted` int(10) unsigned DEFAULT 0,
  `access_type` varchar(20) DEFAULT '',
  `delete_flag` tinyint(4) DEFAULT 0,  
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `faculty_id` (`faculty_id`),
  KEY `posted` (`posted`),
  KEY `term_id` (`term_id`),
  KEY `delete_flag` (`delete_flag`),
  KEY `access_type` (`access_type`)  
); 
  ";

  db_query($q);

$q = "
CREATE TABLE `advising_sessions` (
  `advising_session_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '0',
  `faculty_id` varchar(30) DEFAULT '0',
  `term_id` varchar(20) DEFAULT '',
  `degree_id` int(11) DEFAULT 0,
  `major_code_csv` varchar(255) DEFAULT '',
  `catalog_year` int(11) DEFAULT 0,
  `posted` int(10) unsigned DEFAULT 0,
  `is_whatif` tinyint(4) DEFAULT 0,
  `is_draft` tinyint(4) DEFAULT 0,
  `is_empty` tinyint(4) DEFAULT 0,
  `advising_session_token` varchar(255) DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT 0,
  `most_recent_session` tinyint(4) DEFAULT 0,
  PRIMARY KEY (`advising_session_id`),
  KEY `sid` (`student_id`),
  KEY `termid` (`term_id`),
  KEY `is_empty` (`is_empty`),
  KEY `posted` (`posted`),
  KEY `faculty_id` (`faculty_id`),
  KEY `catalog_year` (`catalog_year`),
  KEY `is_draft` (`is_draft`),
  KEY `is_whatif` (`is_whatif`),
  KEY `delete_flag` (`delete_flag`),
  KEY `most_recent_session` (`most_recent_session`),
  KEY `advising_session_token` (`advising_session_token`)
);   ";
db_query($q);





$q = "

CREATE TABLE `advisor_student` (
  `faculty_id` varchar(30) NOT NULL DEFAULT '0',
  `student_id` varchar(30) NOT NULL DEFAULT '0',
  PRIMARY KEY (`faculty_id`,`student_id`) 
);  ";

db_query($q);


/*
$q = "
CREATE TABLE `blocks` (
  `bid` int(10) unsigned   AUTO_INCREMENT,
  `section` varchar(255)   DEFAULT '',
  `region` varchar(255)   DEFAULT '',
  `module` varchar(255)   DEFAULT '',
  `delta` varchar(255)   DEFAULT '',
  `weight` int(11)   DEFAULT '0',
  PRIMARY KEY (`bid`)
); ";
db_query($q);
 * 
 */
 
 
$q = "
CREATE TABLE `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) DEFAULT 0,
  `subject_id` varchar(10)  DEFAULT '',
  `course_num` varchar(10)  DEFAULT '',
  `catalog_year` int(11) DEFAULT 0,
  `title` longtext  DEFAULT NULL,
  `description` longtext  DEFAULT NULL,
  `min_hours` decimal(8,4) DEFAULT 0.0000,
  `max_hours` decimal(8,4) DEFAULT 0.0000,
  `repeat_hours` decimal(8,4) DEFAULT 0.0000,
  `exclude` tinyint(4) DEFAULT 0,
  `data_entry_comment` longtext  DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `course_id` (`course_id`) ,
  KEY `subject_id` (`subject_id`) ,
  KEY `course_num` (`course_num`) ,
  KEY `delete_flag` (`delete_flag`) ,
  KEY `exclude` (`exclude`) ,
  KEY `catalog_year` (`catalog_year`) ,
  KEY `school_id` (`school_id`),
  KEY `catalog_year_2` (`catalog_year`,`exclude`,`school_id`,`delete_flag`),
  KEY `course_num_2` (`course_num`,`catalog_year`,`exclude`,`delete_flag`,`school_id`),
  KEY `catalog_year_3` (`catalog_year`,`exclude`,`delete_flag`)
);  ";
db_query($q);


db_query("CREATE TABLE `colleges` (
  `college_code` varchar(10) NOT NULL,
  `title` varchar(255) DEFAULT '',
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`college_code`,`school_id`),
  KEY `school_id` (`school_id`)
);  ");

db_query("CREATE TABLE `degree_college` (
  `major_code` varchar(100) NOT NULL DEFAULT '',
  `college_code` varchar(10) DEFAULT '',
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`major_code`,`school_id`),
  KEY `college_code` (`college_code`),
  KEY `school_id` (`school_id`)
);  "); 


$q = "
CREATE TABLE `degree_requirements` (
  `id` int(11)   AUTO_INCREMENT,
  `degree_id` int(11)   DEFAULT '0',
  `semester_num` int(11)   DEFAULT '0',
  `group_id` int(11)   DEFAULT '0',
  `group_requirement_type` varchar(10)   DEFAULT '',
  `group_min_hours_allowed` decimal(8,4)   DEFAULT '0.0000',
  `group_hours_required` decimal(8,4)   DEFAULT '0.0000',
  `group_min_grade` varchar(10)   DEFAULT '',
  `course_id` int(11)   DEFAULT '0',
  `course_min_grade` varchar(10)   DEFAULT '',
  `course_requirement_type` varchar(10)   DEFAULT '',
  `data_entry_value` varchar(50)   DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `degree_id` (`degree_id`),
  KEY `group_id` (`group_id`),
  KEY `dev` (`data_entry_value`)
); ";
db_query($q);

$q = "
CREATE TABLE `degree_tracks` (
  `track_id` int(11) NOT NULL AUTO_INCREMENT,
  `catalog_year` int(11) DEFAULT 2006,
  `major_code` varchar(100) DEFAULT '',
  `track_code` varchar(20) DEFAULT '',
  `track_title` varchar(100) DEFAULT '',
  `track_short_title` varchar(50) DEFAULT '',
  `track_description` longtext DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`track_id`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `degrees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `degree_id` int(11) DEFAULT 0,
  `major_code` varchar(100)  DEFAULT '',
  `degree_type` varchar(20)  DEFAULT '',
  `degree_level` varchar(5)  DEFAULT '',
  `degree_class` varchar(40)  DEFAULT 'MAJOR',
  `title` varchar(200)  DEFAULT '',
  `public_note` longtext  DEFAULT NULL,
  `semester_titles_csv` longtext  DEFAULT NULL,
  `catalog_year` int(11) DEFAULT 2006,
  `exclude` int(11) DEFAULT 0,
  `allow_dynamic` int(11) DEFAULT 0,
  `advising_weight` int(11) DEFAULT 0,
  `override_degree_hours` varchar(20)  DEFAULT '',
  `min_tracks` int(11) DEFAULT 0,
  `max_tracks` int(11) DEFAULT 0,
  `default_tracks` varchar(255)  DEFAULT '',
  `track_selection_config` longtext  DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `degree_id` (`degree_id`) ,
  KEY `major_code` (`major_code`) ,
  KEY `degree_level` (`degree_level`) ,
  KEY `degree_class` (`degree_class`) ,
  KEY `allow_dynamic` (`allow_dynamic`) ,
  KEY `advising_weight` (`advising_weight`) ,
  KEY `catalog_year` (`catalog_year`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `draft_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) DEFAULT 0,
  `subject_id` varchar(10)  DEFAULT '',
  `course_num` varchar(10)  DEFAULT '',
  `catalog_year` int(11) DEFAULT 2006,
  `title` longtext  DEFAULT NULL,
  `description` longtext  DEFAULT NULL,
  `min_hours` decimal(8,4) DEFAULT 0.0000,
  `max_hours` decimal(8,4) DEFAULT 0.0000,
  `repeat_hours` decimal(8,4) DEFAULT 0.0000,
  `exclude` tinyint(4) DEFAULT 0,
  `data_entry_comment` longtext  DEFAULT NULL,
  `delete_flag` tinyint(4) DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `course_id` (`course_id`) ,
  KEY `subject_id` (`subject_id`) ,
  KEY `course_num` (`course_num`) ,
  KEY `delete_flag` (`delete_flag`) ,
  KEY `exclude` (`exclude`) ,    
  KEY `catalog_year` (`catalog_year`) ,
  KEY `school_id` (`school_id`),
  KEY `catalog_year_2` (`catalog_year`,`exclude`,`school_id`,`delete_flag`),
  KEY `course_num_2` (`course_num`,`catalog_year`,`exclude`,`delete_flag`,`school_id`),
  KEY `catalog_year_3` (`catalog_year`,`exclude`,`delete_flag`)
);  ";
db_query($q);

$q = "
CREATE TABLE `draft_degree_requirements` (
  `id` int(11)   AUTO_INCREMENT,
  `degree_id` int(11)   DEFAULT '0',
  `semester_num` int(11)   DEFAULT '0',
  `group_id` int(11)   DEFAULT '0',
  `group_requirement_type` varchar(10)   DEFAULT '',
  `group_min_hours_allowed` decimal(8,4)   DEFAULT '0.0000',
  `group_hours_required` decimal(8,4)   DEFAULT '0.0000',
  `group_min_grade` varchar(10)   DEFAULT '',
  `course_id` int(11)   DEFAULT '0',
  `course_min_grade` varchar(10)   DEFAULT '',
  `course_requirement_type` varchar(10)   DEFAULT '',
  `data_entry_value` varchar(50)   DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `degree_id` (`degree_id`),
  KEY `group_id` (`group_id`),
  KEY `dev` (`data_entry_value`)
);";
db_query($q);

$q = "
CREATE TABLE `draft_degree_tracks` (
  `track_id` int(11) NOT NULL AUTO_INCREMENT,
  `catalog_year` int(11) DEFAULT 2006,
  `major_code` varchar(100) DEFAULT '',
  `track_code` varchar(20) DEFAULT '',
  `track_title` varchar(100) DEFAULT '',
  `track_short_title` varchar(50) DEFAULT '',
  `track_description` longtext DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`track_id`) ,
  KEY `school_id` (`school_id`)
); ";
db_query($q);

$q = "
CREATE TABLE `draft_degrees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `degree_id` int(11) DEFAULT 0,
  `major_code` varchar(100)  DEFAULT '',
  `degree_type` varchar(20)  DEFAULT '',
  `degree_level` varchar(5)  DEFAULT '',
  `degree_class` varchar(40)  DEFAULT 'MAJOR',
  `title` varchar(200)  DEFAULT '',
  `public_note` longtext  DEFAULT NULL,
  `semester_titles_csv` longtext  DEFAULT NULL,
  `catalog_year` int(11) DEFAULT 2006,
  `exclude` int(11) DEFAULT 0,
  `allow_dynamic` int(11) DEFAULT 0,
  `advising_weight` int(11) DEFAULT 0,
  `override_degree_hours` varchar(20)  DEFAULT '',
  `min_tracks` int(11) DEFAULT 0,
  `max_tracks` int(11) DEFAULT 0,
  `default_tracks` varchar(255)  DEFAULT '',
  `track_selection_config` longtext  DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `degree_id` (`degree_id`) ,
  KEY `major_code` (`major_code`) ,
  KEY `exclude` (`exclude`) ,
  KEY `allow_dynamic` (`allow_dynamic`) ,
  KEY `advising_weight` (`advising_weight`) ,
  KEY `degree_level` (`degree_level`) ,
  KEY `degree_class` (`degree_class`) ,
  KEY `catalog_year` (`catalog_year`) ,
  KEY `school_id` (`school_id`)
); ";
db_query($q);

$q = "
CREATE TABLE `draft_group_requirements` (
  `id` int(11)   AUTO_INCREMENT,
  `group_id` int(11)   DEFAULT '0',
  `course_id` int(11)   DEFAULT '0',
  `course_min_grade` varchar(10)   DEFAULT '',
  `course_repeats` int(11)   DEFAULT '0',
  `attributes` varchar(255) DEFAULT '',
  `child_group_id` int(11)   DEFAULT '0',
  `data_entry_value` varchar(50)   DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `dev` (`data_entry_value`)
); ";
db_query($q);



$q = "

CREATE TABLE `draft_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT 0,
  `group_name` varchar(200) DEFAULT '',
  `title` varchar(255) DEFAULT '',
  `public_note` longtext DEFAULT NULL,
  `definition` longtext DEFAULT NULL,
  `icon_filename` longtext DEFAULT NULL,
  `catalog_year` int(11) DEFAULT 2006,
  `priority` int(11) DEFAULT 50,
  `delete_flag` tinyint(4) DEFAULT 0,
  `data_entry_comment` longtext DEFAULT NULL,
  `catalog_repeat` tinyint(4) DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `group_id` (`group_id`) ,
  KEY `group_name` (`group_name`) ,
  KEY `catalog_year` (`catalog_year`) ,
  KEY `title` (`title`) ,
  KEY `delete_flag` (`delete_flag`) ,
  KEY `catalog_repeat` (`catalog_repeat`) ,
  KEY `priority` (`priority`) ,
  KEY `school_id` (`school_id`)
);     ";
db_query($q);

$q = "
CREATE TABLE `draft_instructions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instruction` longtext DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `school_id` (`school_id`)
); ";
db_query($q);

$q = "
CREATE TABLE `faculty` (
  `cwid` varchar(30) NOT NULL,
  `college` varchar(255) DEFAULT '',
  `department_code` varchar(255) DEFAULT '',  
  `major_code_csv` varchar(255) DEFAULT '',  
  PRIMARY KEY (`cwid`),
  KEY `major_code_csv` (`major_code_csv`),  
  KEY `department_code` (`department_code`),
  KEY `college` (`college`)  
);  ";
db_query($q);

$q = "
CREATE TABLE `group_requirements` (
  `id` int(11)   AUTO_INCREMENT,
  `group_id` int(11)   DEFAULT '0',
  `course_id` int(11)   DEFAULT '0',
  `course_min_grade` varchar(10)   DEFAULT '',
  `course_repeats` int(11)   DEFAULT '0',
  `attributes` varchar(255) DEFAULT '',
  `child_group_id` int(11)   DEFAULT '0',
  `data_entry_value` varchar(50)   DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  KEY `dev` (`data_entry_value`)
); ";
db_query($q);

$q = "
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT 0,
  `group_name` varchar(200) DEFAULT '',
  `title` varchar(255) DEFAULT '',
  `public_note` longtext DEFAULT NULL,
  `definition` longtext DEFAULT NULL,
  `icon_filename` longtext DEFAULT NULL,
  `catalog_year` int(11) DEFAULT 2006,
  `priority` int(11) DEFAULT 50,
  `delete_flag` tinyint(4) DEFAULT 0,
  `data_entry_comment` longtext DEFAULT NULL,
  `catalog_repeat` tinyint(4) DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `group_id` (`group_id`) ,
  KEY `group_name` (`group_name`) ,
  KEY `catalog_year` (`catalog_year`) ,
  KEY `title` (`title`) ,
  KEY `delete_flag` (`delete_flag`) ,
  KEY `catalog_repeat` (`catalog_repeat`) ,
  KEY `priority` (`priority`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `menu_router` (
  `path` varchar(255)  ,
  `access_callback` varchar(255) DEFAULT '',
  `access_arguments` longtext,
  `page_callback` varchar(255) DEFAULT '',
  `page_arguments` longtext,
  `title` varchar(255) DEFAULT '',
  `description` longtext,
  `type` tinyint(3) unsigned DEFAULT '0',
  `tab_family` varchar(255) DEFAULT '',
  `tab_parent` varchar(255) DEFAULT '',
  `weight` int(11) DEFAULT '0',
  `icon` varchar(255) DEFAULT '',
  `page_settings` longtext,
  `file` varchar(255) DEFAULT '',
  PRIMARY KEY (`path`),
  KEY `type` (`type`),
  KEY `tab_family` (`tab_family`)
); ";
db_query($q);

$q = "
CREATE TABLE `modules` (
  `path` varchar(255)  ,
  `name` varchar(100)   DEFAULT '',
  `version` varchar(20)   DEFAULT '',
  `requires` longtext,
  `enabled` int(11)   DEFAULT '0',
  `weight` int(11)   DEFAULT '0',
  `type` varchar(20)   DEFAULT '',
  `schema` int(11)   DEFAULT '0',
  `info` longtext,
  PRIMARY KEY (`path`)
); ";
db_query($q);

$q = "
CREATE TABLE `role_permissions` (
  `pid` int(10) unsigned   AUTO_INCREMENT,
  `rid` int(10) unsigned   DEFAULT '0',
  `perm` varchar(255)   DEFAULT '',
  PRIMARY KEY (`pid`),
  KEY `perm` (`perm`),
  KEY `rid` (`rid`)
); ";
db_query($q);

$q = "
CREATE TABLE `roles` (
  `rid` int(11)   AUTO_INCREMENT,
  `name` varchar(255)   DEFAULT '',
  PRIMARY KEY (`rid`)
); ";
db_query($q);

$q = "
CREATE TABLE `standardized_tests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_id` varchar(20) DEFAULT '',
  `category_id` varchar(20) DEFAULT '',
  `position` int(11) DEFAULT 0,
  `test_description` varchar(200) DEFAULT '',
  `category_description` varchar(200) DEFAULT '',
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `student_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `subject_id` varchar(10) DEFAULT '',
  `course_num` varchar(10) DEFAULT '',
  `hours_awarded` decimal(8,4) DEFAULT 0.0000,
  `grade` varchar(5) DEFAULT '',
  `term_id` varchar(20) DEFAULT '',
  `level_code` varchar(10) DEFAULT '',  
  `course_id` int(11) DEFAULT 0,  
  PRIMARY KEY (`id`) ,
  KEY `student_id` (`student_id`) ,
  KEY `course_id` (`course_id`) ,
  KEY `level_code` (`level_code`)  
);  ";
db_query($q);



$q = "
CREATE TABLE `student_degrees` (
  `student_id` varchar(30) NOT NULL DEFAULT '',
  `major_code` varchar(100) NOT NULL DEFAULT '',
  `is_editable` tinyint DEFAULT '0',
  `delete_flag` tinyint DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`student_id`,`major_code`),
  KEY `extra_data` (`extra_data`(250)),
  KEY `major_code` (`major_code`),
  KEY `delete_flag` (`delete_flag`)
);   ";
db_query($q);


$q = "
CREATE TABLE `student_developmentals` (
  `student_id` varchar(30) NOT NULL,
  `requirement` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`student_id`,`requirement`), 
  KEY `requirement` (`requirement`)
); ";
db_query($q);


$q = "
CREATE TABLE `student_settings` (
  `student_id` varchar(30) NOT NULL,
  `settings` longtext DEFAULT NULL,
  `posted` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`student_id`)
) ;  ";
db_query($q);

$q = "
CREATE TABLE `student_substitutions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `faculty_id` varchar(30) DEFAULT '',
  `required_course_id` int(11) DEFAULT 0,
  `required_entry_value` varchar(20) DEFAULT '',
  `required_group_id` varchar(50) DEFAULT '',
  `required_degree_id` int(11) unsigned DEFAULT 0,
  `required_semester_num` int(11) DEFAULT 0,
  `sub_course_id` int(11) DEFAULT 0,
  `sub_entry_value` varchar(20) DEFAULT '',
  `sub_term_id` varchar(20) DEFAULT '',
  `sub_transfer_flag` tinyint(4) DEFAULT 0,
  `sub_hours` decimal(8,4) DEFAULT 0.0000,
  `sub_remarks` longtext DEFAULT NULL,
  `posted` int(10) unsigned DEFAULT 0,
  `delete_flag` tinyint(4) DEFAULT 0,  
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `rev` (`required_entry_value`),
  KEY `sev` (`sub_entry_value`)  
);  ";
db_query($q);

$q = "
CREATE TABLE `student_tests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `test_id` varchar(20) DEFAULT '',
  `category_id` varchar(20) DEFAULT '',
  `score` varchar(10) DEFAULT '',
  `date_taken` datetime DEFAULT '1970-01-01 00:00:01',
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `school_id` (`school_id`)
); ";
db_query($q);

$q = "
CREATE TABLE `student_transfer_courses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `transfer_course_id` int(11) DEFAULT 0,
  `student_specific_course_title` varchar(255) DEFAULT '',
  `term_id` varchar(20) DEFAULT '',
  `grade` varchar(5) DEFAULT '',
  `hours_awarded` decimal(8,4) DEFAULT 0.0000,
  `level_code` varchar(10) DEFAULT '',  
  PRIMARY KEY (`id`) ,
  KEY `student_id` (`student_id`) ,
  KEY `transfer_id` (`transfer_course_id`) ,
  KEY `term_id` (`term_id`) ,
  KEY `grade` (`grade`) ,
  KEY `level_code` (`level_code`) ,
  KEY `hrs` (`hours_awarded`)  
);  ";
db_query($q);

$q = "
CREATE TABLE `student_unassign_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `faculty_id` varchar(30) DEFAULT '',
  `course_id` int(11) DEFAULT 0,
  `term_id` varchar(20) DEFAULT '',
  `transfer_flag` tinyint(4) DEFAULT 0,
  `group_id` varchar(50) DEFAULT '',
  `degree_id` int(11) DEFAULT 0,
  `delete_flag` tinyint(4) DEFAULT 0,
  `posted` int(10) unsigned DEFAULT 0,  
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `faculty_id` (`faculty_id`),
  KEY `delete_flag` (`delete_flag`)  
);  ";
db_query($q);

$q = "
CREATE TABLE `student_unassign_transfer_eqv` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '',
  `faculty_id` varchar(30) DEFAULT '',
  `transfer_course_id` int(11) DEFAULT 0,
  `delete_flag` tinyint(4) DEFAULT 0,
  `posted` int(10) unsigned DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `faculty_id` (`faculty_id`),
  KEY `transfer_course_id` (`transfer_course_id`),
  KEY `delete_flag` (`delete_flag`),
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `students` (
  `cwid` varchar(30) NOT NULL DEFAULT '',
  `cumulative_hours` varchar(5) DEFAULT '',
  `gpa` varchar(5) DEFAULT '',
  `rank_code` varchar(5) DEFAULT '',
  `catalog_year` int(11) DEFAULT 2006,
  `is_active` tinyint(11) DEFAULT 0,
  PRIMARY KEY (`cwid`),
  KEY `rank_code` (`rank_code`),
  KEY `is_active` (`is_active`)
);  ";
db_query($q);

$q = "
CREATE TABLE `subjects` (
  `subject_id` varchar(10) NOT NULL,
  `college` varchar(10)  DEFAULT NULL,
  `title` varchar(255)  DEFAULT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`subject_id`,`school_id`),
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `transfer_courses` (
  `transfer_course_id` int(11) NOT NULL AUTO_INCREMENT,
  `institution_id` varchar(100) DEFAULT '',
  `subject_id` varchar(10) DEFAULT '',
  `course_num` varchar(10) DEFAULT '',
  `title` varchar(100) DEFAULT '',
  `description` longtext DEFAULT NULL,
  `min_hours` decimal(8,4) DEFAULT 0.0000,
  `max_hours` decimal(8,4) DEFAULT 0.0000,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`transfer_course_id`) ,
  KEY `ic` (`institution_id`) ,
  KEY `si` (`subject_id`) ,
  KEY `cn` (`course_num`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);

$q = "
CREATE TABLE `transfer_eqv_per_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(30) DEFAULT '', 
  `transfer_course_id` int(11) DEFAULT 0,
  `local_course_id` int(11) DEFAULT 0,
  `valid_term_id` varchar(20) DEFAULT '',
  `broken_id` int(11) DEFAULT 0,  
  PRIMARY KEY (`id`) ,
  KEY `student_id` (`student_id`) ,
  KEY `transfer_course_id` (`transfer_course_id`) ,
  KEY `local_course_id` (`local_course_id`) ,
  KEY `broken_id` (`broken_id`)  
);  ";
db_query($q);

$q = "
CREATE TABLE `transfer_institutions` (
  `institution_id` varchar(100) NOT NULL DEFAULT '',
  `name` varchar(200) DEFAULT '',
  `state` varchar(10) DEFAULT '',
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`institution_id`) ,
  KEY `state` (`state`) ,
  KEY `name` (`name`) ,
  KEY `school_id` (`school_id`)
); ";
db_query($q);

$q = "
CREATE TABLE `user_roles` (
  `user_id` int(11)  ,
  `rid` int(11)   DEFAULT '0',
  PRIMARY KEY (`user_id`,`rid`),
  KEY (`rid`)
); ";
db_query($q);


$q = "
CREATE TABLE `user_settings` (
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext DEFAULT NULL,
  `updated` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`name`),
  KEY `value` (`value`(768)),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`),
  KEY `updated` (`updated`)
);";
db_query($q);




$q = "
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT '',
  `password` varchar(255) DEFAULT '',
  `is_student` tinyint(4) DEFAULT 0,
  `is_faculty` tinyint(4) DEFAULT 0,
  `email` varchar(255) DEFAULT '',
  `cwid` varchar(30) DEFAULT '',
  `f_name` varchar(100) DEFAULT '',
  `l_name` varchar(100) DEFAULT '',
  `is_disabled` tinyint(4) DEFAULT 0,
  `last_login` int(10) unsigned DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`) ,
  KEY `cwid` (`cwid`) ,
  KEY `user_name` (`user_name`) ,
  KEY `is_disabled` (`is_disabled`) ,
  KEY `is_faculty` (`is_faculty`) ,
  KEY `is_student` (`is_student`) ,
  KEY `school_id` (`school_id`)
);  ";
db_query($q);



$q = "
CREATE TABLE `variables` (
  `name` varchar(255)  ,
  `value` longtext,
  PRIMARY KEY (`name`)
); ";
db_query($q);


$q = "
CREATE TABLE `student_priority` (
  `student_id` varchar(30) NOT NULL,
  `priority_value` decimal(8,4) DEFAULT NULL,
  `results` longtext DEFAULT NULL,
  `updated` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`student_id`) ,
  KEY `priority_value` (`priority_value`),
  KEY `updated` (`updated`)
);  ";
db_query($q);



$q = "
CREATE TABLE `user_attributes` (
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext DEFAULT NULL,
  `updated` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`name`),
  KEY `value` (`value`(768)),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`),
  KEY `updated` (`updated`)
);  ";
db_query($q);


 
$q = "
CREATE TABLE `watchdog` (
  `wid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT 0,
  `user_name` varchar(50) DEFAULT '',
  `cwid` varchar(30) DEFAULT '',
  `type` varchar(100) DEFAULT '',
  `message` longtext DEFAULT NULL,
  `variables` longtext DEFAULT NULL,
  `severity` tinyint(3) unsigned DEFAULT 0,
  `extra_data` varchar(255) DEFAULT '',
  `location` longtext DEFAULT NULL,
  `referer` longtext DEFAULT NULL,
  `ip` varchar(64) DEFAULT '',
  `is_mobile` tinyint(4) DEFAULT 0,
  `is_student` tinyint(4) DEFAULT 0,
  `is_faculty` tinyint(4) DEFAULT 0,
  `timestamp` int(11) unsigned DEFAULT 0,
  `school_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`wid`),
  KEY `type` (`type`),
  KEY `uid` (`user_id`),
  KEY `uname` (`user_name`),
  KEY `severity` (`severity`),
  KEY `cwid` (`cwid`),
  KEY `timestamp` (`timestamp`),
  KEY `is_student` (`is_student`),
  KEY `is_faculty` (`is_faculty`),  
  KEY `school_id` (`school_id`)
);  ";
  
  db_query($q); 
      
}