/*
ALTER TABLE `patients` ADD `age_month` TINYINT(2) NULL AFTER `age`;
ALTER TABLE `patients` ADD `medical_history` VARCHAR(255) NULL COMMENT 'separator is comma' AFTER `note`;
ALTER TABLE `prescriptions` ADD `mh_extra` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `mh_ids`;
CREATE TABLE `appointments_online` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `age` TINYINT(3) NULL , `age_month` TINYINT(2) NULL ,  `sex` varchar(1) NULL , `slot_no` TINYINT(2) NOT NULL , `status` TINYINT(1) NOT NULL DEFAULT '2' , `cdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE `query` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT , `text` TEXT NOT NULL , `execute_for` VARCHAR(32) NOT NULL , `cdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `payment_sections` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `status` TINYINT(1) NOT NULL DEFAULT '1' , `is_sync` tinyint(1) UNSIGNED NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `payment_sections` (`id`, `name`, `status`, `is_sync`) VALUES (1, 'Advice', '1', '0'), (2, 'Treament Service', '1', '0'), (3, 'X-ray', '1', '0');
CREATE TABLE `holidaywork_setup` ( `saturday` TINYINT(1) NULL , `sunday` TINYINT(1) NULL , `monday` TINYINT(1) NULL , `tuesday` TINYINT(1) NULL , `wednesday` TINYINT(1) NULL , `thursday` TINYINT(1) NULL , `friday` TINYINT(1) NULL , `notice` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL ) ENGINE = InnoDB;
CREATE TABLE `holidaywork_holiday` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `holiday_start` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP , `holiday_end` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP , `cause` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `status` TINYINT(1) NOT NULL DEFAULT '1' , `is_sync` tinyint(1) UNSIGNED NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE `holidaywork_log` ( `holiday` DATE NULL , `holiday_id` int(11) NULL , `workday` DATE NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `payment_history` ADD COLUMN `work_id` INT(11) NULL AFTER `section`;
ALTER TABLE `payment_history` ADD COLUMN `film_use` TINYINT(3) NULL AFTER `work_id`;

CREATE TABLE `prosthetic_works` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `patient_id` INT(11) NULL , `lab_id` TINYINT(3) NULL , `work_id` TINYINT(3) NULL , `quantity` TINYINT(3) NULL , `shade_id` TINYINT(3) NULL , `tooth_no` VARCHAR(50) NULL , `instruction` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `work_status` TINYINT(3) NULL DEFAULT '1', `order_type` TINYINT(3) NULL DEFAULT '1', `cdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `prosthetic_labs` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;

CREATE TABLE `prosthetic_works_name` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `default` TINYINT(1) NULL, `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_works_name` (`name`, `default`) VALUES 
('PFM crown', 1),
('Metal Crown', NULL), 
('Dental Bridge', NULL),
('Acrylic Partial Denture', NULL),
('Acrylic Complete Denture (Upper)', NULL),
('Acrylic Complete Denture (Lower)', NULL),
('Flexible Denture', NULL),
('Orthodontic appliance (Upper)', NULL),
('Orthodontic appliance (Lower)', NULL),
('Golden Crown', NULL),
('Post core', NULL),
('Full ceramic crown', NULL);

CREATE TABLE `prosthetic_work_status` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_work_status` (`id`, `name`) VALUES 
(1, 'Waiting for sending'),
(2, 'Sent Order'),
(3, 'Received'),
(4, 'Success'),
(5, 'Developing'),
(6, 'Remake'),
(7, 'Waiting for sending : Developing'),
(8, 'Waiting for sending : Remake');

CREATE TABLE `prosthetic_shade` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_shade` (`id`, `name`, `is_sync`) VALUES 
(1, 'A-1', 0),
(2, 'A-2', 0), 
(3, 'A-3', 0),
(4, 'A-3.5', 0),
(5, 'A-4', 0),
(6, 'B-1', 0),
(7, 'B-2', 0),
(8, 'B-3', 0),
(9, 'B-4', 0),
(10, 'C-1', 0),
(11, 'C-2', 0),
(12, 'C-3', 0),
(13, 'C-4', 0),
(14, 'D-2', 0),
(15, 'D-3', 0),
(16, 'D-4', 0); 

CREATE TABLE `prosthetic_problems` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_problems` (`id`, `name`, `is_sync`) VALUES
(1, 'Loose fitting', 0),
(2, 'Color not matched', 0),
(3, 'High Bite', 0),
(4, 'Low bite', 0),
(5, 'Porcelain Cracked', 0),
(6, 'Anatomy is not good', 0),
(7, 'Short margin', 0),
(8, 'Rough margin causing ulceration', 0),
(9, 'Broken bridge connector', 0);

CREATE TABLE `prosthetic_impressions` ( `id` TINYINT(1) NOT NULL AUTO_INCREMENT , `name` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_impressions` ( `id`, `name`, `is_sync`) VALUES 
(1, 'Yes' , 0),
(2, 'Not', 0); 

CREATE TABLE `prosthetic_calling_status` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_calling_status` (`id`, `name`, `is_sync`) VALUES 
(1, 'Called and Appointment made', 0),
(2, 'Contacted but no appointment', 0),
(3, 'Not Received', 0);

CREATE TABLE `prosthetic_order_type` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_order_type` (`id`, `name`, `is_sync`) VALUES 
(1, 'New', 0),
(2, 'Developing', 0), 
(3, 'Remake', 0); 

CREATE TABLE `prosthetic_insertion_status` ( `id` TINYINT(3) NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prosthetic_insertion_status` (`id`, `name`, `is_sync`) VALUES 
(1, 'Inserted Successfully', 0),
(2, 'Try in', 0), 
(3, 'Try in & Re-Impression & Model', 0);

CREATE TABLE `prosthetic_orders` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `works_id` INT(11) NULL , `order_type_id` TINYINT(3) NULL, `courier_date` DATE NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `prosthetic_orders_received` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `works_id` INT(11) NULL , `labs_id` TINYINT(3) NULL , `calling_id` TINYINT(3) NULL , `date` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP , `is_sync` tinyint(1) UNSIGNED NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `prosthetic_insertions` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `works_id` INT(11) NULL , `insertion_status` TINYINT(3) NULL , `date` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `prosthetic_insertion_remake` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `works_id` INT(11) NULL , `instruction` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `date` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP , `problem_id` TINYINT(3) NULL , `problem_text` VARCHAR(100) NULL , `impress_id` TINYINT(3) NULL , `is_sync` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `settings`(`name`, `value`, `group`, `section`, `status`, `is_sync`) VALUES 
('isMaintenance', '0', 'utility', 'maintenance', 1, 0),
('files', '0', 'utility', 'maintenance', 1, 0);

ALTER TABLE `prosthetic_works` ADD COLUMN `bill` tinyint(3) AFTER `work_status`;
ALTER TABLE `prosthetic_works` ADD COLUMN `insertion_id` int(11) NULL AFTER `work_status`;
ALTER TABLE `appointments_online` ADD COLUMN `mobile` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER `slot_no`;

ALTER TABLE `appointments_slots` ADD COLUMN `isOnline` tinyint(1) DEFAULT 0 AFTER `slot_time`;
UPDATE `appointments_slots` SET `isOnline` = '1' WHERE `appointments_slots`.`id` IN (2,3,6,9,12,15);

ALTER TABLE `treatment_plan` ADD COLUMN `note` text CHARACTER SET utf8 COLLATE utf8_general_ci AFTER `total_service`;
ALTER TABLE `treatment_plan` ADD `prescription_id` INT(11) NULL AFTER `note`;
ALTER TABLE `patients` ADD `last_open` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER `last_update`;
ALTER TABLE `prescriptions` ADD `xray` VARCHAR(100) NULL AFTER `advice_ids`; 
*/
/*Update on November 2021*/
-- ALTER TABLE `payment_history` ADD `status` TINYINT(1) NOT NULL DEFAULT '0' AFTER `isExpense`;

