/*
SQLyog Ultimate v12.5.1 (64 bit)
MySQL - 10.4.28-MariaDB : Database - ina
*********************************************************************
*/



CREATE DATABASE /*!32312 IF NOT EXISTS*/`ina` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `ina`;

/*Table structure for table `m_line` */


CREATE TABLE `m_line` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_id` varchar(10) NOT NULL,
  `line_number` varchar(10) NOT NULL,
  `line_desc` varchar(50) DEFAULT NULL,
  `db_host` varchar(20) NOT NULL,
  `db_username` varchar(30) NOT NULL,
  `db_password` varchar(20) NOT NULL,
  `db_port` int(11) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_operation` */


CREATE TABLE `m_operation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation_id` varchar(20) NOT NULL,
  `operation_code` varchar(20) NOT NULL,
  `operation_desc` varchar(50) DEFAULT NULL,
  `pc_key` varchar(10) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `line_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_operator` */


CREATE TABLE `m_operator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operator_id` varchar(10) NOT NULL,
  `operator_rf_id` varchar(15) NOT NULL,
  `operator_name` varchar(40) NOT NULL,
  `operator_desc` varchar(50) DEFAULT NULL,
  `line_id` varchar(10) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_reason` */


CREATE TABLE `m_reason` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reason_key` varchar(20) NOT NULL,
  `reason_code` varchar(20) NOT NULL,
  `reason_desc` varchar(40) DEFAULT NULL,
  `level` varchar(30) DEFAULT NULL,
  `unit_id` varchar(10) NOT NULL,
  `line_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_shift` */


CREATE TABLE `m_shift` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shift_id` varchar(5) NOT NULL,
  `shift_name` varchar(10) NOT NULL,
  `start` varchar(6) NOT NULL,
  `end` varchar(6) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_style` */


CREATE TABLE `m_style` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `style_id` varchar(20) NOT NULL,
  `style_name` varchar(20) DEFAULT NULL,
  `style_desc` varchar(40) DEFAULT NULL,
  `collection` varchar(40) DEFAULT NULL,
  `stopm_key` varchar(50) DEFAULT NULL,
  `unit_id` varchar(10) NOT NULL,
  `line_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_style_operation` */


CREATE TABLE `m_style_operation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `style_id` varchar(20) NOT NULL,
  `style_name` varchar(50) DEFAULT NULL,
  `operation_id` varchar(20) NOT NULL,
  `operation_code` varchar(20) NOT NULL,
  `operation_order` smallint(6) NOT NULL,
  `operation_smv` smallint(6) NOT NULL,
  `stopm_key` varchar(50) DEFAULT NULL,
  `unit_id` varchar(10) NOT NULL,
  `line_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_unit` */


CREATE TABLE `m_unit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_id` varchar(5) NOT NULL,
  `unit_name` varchar(30) DEFAULT NULL,
  `working_hours` text NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_unit_break` */


CREATE TABLE `m_unit_break` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_id` varchar(5) NOT NULL,
  `break_time_from` varchar(5) NOT NULL,
  `break_time_to` varchar(5) NOT NULL,
  `break_minutes` tinyint(4) NOT NULL,
  `break_desc` varchar(30) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_unit_day_break` */


CREATE TABLE `m_unit_day_break` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_id` varchar(5) NOT NULL,
  `date` date NOT NULL,
  `break_time_from` varchar(5) NOT NULL,
  `break_time_to` varchar(5) NOT NULL,
  `break_minutes` tinyint(4) NOT NULL,
  `break_desc` varchar(30) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_unit_day_hours` */


CREATE TABLE `m_unit_day_hours` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_id` varchar(5) NOT NULL,
  `date` date NOT NULL,
  `working_hours` text NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `m_workstation` */


CREATE TABLE `m_workstation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workstation_id` varchar(20) NOT NULL,
  `workstation_desc` varchar(30) DEFAULT NULL,
  `line_id` varchar(20) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_line_actual_day_track` */


