ALTER TABLE `users` CHANGE `first_name` `first_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, CHANGE `last_name` `last_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, CHANGE `email_verified_at` `email_verified_at` TIMESTAMP NULL DEFAULT NULL, CHANGE `role_id` `role_id` BIGINT(12) NULL, CHANGE `latitude` `latitude` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, CHANGE `longitude` `longitude` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;


ALTER TABLE `users` ADD `dob` DATE NOT NULL AFTER `register_type`, ADD `gender` ENUM('male','female','other') NOT NULL AFTER `dob`, ADD `address_1` VARCHAR(120) NOT NULL AFTER `gender`, ADD `address_2` VARCHAR(120) NOT NULL AFTER `address_1`, ADD `city` VARCHAR(60) NOT NULL AFTER `address_2`, ADD `state` INT(60) NOT NULL AFTER `city`, ADD `zipcode` VARCHAR(60) NOT NULL AFTER `state`, ADD `country` VARCHAR(60) NOT NULL AFTER `zipcode`, ADD `code` BIGINT(11) NOT NULL AFTER `country`, ADD `music_academy` BIGINT(11) NULL AFTER `code`, ADD `teacher_name` BIGINT(11) NULL AFTER `music_academy`;


ALTER TABLE `users` CHANGE `address_2` `address_2` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;

ALTER TABLE `users` CHANGE `state` `state` VARCHAR(60) NOT NULL;

