

CREATE TABLE `counter_section` (
  `counter_section_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '',
  `section_info_id` INT(10) NULL COMMENT '',
  `counter_id` INT(10) NULL COMMENT '',
  PRIMARY KEY (`counter_section_id`)  COMMENT '');


ALTER TABLE `counter_section`
ADD INDEX `fk_counter_section_1_idx` (`counter_id` ASC)  COMMENT '';
ALTER TABLE `counter_section`
ADD CONSTRAINT `fk_counter_section_1`
  FOREIGN KEY (`counter_id`)
  REFERENCES `counter_info` (`counter_info_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- 1-2-2017

CREATE TABLE `academic_year` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `academic_year` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');


INSERT INTO `academic_year` (`academic_year`) VALUES ('2012-13');
INSERT INTO `academic_year` (`academic_year`) VALUES ('2013-14');
INSERT INTO `academic_year` (`academic_year`) VALUES ('2014-15');
INSERT INTO `academic_year` (`academic_year`) VALUES ('2015-16');
INSERT INTO `academic_year` (`academic_year`) VALUES ('2016-17');



ALTER TABLE `fee_info`
ADD COLUMN `admission_type` VARCHAR(45) NULL COMMENT '' AFTER `academic_year`;

-- 6-2-2017

	ADD COLUMN `eligibility_no` VARCHAR(20) NULL COMMENT '' AFTER `pattern_name`,
ADD COLUMN `pattern_name` VARCHAR(45) NULL COMMENT '' AFTER `eligibility_no`
ADD COLUMN `roll_no` VARCHAR(45) NULL COMMENT '' AFTER `pattern_name`
ADD COLUMN `s_mobile_no` VARCHAR(20) NULL COMMENT '' AFTER `roll_no`,
ADD COLUMN `s_uid_no` VARCHAR(45) NULL COMMENT '' AFTER `s_mobile_no`;

ALTER TABLE `s_personal_info`
ADD COLUMN `s_taluka` VARCHAR(45) NULL COMMENT '' AFTER `s_uid_no`,
ADD COLUMN `s_district` VARCHAR(45) NULL COMMENT '' AFTER `s_taluka`;




ALTER TABLE `s_academic_info`
ADD COLUMN `s_achievements` VARCHAR(100) NULL COMMENT '' AFTER `s_cap_round`,
ADD COLUMN `s_academic_year` VARCHAR(20) NULL COMMENT '' AFTER `s_achievements`,
ADD COLUMN `s_is_gap` VARCHAR(20) NULL COMMENT '' AFTER `s_academic_year`,
ADD COLUMN `no_gap_years` VARCHAR(20) NULL COMMENT '' AFTER `s_is_gap`,
ADD COLUMN `gap_reason` VARCHAR(100) NULL COMMENT '' AFTER `no_gap_years`,
ADD COLUMN `s_repeat_year` VARCHAR(45) NULL COMMENT '' AFTER `gap_reason`;


CREATE TABLE `user` (
  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `username` VARCHAR(45) NOT NULL COMMENT '',
  `password` VARCHAR(255) NOT NULL COMMENT '',
  `email` VARCHAR(100) NOT NULL COMMENT '',
  `forgotten_password_code` VARCHAR(45) NULL COMMENT '',
  `created_on` INT(11) NULL COMMENT '',
  `active` TINYINT(1) UNSIGNED NULL COMMENT '',
  `firstname` VARCHAR(50) NULL COMMENT '',
  `lastname` VARCHAR(50) NULL COMMENT '',
  `phonenumber` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`user_id`)  COMMENT '');



CREATE TABLE `groups` (
  `group_id` INT(11) NOT NULL COMMENT '',
  `name` VARCHAR(45) NULL COMMENT '',
  `description` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`group_id`)  COMMENT '');

