-- MySQL Script generated by MySQL Workbench
-- Mon May 21 09:30:00 2018
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema finschool
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `user_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_roles` (
  `role_id` INT(11) NOT NULL AUTO_INCREMENT,
  `role_name` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`role_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_email` VARCHAR(45) NULL DEFAULT NULL,
  `user_password` VARCHAR(45) NULL DEFAULT NULL,
  `user_full_name` VARCHAR(255) NULL DEFAULT NULL,
  `user_image` VARCHAR(255) NULL DEFAULT NULL,
  `user_description` TEXT NULL DEFAULT NULL,
  `user_role_role_id` INT(11) NULL,
  `user_is_deleted` TINYINT(1) NULL DEFAULT 0,
  `user_input_date` TIMESTAMP NULL DEFAULT NULL,
  `user_last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  INDEX `fk_user_user_role1_idx` (`user_role_role_id` ASC),
  CONSTRAINT `fk_user_user_role1`
    FOREIGN KEY (`user_role_role_id`)
    REFERENCES `user_roles` (`role_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logs` (
  `log_id` INT(11) NOT NULL AUTO_INCREMENT,
  `log_date` TIMESTAMP NULL DEFAULT NULL,
  `log_action` VARCHAR(45) NULL DEFAULT NULL,
  `log_module` VARCHAR(45) NULL DEFAULT NULL,
  `log_info` TEXT NULL DEFAULT NULL,
  `user_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  INDEX `fk_g_activity_log_g_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_g_activity_log_g_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ci_sessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `id` VARCHAR(40) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `timestamp` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `data` BLOB NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ci_sessions_timestamp` (`timestamp` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `class`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `class` (
  `class_id` INT NOT NULL AUTO_INCREMENT,
  `class_name` VARCHAR(45) NULL,
  PRIMARY KEY (`class_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `majors`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `majors` (
  `majors_id` INT NOT NULL AUTO_INCREMENT,
  `majors_name` VARCHAR(100) NULL,
  `majors_short_name` VARCHAR(45) NULL,
  PRIMARY KEY (`majors_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `student` (
  `student_id` INT NOT NULL AUTO_INCREMENT,
  `student_nis` VARCHAR(45) NULL,
  `student_nisn` VARCHAR(45) NULL,
  `student_password` VARCHAR(100) NULL,
  `student_full_name` VARCHAR(255) NULL,
  `student_gender` ENUM('L', 'P') NULL,
  `student_born_place` VARCHAR(45) NULL,
  `student_born_date` DATE NULL,
  `student_img` VARCHAR(255) NULL,
  `student_phone` VARCHAR(45) NULL,
  `student_hobby` VARCHAR(100) NULL,
  `student_address` TEXT NULL,
  `student_name_of_mother` VARCHAR(255) NULL,
  `student_name_of_father` VARCHAR(255) NULL,
  `student_parent_phone` VARCHAR(45) NULL,
  `class_class_id` INT NULL,
  `majors_majors_id` INT NULL,
  `student_status` TINYINT(1) NULL DEFAULT 1,
  `student_input_date` TIMESTAMP NULL,
  `student_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`student_id`),
  INDEX `fk_student_class1_idx` (`class_class_id` ASC),
  INDEX `fk_student_majors1_idx` (`majors_majors_id` ASC),
  CONSTRAINT `fk_student_class1`
    FOREIGN KEY (`class_class_id`)
    REFERENCES `class` (`class_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_student_majors1`
    FOREIGN KEY (`majors_majors_id`)
    REFERENCES `majors` (`majors_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `period`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `period` (
  `period_id` INT NOT NULL AUTO_INCREMENT,
  `period_start` YEAR NULL,
  `period_end` YEAR NULL,
  `period_status` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`period_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pos` (
  `pos_id` INT NOT NULL AUTO_INCREMENT,
  `pos_name` VARCHAR(100) NULL,
  `pos_description` VARCHAR(100) NULL,
  PRIMARY KEY (`pos_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `payment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment` (
  `payment_id` INT NOT NULL AUTO_INCREMENT,
  `payment_type` ENUM('BEBAS', 'BULAN') NULL,
  `period_period_id` INT NULL,
  `pos_pos_id` INT NULL,
  `payment_input_date` TIMESTAMP NULL,
  `payment_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`payment_id`),
  INDEX `fk_payment_pos1_idx` (`pos_pos_id` ASC),
  INDEX `fk_payment_period1_idx` (`period_period_id` ASC),
  CONSTRAINT `fk_payment_pos1`
    FOREIGN KEY (`pos_pos_id`)
    REFERENCES `pos` (`pos_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_payment_period1`
    FOREIGN KEY (`period_period_id`)
    REFERENCES `period` (`period_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bebas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bebas` (
  `bebas_id` INT NOT NULL AUTO_INCREMENT,
  `student_student_id` INT NULL,
  `payment_payment_id` INT NULL,
  `bebas_bill` DECIMAL NULL,
  `bebas_total_pay` DECIMAL NULL DEFAULT 0,
  `bebas_input_date` TIMESTAMP NULL,
  `bebas_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`bebas_id`),
  INDEX `fk_bebas_payment1_idx` (`payment_payment_id` ASC),
  INDEX `fk_bebas_student1_idx` (`student_student_id` ASC),
  CONSTRAINT `fk_bebas_payment1`
    FOREIGN KEY (`payment_payment_id`)
    REFERENCES `payment` (`payment_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_bebas_student1`
    FOREIGN KEY (`student_student_id`)
    REFERENCES `student` (`student_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `month`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `month` (
  `month_id` INT NOT NULL AUTO_INCREMENT,
  `month_name` VARCHAR(45) NULL,
  PRIMARY KEY (`month_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bulan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bulan` (
  `bulan_id` INT NOT NULL AUTO_INCREMENT,
  `student_student_id` INT NULL,
  `payment_payment_id` INT NULL,
  `month_month_id` INT NULL,
  `bulan_bill` DECIMAL NULL,
  `bulan_status` TINYINT(1) NULL DEFAULT 0,
  `bulan_number_pay` VARCHAR(100) NULL,
  `bulan_date_pay` DATE NULL,
  `user_user_id` INT(11) NULL,
  `bulan_input_date` TIMESTAMP NULL,
  `bulan_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`bulan_id`),
  INDEX `fk_bulan_payment1_idx` (`payment_payment_id` ASC),
  INDEX `fk_bulan_month1_idx` (`month_month_id` ASC),
  INDEX `fk_bulan_student1_idx` (`student_student_id` ASC),
  INDEX `fk_bulan_users1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_bulan_payment1`
    FOREIGN KEY (`payment_payment_id`)
    REFERENCES `payment` (`payment_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_bulan_month1`
    FOREIGN KEY (`month_month_id`)
    REFERENCES `month` (`month_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bulan_student1`
    FOREIGN KEY (`student_student_id`)
    REFERENCES `student` (`student_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_bulan_users1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bebas_pay`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bebas_pay` (
  `bebas_pay_id` INT NOT NULL AUTO_INCREMENT,
  `bebas_bebas_id` INT NULL,
  `bebas_pay_number` VARCHAR(100) NULL,
  `bebas_pay_bill` DECIMAL NULL,
  `bebas_pay_desc` VARCHAR(100) NULL,
  `user_user_id` INT(11) NULL,
  `bebas_pay_input_date` DATE NULL,
  `bebas_pay_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`bebas_pay_id`),
  INDEX `fk_bebas_pay_bebas1_idx` (`bebas_bebas_id` ASC),
  INDEX `fk_bebas_pay_users1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_bebas_pay_bebas1`
    FOREIGN KEY (`bebas_bebas_id`)
    REFERENCES `bebas` (`bebas_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_bebas_pay_users1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `setting`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `setting` (
  `setting_id` INT NOT NULL AUTO_INCREMENT,
  `setting_name` VARCHAR(255) NULL,
  `setting_value` TEXT NULL,
  `setting_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`setting_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kredit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kredit` (
  `kredit_id` INT NOT NULL AUTO_INCREMENT,
  `kredit_date` DATE NULL,
  `kredit_desc` VARCHAR(100) NULL,
  `kredit_value` DECIMAL NULL,
  `user_user_id` INT(11) NULL,
  `kredit_input_date` TIMESTAMP NULL,
  `kredit_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`kredit_id`),
  INDEX `fk_jurnal_kredit_users1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_jurnal_kredit_users1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `debit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `debit` (
  `debit_id` INT NOT NULL AUTO_INCREMENT,
  `debit_date` DATE NULL,
  `debit_desc` VARCHAR(100) NULL,
  `debit_value` DECIMAL NULL,
  `user_user_id` INT(11) NULL,
  `debit_input_date` TIMESTAMP NULL,
  `debit_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`debit_id`),
  INDEX `fk_jurnal_debit_users1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_jurnal_debit_users1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `holiday`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `holiday` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `year` YEAR NULL,
  `date` DATE NULL,
  `info` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `letter`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `letter` (
  `letter_id` INT NOT NULL AUTO_INCREMENT,
  `letter_number` VARCHAR(100) NULL,
  `letter_month` INT NULL,
  `letter_year` YEAR NULL,
  PRIMARY KEY (`letter_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `information`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `information` (
  `information_id` INT NOT NULL AUTO_INCREMENT,
  `information_title` VARCHAR(100) NULL,
  `information_desc` TEXT NULL,
  `information_img` VARCHAR(255) NULL,
  `information_publish` TINYINT(1) NULL DEFAULT 0,
  `user_user_id` INT(11) NULL,
  `information_input_date` TIMESTAMP NULL,
  `information_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`information_id`),
  INDEX `fk_information_users1_idx` (`user_user_id` ASC),
  CONSTRAINT `fk_information_users1`
    FOREIGN KEY (`user_user_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `log_trx`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `log_trx` (
  `log_trx_id` INT NOT NULL AUTO_INCREMENT,
  `student_student_id` INT NULL,
  `bulan_bulan_id` INT NULL,
  `bebas_pay_bebas_pay_id` INT NULL,
  `log_trx_input_date` TIMESTAMP NULL,
  `log_trx_last_update` TIMESTAMP NULL,
  PRIMARY KEY (`log_trx_id`),
  INDEX `fk_log_trx_bebas_pay1_idx` (`bebas_pay_bebas_pay_id` ASC),
  INDEX `fk_log_trx_bulan1_idx` (`bulan_bulan_id` ASC),
  INDEX `fk_log_trx_student1_idx` (`student_student_id` ASC),
  CONSTRAINT `fk_log_trx_bebas_pay1`
    FOREIGN KEY (`bebas_pay_bebas_pay_id`)
    REFERENCES `bebas_pay` (`bebas_pay_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_log_trx_bulan1`
    FOREIGN KEY (`bulan_bulan_id`)
    REFERENCES `bulan` (`bulan_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `fk_log_trx_student1`
    FOREIGN KEY (`student_student_id`)
    REFERENCES `student` (`student_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