---------------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE `users` CHANGE `role_type` `role_type` ENUM('Admin','Subadmin','User','Academy','Teacher') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-----------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `academy` (
  `id` int(11) NOT NULL,
  `academy_name` varchar(20) NOT NULL,
  `address_1` varchar(255) NOT NULL,
  `address_2` varchar(255) NOT NULL,
  `city` varchar(25) NOT NULL,
  `state` varchar(25) NOT NULL,
  `country` varchar(25) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `exam_evaluation` (
  `id` int(11) NOT NULL,
  `exam_shedule_id` bigint(20) NOT NULL,
  `exam_id` bigint(20) DEFAULT NULL,
  `note` varchar(500) NOT NULL,
  `marks` double(10,2) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) NOT NULL,
  `updated_at` datetime NOT NULL,
  `updated_by` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `exam_evaluation_grade` (
  `id` int(11) NOT NULL,
  `exam_shedule_id` bigint(20) NOT NULL,
  `final_comment` varchar(500) NOT NULL,
  `grade` double(10,2) NOT NULL,
  `marks` double(10,2) NOT NULL,
  `status` enum('Pass','Fail') NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) NOT NULL,
  `updated_at` datetime NOT NULL,
  `updated_by` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `exam_master` (
  `id` int(11) NOT NULL,
  `exam_level` varchar(100) NOT NULL,
  `category` varchar(100) NOT NULL,
  `category_total_marks` double(10,2) NOT NULL,
  `parent_exam_id` bigint(20) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `exam_scheduler` (
  `id` int(11) NOT NULL,
  `slot_id` bigint(20) NOT NULL,
  `student_id` bigint(20) DEFAULT NULL,
  `examiner_id` bigint(20) NOT NULL,
  `exam_master_id` bigint(20) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `slots` (
  `id` int(11) NOT NULL,
  `slot_date` date NOT NULL,
  `slot_time_start` varchar(120) NOT NULL,
  `slot_time_end` varchar(120) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `academy`
  ADD PRIMARY KEY (`id`);
  
 ALTER TABLE `exam_evaluation`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `exam_evaluation_grade`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `exam_master`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `exam_scheduler`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `slots`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `academy`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `exam_evaluation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `exam_evaluation_grade`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `exam_master`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `exam_scheduler`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `slots`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
  
  --------------------------------------------------------------------------------------------------------------

ALTER TABLE `exam_master` ADD `genre` VARCHAR(100) NULL AFTER `category`;

ALTER TABLE `exam_evaluation` CHANGE `status` `status` ENUM('Pending','Inprogress','Complete') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'Pending';

ALTER TABLE `exam_evaluation` ADD `exam_started_at` DATETIME NULL DEFAULT NULL AFTER `updated_by`, ADD `exam_ended_at` DATETIME NULL DEFAULT NULL AFTER `exam_started_at`;

ALTER TABLE `exam_evaluation_grade` CHANGE `final_comment` `final_comment` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

CREATE TABLE `swarkul`.`configurations` ( `id` INT NOT NULL AUTO_INCREMENT , `configuration_name` VARCHAR(100) NULL , `configuration_value` VARCHAR(100) NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `configurations` (`id`, `configuration_name`, `configuration_value`) VALUES (NULL, 'slot_time', '45');

INSERT INTO `configurations` (`id`, `configuration_name`, `configuration_value`) VALUES (NULL, 'evaluation_button_enable_from', '15');

INSERT INTO `configurations` (`id`, `configuration_name`, `configuration_value`) VALUES (NULL, 'evaluation_button_enable_to', '15');



---------------------------------------------------------------------------------------------------------------------

ALTER TABLE `exam_evaluation` DROP `status`;"

ALTER TABLE `exam_scheduler` CHANGE `status` `status` ENUM('Pending','Inprogress','Complete') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'Pending';

--------------------------
INSERT INTO `configurations` (`id`, `configuration_name`, `configuration_value`) VALUES (NULL, 'evaluation_grading', '35:"Fail",60:"Second Class",80:"First Class",100:"Distinction"');

----------------------------------------------------------------------------------------------------

ALTER TABLE `exam_evaluation_grade` CHANGE `status` `status` VARCHAR(220) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

UPDATE `configurations` SET `configuration_value` = '{\"35\":\"Fail\",\"60\":\"Second Class\",\"80\":\"First Class\",\"100\":\"Distinction\"}' WHERE `configurations`.`id` = 4;

-------------------------------------------------------------------------------------

ALTER TABLE `exam_scheduler` CHANGE `examiner_id` `examiner_id` BIGINT(20) NULL;

------------------------------

ALTER TABLE `users` CHANGE `role_type` `role_type` ENUM('Admin','Student','Teacher','Examiner') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `users` DROP `first_name`, DROP `last_name`;
ALTER TABLE `users` ADD `is_academy` BOOLEAN NULL DEFAULT FALSE AFTER `country`, ADD `music_stream` VARCHAR(250) NULL DEFAULT NULL AFTER `is_academy`;

-------------------------------------------------------------------------------------------------------------------

ALTER TABLE `users` ADD `unique_code` VARCHAR(120) NULL AFTER `code`;

-------------------------------------------------------------

ALTER TABLE `users` CHANGE `teacher_name` `teacher_name` VARCHAR(120) NULL DEFAULT NULL;

ALTER TABLE `users` CHANGE `music_academy` `music_academy` VARCHAR(120) NULL DEFAULT NULL;

ALTER TABLE `users` CHANGE `role_type` `role_type` ENUM('Admin','Student','Academy','Examiner') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

----------- Table exminer_capabilities ----------
---- examiner_id from users table of type examiner -----
---- genre is a normal string of different type of genre, this will be manually added -----
---- level is a comma seperate string of different levels -----

CREATE TABLE `examiner_capabilities` ( `id` INT NOT NULL AUTO_INCREMENT , `examiner_id` INT NOT NULL , `genre` VARCHAR(20) NOT NULL , `level` VARCHAR(100) NOT NULL , `type` ENUM('Elite','Regular') NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
ALTER TABLE `exam_scheduler` ADD `student_booked_at` TIMESTAMP NULL AFTER `student_id`;
ALTER TABLE `exam_scheduler` ADD `payment_status` ENUM('Failed','Success') NULL AFTER `student_booked_at`;
ALTER TABLE `exam_scheduler` ADD `academy_name` VARCHAR(100) NULL AFTER `payment_status`;

CREATE TABLE `fee_structure` ( `id` INT NOT NULL AUTO_INCREMENT , `level` VARCHAR(20) NOT NULL , `inr_currency` DOUBLE NOT NULL , `usd_currency` DOUBLE NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('1', 'Pre-grade', '1000', '50');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('2', 'Level 1', '1250', '60');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('3', 'Level 2', '1250', '60');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('4', 'Level 3', '1500', '75');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('5', 'Level 4', '2000', '100');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('6', 'Level 5', '2500', '125');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('7', 'Level 6', '3000', '125');
INSERT INTO `fee_structure` (`id`, `level`, `inr_currency`, `usd_currency`) VALUES ('8', 'Level 7', '4000', '150');

ALTER TABLE `academy` CHANGE `academy_name` `academy_name` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `academy` CHANGE `address_2` `address_2` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `academy` CHANGE `created_at` `created_at` DATETIME NULL;
ALTER TABLE `academy` CHANGE `updated_at` `updated_at` DATETIME NULL;

INSERT INTO `exam_master` (`id`, `exam_level`, `category`, `genre`, `category_total_marks`, `parent_exam_id`, `status`, `created_at`, `created_by`) VALUES
(2, 'Level 1', 'Vocal', 'Vocal', 200.00, 0, 'Active', '2020-12-12 22:21:01', 1),
(4, 'Level 1', 'Western', 'Western', 100.00, 2, 'Active', '2020-12-12 00:00:00', 1),
(5, 'Level 1', 'Carnatic', 'Carnatic', 100.00, 2, 'Active', '2020-12-12 22:24:26', 1),
(6, 'Level 1', 'Swaram', 'Swaram', 100.00, 4, 'Active', '2020-12-12 22:24:26', 1),
(7, 'Level 1', 'Thalam', 'Thalam', 100.00, 5, 'Active', '2020-12-12 22:24:26', 1),
(8, 'Level 2', 'Vocal', 'Vocal', 200.00, 0, 'Active', '2020-12-12 22:21:01', 1);

INSERT INTO `academy` (`id`, `academy_name`, `address_1`, `address_2`, `city`, `state`, `country`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'International Music ', 'Test Address', '', 'Bangalore', 'Karnataka', 'India', '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL),
(2, 'The Carnatic School', 'Test Address 1', NULL, 'Chennai', 'Tamil Nadu', 'India', NULL, NULL, NULL);


---------------------------------------------------------------------------------------------------------------------------


ALTER TABLE `academy` ADD `code` VARCHAR(12) NULL AFTER `country`;


CREATE TABLE `country` (
  `id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `country`
  ADD PRIMARY KEY (`id`);
  
  
 ALTER TABLE `country`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;