

-- 30-09-2023

ALTER TABLE m_unit ADD COLUMN working_hours TEXT NOT NULL COMMENT "the default unit workgin hours in CSV"


-- the entity that stores the changed working hours of the unit if any special occasion day is happened.  
CREATE TABLE m_unit_day_hours (
	id INT AUTO_INCREMENT PRIMARY KEY,
	unit_id INT NOT NULL,
	`date` DATE NOT NULL COMMENT "the effect date on which the unit runs different to default working hours(special days)",
	working_hours TEXT NOT NULL COMMENT "the unit workgin hours in CSV in 24 hour format",
	created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
	updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

-- the entity that stores the default breaks for the unit
CREATE TABLE m_unit_break (
	id INT AUTO_INCREMENT PRIMARY KEY,
	unit_id INT NOT NULL,
	break_time_from VARCHAR(5) NOT NULL COMMENT "the break time start time in 24 hour format",
	break_minutes TINYINT NOT NULL COMMENT "the total break minutes",
	break_time_to VARCHAR(5) NOT NULL COMMENT "the break time end time in 24 hour format.Calculated and to be inserted based on break minutes",
	break_desc VARCHAR(30) NOT NULL,
	created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
	updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

-- the entity that stores the changed breaks for any occasional day 
CREATE TABLE m_unit_day_break (
	id INT AUTO_INCREMENT PRIMARY KEY,
	unit_id INT NOT NULL,
	`date` DATE NOT NULL COMMENT "the effected date on which the unit runs different to default breaks(special days)",
	break_time_from VARCHAR(5) NOT NULL COMMENT "the break time start time in 24 hour format 00:00",
	break_minutes TINYINT NOT NULL COMMENT "the total break minutes",
	break_time_to VARCHAR(5) NOT NULL COMMENT "the break time end time in 24 hour format.Calculated and to be inserted based on break minutes 00:00",
	break_desc VARCHAR(30) NOT NULL,
	created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
	updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

ALTER TABLE `t_migration_log` ADD INDEX U_L_D_H(unit, line_id, migration_date, HOUR)


ALTER TABLE `t_line_actual_day_track` ADD COLUMN actual_hour TINYINT AFTER HOUR; 
ALTER TABLE `t_line_down_time` ADD COLUMN actual_hour TINYINT AFTER HOUR; 
ALTER TABLE `t_line_efficiency_day_track` ADD COLUMN actual_hour TINYINT AFTER HOUR;
ALTER TABLE `t_operator_efficiency_day_track` ADD COLUMN actual_hour TINYINT AFTER HOUR;
ALTER TABLE m_unit ADD COLUMN actual_working_hours TEXT;
ALTER TABLE m_unit_day_hours ADD COLUMN actual_working_hours TEXT AFTER working_hours;


-------------- INDEXES -------------
ALTER TABLE `t_line_actual_day_track` ADD INDEX U_D(unit_id, plan_date);
ALTER TABLE `t_line_efficiency_day_track` ADD INDEX U_D(unit_id, plan_date);
ALTER TABLE `t_operator_efficiency_day_track` ADD INDEX U_D(unit_id, plan_date);



ALTER TABLE m_style ADD COLUMN target_efficiency DECIMAL (5, 2) DEFAULT 0;


























--------------------  GTS -----------------------------

ALTER TABLE `t_migration_log` ADD INDEX U_D_H_TB(unit_id, migration_date, HOUR, table_name);

ALTER TABLE `m_buyer` MODIFY COLUMN buyer_name VARCHAR(100) NOT NULL;
ALTER TABLE `m_buyer` MODIFY COLUMN buyer_code VARCHAR(100) NOT NULL;
ALTER TABLE `t_tracking_info` MODIFY COLUMN ref_id VARCHAR(30) NOT NULL DEFAULT '0';