CREATE TABLE `t_line_actual_day_track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_id` varchar(20) NOT NULL,
  `style_id` varchar(20) NOT NULL,
  `operator_id` varchar(20) NOT NULL,
  `operation_id` varchar(20) NOT NULL,
  `workstation_id` varchar(20) NOT NULL,
  `plan_date` date NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `first_trans_in_hour` time NOT NULL,
  `last_trans_in_hour` time NOT NULL,
  `operation_smv` int(11) NOT NULL,
  `actual_spent_time` int(11) NOT NULL,
  `is_rework` tinyint(4) NOT NULL DEFAULT 0,
  `is_rejection` tinyint(4) NOT NULL DEFAULT 0,
  `is_last_op` tinyint(4) NOT NULL DEFAULT 0,
  `good_quantity` int(11) NOT NULL,
  `rework_raised_quantity` smallint(6) NOT NULL DEFAULT 0,
  `rejected_quantity` int(11) NOT NULL,
  `overtime` tinyint(4) NOT NULL DEFAULT 0,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_line_day_plan` */


CREATE TABLE `t_line_day_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `style_id` varchar(20) NOT NULL,
  `line_id` varchar(20) NOT NULL,
  `plan_date` date NOT NULL,
  `plan_quantity` int(11) NOT NULL,
  `buyer` varchar(30) DEFAULT NULL,
  `hour` tinyint(4) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_line_down_time` */


CREATE TABLE `t_line_down_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_id` varchar(20) NOT NULL,
  `style_id` varchar(20) NOT NULL,
  `operator_id` varchar(20) NOT NULL,
  `operation_id` varchar(20) NOT NULL,
  `operation_smv` int(11) NOT NULL,
  `workstation_id` varchar(20) NOT NULL,
  `plan_date` date NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `downtime` int(11) NOT NULL,
  `downtime_code` varchar(20) NOT NULL,
  `downtime_reason` varchar(40) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_line_efficiency_day_track` */


CREATE TABLE `t_line_efficiency_day_track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_id` varchar(20) NOT NULL,
  `style_id` varchar(20) NOT NULL,
  `actual_quantity` int(11) NOT NULL,
  `plan_quantity` int(11) NOT NULL,
  `garment_smv` int(11) NOT NULL,
  `planned_smv` decimal(10,0) NOT NULL,
  `actual_smv` decimal(10,0) NOT NULL,
  `efficiency` int(11) NOT NULL DEFAULT 0,
  `plan_date` date NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `first_trans_in_hour` time NOT NULL,
  `last_trans_in_hour` time NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_migration_log` */


CREATE TABLE `t_migration_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(50) NOT NULL,
  `migration_date` date NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `unit` varchar(10) NOT NULL,
  `line_id` varchar(10) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 0,
  `fail_reason` text 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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Table structure for table `t_operator_efficiency_day_track` */


CREATE TABLE `t_operator_efficiency_day_track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_id` varchar(20) NOT NULL,
  `workstation_id` varchar(20) NOT NULL,
  `style_id` varchar(20) NOT NULL,
  `operator_id` varchar(20) NOT NULL,
  `operation_id` varchar(20) NOT NULL,
  `plan_date` date NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `first_trans_in_hour` time NOT NULL,
  `last_trans_in_hour` time NOT NULL,
  `hour_plan_time` int(11) NOT NULL,
  `actual_plan_time` int(11) NOT NULL,
  `actual_spent_time` int(11) NOT NULL,
  `efficiency` decimal(10,0) NOT NULL,
  `is_last_op` tinyint(4) NOT NULL DEFAULT 0,
  `op_order` tinyint(4) NOT NULL,
  `unit_id` varchar(10) NOT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



/* ADDED ON  01-04-2024 for actual eff  */
CREATE TABLE `line_target_eff` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `unit_id` VARCHAR(10) NOT NULL,
  `line_id` VARCHAR(20) NOT NULL,
  `style_id` VARCHAR(20) NOT NULL,
  `plan_date` DATE NOT NULL,
  `efficiency` DECIMAL(5,2) NOT NULL,
  `is_active` TINYINT(4) NOT NULL DEFAULT 1,
  `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),
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


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