ALTER TABLE `groups`
CHANGE COLUMN `group_id` `group_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '' ;



CREATE TABLE `user_groups` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '',
  `group_id` INT(11) UNSIGNED NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_user_groups_1_idx` (`user_id` ASC)  COMMENT '',
  CONSTRAINT `fk_user_groups_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


ALTER TABLE `user_groups`
ADD INDEX `index3` (`group_id` ASC)  COMMENT '';

ALTER TABLE `groups`
CHANGE COLUMN `group_id` `group_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '' ;


ALTER TABLE `user_groups`
ADD CONSTRAINT `fk_user_groups_2`
  FOREIGN KEY (`group_id`)
  REFERENCES `groups` (`group_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


ALTER TABLE `user`
ADD COLUMN `user_role` VARCHAR(45) NULL COMMENT '' ;

INSERT INTO `groups` (`name`, `description`) VALUES ('Administrator', 'Admin');
INSERT INTO `groups` (`name`, `description`) VALUES ('Student', 'Student');
INSERT INTO `groups` (`name`, `description`) VALUES ('Teacher', 'Teacher');

INSERT INTO `user` (`user_id`, `username`, `password`, `email`, `forgotten_password_code`, `created_on`, `active`, `firstname`, `lastname`, `phonenumber`,`user_role`) VALUES ('1', 'admin', 'admin123', 'admin@gmail.com', '', '08022017', '1', 'Admin', '', '8888888898','Administrator');


INSERT INTO `user_groups` (`user_id`, `group_id`) VALUES ('1', '1');



-- made by arvind kadam

ALTER TABLE `receipt_info` 

 ADD COLUMN `s_standard` VARCHAR(45) AFTER `exam_status`,
 ADD COLUMN `bus_stop_name` VARCHAR(45) AFTER `s_standard`,
 ADD COLUMN `stop_from` VARCHAR(45) AFTER `bus_stop_name`,
  ADD COLUMN `stop_to` VARCHAR(45) AFTER `stop_from`,
 ADD COLUMN `cash_amount` VARCHAR(45) AFTER `stop_to`,
 ADD COLUMN `trf_no` VARCHAR(45) AFTER `cash_amount`,
 ADD COLUMN `trf_narration` VARCHAR(45) AFTER `trf_no`,
 ADD COLUMN `rtgs_no` VARCHAR(45) AFTER `trf_narration`,
 ADD COLUMN `rtgs_narration` VARCHAR(45) AFTER `rtgs_no`;



ALTER TABLE `receipt_info` ADD COLUMN `refund_description` VARCHAR(350) AFTER `rtgs_narration`;


CREATE TABLE  `religion` (
  `religion_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `religion_name` varchar(45) NOT NULL,
  PRIMARY KEY (`religion_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


ALTER TABLE `s_fee_history`
ADD COLUMN `s_admission_type` VARCHAR(45) NULL COMMENT '';


-- Making default Not Applicable // for old records
SET SQL_SAFE_UPDATES = 0;
update s_personal_info set s_branch_type='Not Applicable' where s_branch_type='Not Selected';
SET SQL_SAFE_UPDATES = 1;

-- // for old records

SET SQL_SAFE_UPDATES = 0;
update s_fee_history set s_admission_type='Regular Admission' where s_admission_type is null;
SET SQL_SAFE_UPDATES = 1;

-- // for old records

SET SQL_SAFE_UPDATES = 0;
update s_fee_history set s_admission_type='Regular Admission' where s_admission_type is null;
SET SQL_SAFE_UPDATES = 1;

-- // for old records

SET SQL_SAFE_UPDATES = 0;
update fee_details set branch ='Not Applicable' where branch is null;
SET SQL_SAFE_UPDATES = 1;

SET SQL_SAFE_UPDATES = 0;
update s_academic_info set s_is_repeat_year='No' where s_is_repeat_year is null;
SET SQL_SAFE_UPDATES = 1;



SET SQL_SAFE_UPDATES =0;
UPDATE fee_info SET  `admission_type` =  'Regular Admission' WHERE  `admission_type` IS NULL ;
SET SQL_SAFE_UPDATES =1;

-- password for student
ALTER TABLE `s_personal_info`
ADD COLUMN `s_password` VARCHAR(255) NULL COMMENT '';