/*Update on 01 July 2022
ALTER TABLE `holidaywork_holiday` CHANGE `holiday_start` `holiday_start` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `holidaywork_holiday` CHANGE `holiday_end` `holiday_end` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `prosthetic_insertions` CHANGE `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `prosthetic_insertion_remake` CHANGE `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `prosthetic_orders_received` CHANGE `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
*/

/*Update on October 2022*/
ALTER TABLE `drugs_available_type` CHANGE `name_small` `name_small` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
INSERT INTO `drugs_available_type` (`id`, `name`, `name_small`, `is_sync`) VALUES 
(NULL, 'Injection', 'Injection.', '0'),
(NULL, 'Oral Paste', 'Oral Paste.', '0'),
(NULL, 'Oral Gel', 'Oral Gel.', '0'),
(NULL, 'Oral Solution', 'Oral Solution.', '0'),
(NULL, 'Ointment', 'Ointment.', '0'),
(NULL, 'Spray', 'Spray.', '0'),
(NULL, 'Toothpaste', 'Toothpaste.', '0');
CREATE TABLE `fdc`.`prescriptions_ready_text` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , `parent_id` INT(11) NULL , `section_id` TINYINT(1) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO `prescriptions_ready_text` (`id`, `name`, `parent_id`, `section_id`) VALUES 
('1', 'Caries', NULL, '1')
,('2', 'Class-1', '1', '1')
,('3', 'Class-2', '1', '1')
,('4', 'Class-3', '1', '1')
,('5', 'Class-4', '1', '1')
,('6', 'Class-5', '1', '1')
,('7', 'Class-6', '1', '1')
,('8', 'Incipient', '1', '1')
,('9', 'Interproximal Nursing Bottle caries', '1', '1')
,('10', 'Attrition', NULL, '1')
,('11', 'Abrasion-Erosion', NULL, '1')
,('12', 'Buccal-Cervical abrasion', NULL, '1')
,('13', 'Broken Down Root(BDR)', NULL, '1')
,('14', 'Retained Root stump', NULL, '1')
,('15', 'Retained', NULL, '1')
,('16', 'Erupted', NULL, '1')
,('17', 'Normally', '16', '1')
,('18', 'Lingually', '16', '1')
,('19', 'Labially', '16', '1')
,('20', 'Buccally', '16', '1')
,('21', 'Crown fracture', NULL, '1')
,('22', 'Vertical', '21', '1')
,('23', 'Horizontal', '21', '1')
,('24', 'Oblique', '21', '1')
,('25', 'Crown root fracture', NULL, '1')
,('26', 'Broken crown', NULL, '1')
,('27', 'Incisal', '26', '1')
,('28', 'Buccal', '26', '1')
,('29', 'Lingual', '26', '1')
,('30', 'Palatal', '26', '1')
,('31', 'Cavitations', NULL, '1')
,('32', 'Prosthetic crown/Bridge dislodgement from', NULL, '1')
,('33', 'Opened access cavity', NULL, '1')
,('34', 'Mesio Dense', NULL, '1')
,('35', 'Extra tooth', NULL, '1')
,('36', 'Supernumerary', NULL, '1')
,('37', 'TF on', NULL, '1')
,('38', 'Mobility', NULL, '1')
,('39', 'Grade-1', '38', '1')
,('40', 'Grade-2', '38', '1')
,('41', 'Grade-3', '38', '1')
,('42', 'Swelling', NULL, '1')
,('43', 'Buccal', '42', '1')
,('44', 'Lingual', '42', '1')
,('45', 'Palatal', '42', '1')
,('46', 'Labial', '42', '1')
,('47', 'Sinus', NULL, '1')
,('48', 'Buccal', '47', '1')
,('49', 'Labial', '47', '1')
,('50', 'Lingual', '47', '1')
,('51', 'Palatal', '47', '1')
,('52', 'Calculus', NULL, '1')
,('53', '+', '52', '1')
,('54', '++', '52', '1')
,('55', '+++', '52', '1')
,('56', 'BOP', NULL, '1')
,('57', '+', '56', '1')
,('58', '++', '56', '1')
,('59', '+++', '56', '1')
,('60', 'Stain', NULL, '1')
,('61', '+', '60', '1')
,('62', '++', '60', '1')
,('63', '+++', '60', '1')
,('64', 'PD pocket', NULL, '1')
,('65', 'Midline Diastema', NULL, '1')
,('66', 'Upper', '65', '1')
,('67', 'Lower', '65', '1')
,('68', 'Cross bite', NULL, '1')
,('69', 'Anterior', '68', '1')
,('70', 'Posterior', '68', '1')
,('71', 'Malocclusion', NULL, '1')
,('72', 'Class-1', '71', '1')
,('73', 'Class-2', '71', '1')
,('74', 'Class-3', '71', '1')
,('75', 'Anterior Open bite', NULL, '1')
,('76', 'Over jet', NULL, '1')
,('77', 'Normal', '76', '1')
,('78', 'Increased', '76', '1')
,('79', 'Reduced', '76', '1')
,('80', 'Overbite', NULL, '1')
,('81', 'Normal', '80', '1')
,('82', 'Increased', '80', '1')
,('83', 'Reduced', '80', '1')
,('84', 'Crowding', NULL, '1')
,('85', 'Upper', '84', '1')
,('86', 'Lower', '84', '1')
,('87', 'Spacing of teeth', NULL, '1')
,('88', 'Generalized', '87', '1')
,('89', 'Upper anterior', '87', '1')
,('90', 'Lower anterior', '87', '1')
,('91', 'PFM crown', NULL, '1')
,('92', 'Missing', NULL, '1')
,('93', 'Bridge on', NULL, '1')
,('94', 'Filling on', NULL, '2')
,('95', 'RCT on', NULL, '2')
,('96', 'Extraction of', NULL, '2')
,('97', 'Crown insertion', NULL, '2')
,('98', 'Bridge on', NULL, '2')
,('99', 'Tube-well Handle trauma', NULL, '2')
,('100', 'Road Traffic Accident injury', NULL, '2')
,('101', 'Pulpal', NULL, '3')
,('102', 'Reversible Pulpitis', '101', '3')
,('103', 'Symptomatic Irreversible pulpitis', '101', '3')
,('104', 'Asymptomatic Irreversible pulpitis', '101', '3')
,('105', 'Pulp Necrosis', '101', '3')
,('106', 'Previously treated pulp', '101', '3')
,('107', 'Apical', NULL, '3')
,('108', 'Symptomatic apical Periodontists', '107', '3')
,('109', 'Asymptomatic Apical periodontitis', '107', '3')
,('110', 'Acute Apical Abscess', '107', '3')
,('111', 'Chronic Apical Abscess', '107', '3')
,('112', 'Periodontal', NULL, '3')
,('113', 'Deep PD pocket', '112', '3')
,('114', 'Gingival recession', '112', '3')
,('115', 'Gingivitis', '112', '3')
,('116', 'Periodontitis', '112', '3')
,('117', 'Impacted', NULL, '3')
,('118', 'Vertical', '117', '3')
,('119', 'Mesioangular', '117', '3')
,('120', 'Horizontal', '117', '3')
,('121', 'Distoangular', '117', '3')
,('122', 'Buccal-Lingual', '117', '3')
,('123', 'Alveolar osteitis', NULL, '3')
,('124', 'Fracture', NULL, '3')
,('125', 'Pathology', NULL, '3')
,('126', 'Leukoedema', '125', '3')
,('127', 'Geographic Tongue', '125', '3')
,('128', 'Fissured Tongue', '125', '3')
,('129', 'Hemangioma', '125', '3')
,('130', 'Tori', '125', '3')
,('131', 'Globulomaxillary lesion', '125', '3')
,('132', 'Traumatic bone cyst', '125', '3')
,('133', 'Linea alba', '125', '3')
,('134', 'Traumatic Ulcer', '125', '3')
,('135', 'Chemical Burn', '125', '3')
,('136', 'Nicotine Stomatitis', '125', '3')
,('137', 'Amalgam tatto', '125', '3')
,('138', 'Smoking Associated melanosis', '125', '3')
,('139', 'Herpis simplex virus', '125', '3')
,('140', 'Varicella zoster', '125', '3')
,('141', 'Oral Lichen Planus', '125', '3')
,('142', 'Oral Leukoplakia', '125', '3')
,('143', 'Oral Candidiasis', '125', '3')
;

ALTER TABLE `appointments` ADD `wt_estimate` TINYINT NOT NULL DEFAULT '30' AFTER `cdate`, ADD `wt_start` TIMESTAMP NULL AFTER `wt_estimate`, ADD `wt_end` TIMESTAMP NULL AFTER `wt_start`, ADD `wt_status` TINYINT NOT NULL DEFAULT '1' COMMENT '0-Inactive 1-Active 2-Work 3-Done' AFTER `wt_end`;