
ALTER TABLE `item_suppliers` ADD `minimum_ord_qty` DECIMAL(10.2) NOT NULL AFTER `supplier_cost_price`;
ALTER TABLE `item_location` ADD `warehouse_name` VARCHAR(255) NOT NULL AFTER `shelf_id`, ADD `po_number` VARCHAR(255) NOT NULL AFTER `warehouse_name`, ADD `stock_qty` DECIMAL(10.2) NOT NULL AFTER `po_number`;
ALTER TABLE `item_location` ADD `stock_date` DATETIME NOT NULL AFTER `stock_qty`;
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'vat_category', 'Reduced Rate 5%', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'vat_category', 'Zero Rate 0%', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
CREATE TABLE IF NOT EXISTS  `job_task_list` ( 
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `job_id` int(11) NOT NULL,
 `sub_job_number` varchar(255) NOT NULL,
 `qty` double NOT NULL, `twght` double NOT NULL,
 `task_type` varchar(255) NOT NULL,
 `tentative_completion_date` datetime NOT NULL,
 `tentative_completion_time` time NOT NULL, 
`task_description` text NOT NULL, 
`job_assigned` varchar(255) NOT NULL, 
`job_alocation_date` datetime NOT NULL, 
`job_alocation_time` time NOT NULL,
 `job_start_date` datetime NOT NULL,
 `job_start_time` time NOT NULL, 
`job_end_date` datetime NOT NULL,
 `job_end_time` time NOT NULL,
 `sign_off_date` datetime NOT NULL, 
`sign_off_time` time NOT NULL, 
`sign_off_by` int(11) NOT NULL,
 `notes` text NOT NULL, 
`created_by` int(11) NOT NULL,
 `created_on` datetime NOT NULL,
 `updated_by` int(11) NOT NULL, 
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP )

CREATE TABLE `job_cut_list` ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `job_id` INT(11) NOT NULL, `sub_job_id` INT(11) NOT NULL, `user_id` INT(11) NOT NULL, `tab_id` TINYINT(1) DEFAULT NULL COMMENT '1=cut, 2=drilled, 3=fabbed, 4=welded, 5=checked', `start_datetime` DATETIME DEFAULT NULL, `end_datetime` DATETIME DEFAULT NULL, `hours_worked` DECIMAL(5,2) DEFAULT NULL, `notes` TEXT, `created_by` INT(11) DEFAULT NULL, `created_on` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_by` INT(11) DEFAULT NULL, `updated_on` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX (`job_id`), INDEX (`sub_job_id`), INDEX (`user_id`) )

CREATE TABLE IF NOT EXISTS `job_assigned_users` (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  job_id int(11) NOT NULL,
  user_type int(11) NOT NULL,
  user_id varchar(255) NOT NULL,
  created_by int(11) NOT NULL,
  created_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by int(11) NOT NULL,
  updated_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
)


15-04-2026
ALTER TABLE `quo_quotation_management_history` ADD `customer_id` INT(11) NOT NULL AFTER `customer_enquiry_id`, ADD `project_name` VARCHAR(255) NOT NULL AFTER `customer_id`;
ALTER TABLE `quo_quotation_management_history` ADD `std_fittings_weight` DOUBLE NOT NULL AFTER `status_id`, ADD `std_fittings_tonne_rate` DOUBLE NOT NULL AFTER `std_fittings_weight`, ADD `std_fittings_material` DOUBLE NOT NULL AFTER `std_fittings_tonne_rate`, ADD `std_fittings_labour` DOUBLE NOT NULL AFTER `std_fittings_material`, ADD `std_fittings_total` DOUBLE NOT NULL AFTER `std_fittings_labour`, ADD `std_cleats_weight` DOUBLE NOT NULL AFTER `std_fittings_total`, ADD `std_cleats_tonne_rate` DOUBLE NOT NULL AFTER `std_cleats_weight`, ADD `std_cleats_material` DOUBLE NOT NULL AFTER `std_cleats_tonne_rate`, ADD `std_cleats_labour` DOUBLE NOT NULL AFTER `std_cleats_material`, ADD `std_cleats_total` DOUBLE NOT NULL AFTER `std_cleats_labour`;

ALTER TABLE `quo_quotation_management_history` ADD `std_section_weight` DOUBLE NOT NULL AFTER `std_cleats_total`, ADD `std_section_tonne_rate` DOUBLE NOT NULL AFTER `std_section_weight`, ADD `std_section_material` DOUBLE NOT NULL AFTER `std_section_tonne_rate`, ADD `std_section_labour` DOUBLE NOT NULL AFTER `std_section_material`, ADD `std_section_total` DOUBLE NOT NULL AFTER `std_section_labour`, ADD `std_hollow_weight` DOUBLE NOT NULL AFTER `std_section_total`, ADD `std_hollow_tonne_rate` DOUBLE NOT NULL AFTER `std_hollow_weight`, ADD `std_hollow_material` DOUBLE NOT NULL AFTER `std_hollow_tonne_rate`, ADD `std_hollow_labour` DOUBLE NOT NULL AFTER `std_hollow_material`, ADD `std_hollow_total` DOUBLE NOT NULL AFTER `std_hollow_labour`;
ALTER TABLE `quo_quotation_management_history` ADD `std_fabrication_weight` DOUBLE NOT NULL AFTER `std_hollow_total`, ADD `std_fabrication_tonne_rate` DOUBLE NOT NULL AFTER `std_fabrication_weight`, ADD `std_fabrication_labour` DOUBLE NOT NULL AFTER `std_fabrication_tonne_rate`, ADD `std_fabrication_total` DOUBLE NOT NULL AFTER `std_fabrication_labour`, ADD `std_blasting_weight` DOUBLE NOT NULL AFTER `std_fabrication_total`, ADD `std_blasting_tonne_rate` DOUBLE NOT NULL AFTER `std_blasting_weight`, ADD `std_blasting_total` DOUBLE NOT NULL AFTER `std_blasting_tonne_rate`, ADD `std_painting_weight` DOUBLE NOT NULL AFTER `std_blasting_total`, ADD `std_painting_tonne_rate` DOUBLE NOT NULL AFTER `std_painting_weight`, ADD `std_painting_material` DOUBLE NOT NULL AFTER `std_painting_tonne_rate`;
ALTER TABLE `quo_quotation_management_history` ADD `std_painting_labour` DOUBLE NOT NULL AFTER `std_painting_material`, ADD `std_painting_total` DOUBLE NOT NULL AFTER `std_painting_labour`, ADD `std_galvanising_weight` DOUBLE NOT NULL AFTER `std_painting_total`, ADD `std_galvanising_tonne_rate` DOUBLE NOT NULL AFTER `std_galvanising_weight`, ADD `std_galvanising_total` DOUBLE NOT NULL AFTER `std_galvanising_tonne_rate`, ADD `std_powder_coating_weight` DOUBLE NOT NULL AFTER `std_galvanising_total`, ADD `std_powder_coating_tonne_rate` DOUBLE NOT NULL AFTER `std_powder_coating_weight`, ADD `std_powder_coating_material` DOUBLE NOT NULL AFTER `std_powder_coating_tonne_rate`, ADD `std_powder_coating_labour` DOUBLE NOT NULL AFTER `std_powder_coating_material`, ADD `std_powder_coating_total` DOUBLE NOT NULL AFTER `std_powder_coating_labour`;
ALTER TABLE `quo_quotation_management_history` ADD `surface_finishing_grand_total` DOUBLE NOT NULL AFTER `std_powder_coating_total`, ADD `std_delivery_cost` DOUBLE NOT NULL AFTER `surface_finishing_grand_total`;

ALTER TABLE `quo_quotation_management` ADD `std_fittings_weight` DOUBLE NOT NULL AFTER `status_id`, ADD `std_fittings_tonne_rate` DOUBLE NOT NULL AFTER `std_fittings_weight`, ADD `std_fittings_material` DOUBLE NOT NULL AFTER `std_fittings_tonne_rate`, ADD `std_fittings_labour` DOUBLE NOT NULL AFTER `std_fittings_material`, ADD `std_fittings_total` DOUBLE NOT NULL AFTER `std_fittings_labour`, ADD `std_cleats_weight` DOUBLE NOT NULL AFTER `std_fittings_total`, ADD `std_cleats_tonne_rate` DOUBLE NOT NULL AFTER `std_cleats_weight`, ADD `std_cleats_material` DOUBLE NOT NULL AFTER `std_cleats_tonne_rate`, ADD `std_cleats_labour` DOUBLE NOT NULL AFTER `std_cleats_material`, ADD `std_cleats_total` DOUBLE NOT NULL AFTER `std_cleats_labour`;

ALTER TABLE `quo_quotation_management` ADD `std_section_weight` DOUBLE NOT NULL AFTER `std_cleats_total`, ADD `std_section_tonne_rate` DOUBLE NOT NULL AFTER `std_section_weight`, ADD `std_section_material` DOUBLE NOT NULL AFTER `std_section_tonne_rate`, ADD `std_section_labour` DOUBLE NOT NULL AFTER `std_section_material`, ADD `std_section_total` DOUBLE NOT NULL AFTER `std_section_labour`, ADD `std_hollow_weight` DOUBLE NOT NULL AFTER `std_section_total`, ADD `std_hollow_tonne_rate` DOUBLE NOT NULL AFTER `std_hollow_weight`, ADD `std_hollow_material` DOUBLE NOT NULL AFTER `std_hollow_tonne_rate`, ADD `std_hollow_labour` DOUBLE NOT NULL AFTER `std_hollow_material`, ADD `std_hollow_total` DOUBLE NOT NULL AFTER `std_hollow_labour`;
ALTER TABLE `quo_quotation_management` ADD `std_fabrication_weight` DOUBLE NOT NULL AFTER `std_hollow_total`, ADD `std_fabrication_tonne_rate` DOUBLE NOT NULL AFTER `std_fabrication_weight`, ADD `std_fabrication_labour` DOUBLE NOT NULL AFTER `std_fabrication_tonne_rate`, ADD `std_fabrication_total` DOUBLE NOT NULL AFTER `std_fabrication_labour`, ADD `std_blasting_weight` DOUBLE NOT NULL AFTER `std_fabrication_total`, ADD `std_blasting_tonne_rate` DOUBLE NOT NULL AFTER `std_blasting_weight`, ADD `std_blasting_total` DOUBLE NOT NULL AFTER `std_blasting_tonne_rate`, ADD `std_painting_weight` DOUBLE NOT NULL AFTER `std_blasting_total`, ADD `std_painting_tonne_rate` DOUBLE NOT NULL AFTER `std_painting_weight`, ADD `std_painting_material` DOUBLE NOT NULL AFTER `std_painting_tonne_rate`;
ALTER TABLE `quo_quotation_management` ADD `std_painting_labour` DOUBLE NOT NULL AFTER `std_painting_material`, ADD `std_painting_total` DOUBLE NOT NULL AFTER `std_painting_labour`, ADD `std_galvanising_weight` DOUBLE NOT NULL AFTER `std_painting_total`, ADD `std_galvanising_tonne_rate` DOUBLE NOT NULL AFTER `std_galvanising_weight`, ADD `std_galvanising_total` DOUBLE NOT NULL AFTER `std_galvanising_tonne_rate`, ADD `std_powder_coating_weight` DOUBLE NOT NULL AFTER `std_galvanising_total`, ADD `std_powder_coating_tonne_rate` DOUBLE NOT NULL AFTER `std_powder_coating_weight`, ADD `std_powder_coating_material` DOUBLE NOT NULL AFTER `std_powder_coating_tonne_rate`, ADD `std_powder_coating_labour` DOUBLE NOT NULL AFTER `std_powder_coating_material`, ADD `std_powder_coating_total` DOUBLE NOT NULL AFTER `std_powder_coating_labour`;
ALTER TABLE `quo_quotation_management` ADD `surface_finishing_grand_total` DOUBLE NOT NULL AFTER `std_powder_coating_total`, ADD `std_delivery_cost` DOUBLE NOT NULL AFTER `surface_finishing_grand_total`;

CREATE TABLE IF NOT EXISTS `quo_quote_mapping` ( `id` int(11) NOT NULL AUTO_INCREMENT, `quotation_id` int(11) NOT NULL, `description` text NOT NULL, `weight` double NOT NULL, `tonne_rate` double NOT NULL, `material` double NOT NULL, `labour` double NOT NULL, `line_total` double NOT NULL, `created_on` datetime NOT NULL, `created_by` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `quotation_id` (`quotation_id`) )

10-04-2026
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'entitlement', 'days', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'entitlement', 'hours', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
ALTER TABLE `admin` ADD `entitlement_dayhour` INT(11) NOT NULL AFTER `entitlement_unit`;
ALTER TABLE `admin_history` ADD `entitlement_dayhour` INT(11) NOT NULL AFTER `entitlement_unit`;
ALTER TABLE `sup_supplier_details_history` ADD `bank_country` VARCHAR(255) NOT NULL AFTER `bank_postcode`;
ALTER TABLE `sup_supplier_details` ADD `vat_number` VARCHAR(255) NOT NULL AFTER `country`, ADD `job_role` VARCHAR(255) NOT NULL AFTER `vat_number`, ADD `nominal_code` INT(11) NOT NULL AFTER `job_role`, ADD `tax_code` INT(11) NOT NULL AFTER `nominal_code`, ADD `bank_name` VARCHAR(255) NOT NULL AFTER `tax_code`, ADD `bank_address_line_1` VARCHAR(255) NOT NULL AFTER `bank_name`, ADD `bank_address_line_2` VARCHAR(255) NOT NULL AFTER `bank_address_line_1`, ADD `bank_town` VARCHAR(255) NOT NULL AFTER `bank_address_line_2`, ADD `bank_county` VARCHAR(255) NOT NULL AFTER `bank_town`, ADD `bank_postcode` VARCHAR(100) NOT NULL AFTER `bank_county`;
ALTER TABLE `sup_supplier_details` ADD `account_name` VARCHAR(255) NOT NULL AFTER `bank_postcode`, ADD `account_number` VARCHAR(100) NOT NULL AFTER `account_name`, ADD `sort_code` VARCHAR(100) NOT NULL AFTER `account_number`;

CREATE TABLE IF NOT EXISTS `supplier_documents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) NOT NULL, `notes` text NOT NULL, `document_name` varchar(255) DEFAULT NULL COMMENT 'Original File Name', `document_file` varchar(255) DEFAULT NULL COMMENT 'System File Name', `created_on` datetime DEFAULT NULL, `created_by` int(11) DEFAULT NULL, PRIMARY KEY (`id`) )

ALTER TABLE `sup_supplier_details_history` ADD `account_code` VARCHAR(255) NOT NULL AFTER `supplier_name`, ADD `account_archived` TINYINT(1) NOT NULL AFTER `account_code`;

ALTER TABLE `sup_supplier_details_history` ADD `vat_number` VARCHAR(255) NOT NULL AFTER `country`, ADD `job_role` VARCHAR(255) NOT NULL AFTER `vat_number`, ADD `nominal_code` INT(11) NOT NULL AFTER `job_role`, ADD `tax_code` INT(11) NOT NULL AFTER `nominal_code`, ADD `bank_name` VARCHAR(255) NOT NULL AFTER `tax_code`, ADD `bank_address_line_1` VARCHAR(255) NOT NULL AFTER `bank_name`, ADD `bank_address_line_2` VARCHAR(255) NOT NULL AFTER `bank_address_line_1`, ADD `bank_town` VARCHAR(255) NOT NULL AFTER `bank_address_line_2`, ADD `bank_county` VARCHAR(255) NOT NULL AFTER `bank_town`, ADD `bank_postcode` VARCHAR(100) NOT NULL AFTER `bank_county`, ADD `account_name` VARCHAR(255) NOT NULL AFTER `bank_postcode`, ADD `account_number` VARCHAR(100) NOT NULL AFTER `account_name`, ADD `sort_code` VARCHAR(100) NOT NULL AFTER `account_number`;

ALTER TABLE `sup_supplier_details_history` ADD `action_type` VARCHAR(255) NOT NULL AFTER `updated_by`;
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'nominal_code', 'cash', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'tax_code ', 'T1', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'trading_terms', 'term1', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');

CREATE TABLE `cust_customer_details_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `his_id` int(11) DEFAULT NULL,
  `account_code` varchar(100) DEFAULT NULL,
  `company_name` varchar(255) NOT NULL,
  `account_archived` tinyint(1) NOT NULL DEFAULT '0',
  `address_line_1` varchar(255) DEFAULT NULL,
  `address_line_2` varchar(255) DEFAULT NULL,
  `town` varchar(100) DEFAULT NULL,
  `county` varchar(100) DEFAULT NULL,
  `postcode` varchar(20) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `vat_number` varchar(100) DEFAULT NULL,
  `eori_number` varchar(100) DEFAULT NULL,
  `contact_name` varchar(255) DEFAULT NULL,
  `job_role` varchar(100) DEFAULT NULL,
  `contact_email` varchar(512) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `nominal_code` int(11) DEFAULT NULL,
  `tax_code` int(11) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `credit_limit` decimal(12,2) DEFAULT NULL,
  `trading_terms` int(11) DEFAULT NULL,
  `account_open_date` date DEFAULT NULL,
  `bank_name` varchar(255) DEFAULT NULL,
  `bank_address_line_1` varchar(255) DEFAULT NULL,
  `bank_address_line_2` varchar(255) DEFAULT NULL,
  `bank_town` varchar(100) DEFAULT NULL,
  `bank_county` varchar(100) DEFAULT NULL,
  `bank_postcode` varchar(20) DEFAULT NULL,
  `bank_country` varchar(100) DEFAULT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `sort_code` varchar(50) DEFAULT NULL,
  `notes` text,
  `image_main_id` int(11) NOT NULL,
  `is_archive` tinyint(4) NOT NULL DEFAULT '0',
  `archived_on` datetime DEFAULT NULL,
  `archived_by` int(11) DEFAULT NULL,
  `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(11) DEFAULT NULL,
  `updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `action_type` varchar(255) NOT NULL,
  `history_created_by` int(11) NOT NULL,
  `history_created_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `postcode` (`postcode`),
  KEY `is_archive` (`is_archive`),
  KEY `his_id` (`his_id`)
) ;
CREATE TABLE IF NOT EXISTS `customer_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `doc_type` int(11) DEFAULT NULL COMMENT 'Linked to keycode.id',
  `document_name` varchar(255) DEFAULT NULL COMMENT 'Original File Name',
  `document_file` varchar(255) DEFAULT NULL COMMENT 'System File Name',
  `created_on` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`)
);
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'customer_document_type', 'Invoice', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
ALTER TABLE `admin_history` ADD `middle_name` VARCHAR(512) NOT NULL AFTER `first_name`;
ALTER TABLE `admin_history` ADD `gender` INT(11) NOT NULL AFTER `sur_name`;
ALTER TABLE `admin_history` ADD `home_phone` varchar(25) NOT NULL AFTER `contact_number`, ADD `work_phone` varchar(25) NOT NULL AFTER `home_phone`, ADD `work_extension` varchar(512) NOT NULL AFTER `work_phone`, ADD `emergency_first_name` varchar(512) NOT NULL AFTER `work_extension`, ADD `emergency_last_name` varchar(512) NOT NULL AFTER `emergency_first_name`
ALTER TABLE `admin_history` ADD `employment_type` INT(11) NOT NULL AFTER `postcode`, ADD `entitlement_unit` VARCHAR(512) NOT NULL AFTER `employment_type`, ADD `emergency_mobile_phone` VARCHAR(25) NOT NULL AFTER `entitlement_unit`, ADD `emergency_work_phone` VARCHAR(25) NOT NULL AFTER `emergency_mobile_phone`, ADD `relationship` VARCHAR(512) NOT NULL AFTER `emergency_work_phone`, ADD `payroll_reference` VARCHAR(512) NOT NULL AFTER `relationship`, ADD `rti_payroll_id` VARCHAR(512) NOT NULL AFTER `payroll_reference`, ADD `payment_reference` INT(11) NOT NULL AFTER `rti_payroll_id`, ADD `pay_frequency` INT(11) NOT NULL AFTER `payment_reference`, ADD `permission_level` INT(11) NOT NULL AFTER `pay_frequency`;
ALTER TABLE `admin_history` ADD `job_title` VARCHAR(512) NOT NULL AFTER `permission_level`, ADD `team` INT(11) NOT NULL AFTER `job_title`, ADD `reports_to` INT(11) NOT NULL AFTER `team`, ADD `contract_type` INT(11) NOT NULL AFTER `reports_to`, ADD `contracted_hours` VARCHAR(50) NOT NULL AFTER `contract_type`, ADD `start_date` DATE NOT NULL AFTER `contracted_hours`, ADD `length_of_service` VARCHAR(512) NOT NULL AFTER `start_date`, ADD `probation_end_date` DATE NOT NULL AFTER `length_of_service`, ADD `working_location` INT(11) NOT NULL AFTER `probation_end_date`, ADD `working_time_pattern` INT(11) NOT NULL AFTER `working_location`, ADD `right_to_work_status` INT(11) NOT NULL AFTER `working_time_pattern`, ADD `settled_share_code` VARCHAR(512) NOT NULL AFTER `right_to_work_status`;
ALTER TABLE `admin_history` ADD `settled_date_issued` DATE NOT NULL AFTER `settled_share_code`, ADD `settled_date_checked` DATE NOT NULL AFTER `settled_date_issued`, ADD `pre_settled_share_code` VARCHAR(512) NOT NULL AFTER `settled_date_checked`, ADD `pre_settled_date_issued` DATE NOT NULL AFTER `pre_settled_share_code`, ADD `pre_settled_date_checked` DATE NOT NULL AFTER `pre_settled_date_issued`, ADD `pre_settled_date_expiry` DATE NOT NULL AFTER `pre_settled_date_checked`, ADD `not_declared_share_code` VARCHAR(512) NOT NULL AFTER `pre_settled_date_expiry`, ADD `not_declared_date_issued` DATE NOT NULL AFTER `not_declared_share_code`, ADD `not_declared_date_checked` DATE NOT NULL AFTER `not_declared_date_issued`, ADD `country_of_issue` VARCHAR(512) NOT NULL AFTER `not_declared_date_checked`;
ALTER TABLE `admin_history` ADD `licence_class` VARCHAR(512) NOT NULL AFTER `driving_licence_number`, ADD `date_of_expiry` DATE NOT NULL AFTER `licence_class`, ADD `first_aider` VARCHAR(512) NOT NULL AFTER `date_of_expiry`, ADD `first_responder` VARCHAR(512) NOT NULL AFTER `first_aider`, ADD `manager` VARCHAR(512) NOT NULL AFTER `first_responder`, ADD `user_admin` VARCHAR(512) NOT NULL AFTER `manager`, ADD `leave_entitlement_days` VARCHAR(50) NOT NULL AFTER `user_admin`;
ALTER TABLE `admin_history` ADD `action_type` VARCHAR(255) NOT NULL AFTER `last_login`;
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'contract_type', 'Permanent', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'working_location', 'On-Site', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'working_time_pattern', 'Regular', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
CREATE TABLE IF NOT EXISTS `cust_customer_details`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `account_code` VARCHAR(100) DEFAULT NULL,
  `company_name` VARCHAR(255) NOT NULL,
  `account_archived` TINYINT(1) NOT NULL DEFAULT 0,
  `address_line_1` VARCHAR(255) DEFAULT NULL,
  `address_line_2` VARCHAR(255) DEFAULT NULL,
  `town` VARCHAR(100) DEFAULT NULL,
  `county` VARCHAR(100) DEFAULT NULL,
  `postcode` VARCHAR(20) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT NULL,
  `vat_number` VARCHAR(100) DEFAULT NULL,
  `eori_number` VARCHAR(100) DEFAULT NULL,
  `contact_name` VARCHAR(255) DEFAULT NULL,
  `job_role` VARCHAR(100) DEFAULT NULL,
  `contact_email` VARCHAR(512) DEFAULT NULL,
  `phone_number` VARCHAR(255) DEFAULT NULL,
  `nominal_code` INT(11) DEFAULT NULL,
  `tax_code` INT(11) DEFAULT NULL,
  `discount` DECIMAL(10,2) DEFAULT NULL,
  `credit_limit` DECIMAL(12,2) DEFAULT NULL,
  `trading_terms` INT(11) DEFAULT NULL,
  `account_open_date` DATE DEFAULT NULL,
  `bank_name` VARCHAR(255) DEFAULT NULL,
  `bank_address_line_1` VARCHAR(255) DEFAULT NULL,
  `bank_address_line_2` VARCHAR(255) DEFAULT NULL,
  `bank_town` VARCHAR(100) DEFAULT NULL,
  `bank_county` VARCHAR(100) DEFAULT NULL,
  `bank_postcode` VARCHAR(20) DEFAULT NULL,
  `bank_country` VARCHAR(100) DEFAULT NULL,
  `account_name` VARCHAR(255) DEFAULT NULL,
  `account_number` VARCHAR(50) DEFAULT NULL,
  `sort_code` VARCHAR(50) DEFAULT NULL,
  `notes` TEXT,
  `is_archive` tinyint(4) NOT NULL DEFAULT 0,
  `archived_on` datetime DEFAULT NULL,
  `archived_by` int(11) DEFAULT NULL,
  `created_on` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `updated_on` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)
);
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'payment_reference', 'FR', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'pay_frequency', 'Weekly', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'permission_level', 'Admin', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'employment_type', 'Full-time', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');
INSERT INTO `keycode` (`id`, `name`, `code`, `description`, `site_id`, `ord`, `updated_on`, `updated_by`, `status`) VALUES (NULL, 'right_to_work_status', 'Settled', NULL, NULL, '', CURRENT_TIMESTAMP, '', '1');

ALTER TABLE `admin` ADD `middle_name` VARCHAR(512) NOT NULL AFTER `first_name`;
ALTER TABLE `admin` ADD `gender` INT(11) NOT NULL AFTER `sur_name`;
ALTER TABLE `admin` ADD `home_phone` varchar(25) NOT NULL AFTER `contact_number`,
ADD `work_phone` varchar(25) NOT NULL AFTER `home_phone`, ADD `work_extension` varchar(512) NOT NULL AFTER `work_phone`, ADD `emergency_first_name` varchar(512) NOT NULL AFTER `work_extension`, ADD `emergency_last_name` varchar(512) NOT NULL AFTER `emergency_first_name`;
ALTER TABLE `admin` ADD `employment_type` INT(11) NOT NULL AFTER `postcode`, ADD `entitlement_unit` VARCHAR(512) NOT NULL AFTER `employment_type`, ADD `emergency_mobile_phone` VARCHAR(25) NOT NULL AFTER `entitlement_unit`, ADD `emergency_work_phone` VARCHAR(25) NOT NULL AFTER `emergency_mobile_phone`, ADD `relationship` VARCHAR(512) NOT NULL AFTER `emergency_work_phone`, ADD `payroll_reference` VARCHAR(512) NOT NULL AFTER `relationship`, ADD `rti_payroll_id` VARCHAR(512) NOT NULL AFTER `payroll_reference`, ADD `payment_reference` INT(11) NOT NULL AFTER `rti_payroll_id`, ADD `pay_frequency` INT(11) NOT NULL AFTER `payment_reference`, ADD `permission_level` INT(11) NOT NULL AFTER `pay_frequency`;
ALTER TABLE `admin` ADD `job_title` VARCHAR(512) NOT NULL AFTER `permission_level`, ADD `team` INT(11) NOT NULL AFTER `job_title`, ADD `reports_to` INT(11) NOT NULL AFTER `team`, ADD `contract_type` INT(11) NOT NULL AFTER `reports_to`, ADD `contracted_hours` VARCHAR(50) NOT NULL AFTER `contract_type`, ADD `start_date` DATE NOT NULL AFTER `contracted_hours`, ADD `length_of_service` VARCHAR(512) NOT NULL AFTER `start_date`, ADD `probation_end_date` DATE NOT NULL AFTER `length_of_service`, ADD `working_location` INT(11) NOT NULL AFTER `probation_end_date`, ADD `working_time_pattern` INT(11) NOT NULL AFTER `working_location`, ADD `right_to_work_status` INT(11) NOT NULL AFTER `working_time_pattern`, ADD `settled_share_code` VARCHAR(512) NOT NULL AFTER `right_to_work_status`;
ALTER TABLE `admin` ADD `settled_date_issued` DATE NOT NULL AFTER `settled_share_code`, ADD `settled_date_checked` DATE NOT NULL AFTER `settled_date_issued`, ADD `pre_settled_share_code` VARCHAR(512) NOT NULL AFTER `settled_date_checked`, ADD `pre_settled_date_issued` DATE NOT NULL AFTER `pre_settled_share_code`, ADD `pre_settled_date_checked` DATE NOT NULL AFTER `pre_settled_date_issued`, ADD `pre_settled_date_expiry` DATE NOT NULL AFTER `pre_settled_date_checked`, ADD `not_declared_share_code` VARCHAR(512) NOT NULL AFTER `pre_settled_date_expiry`, ADD `not_declared_date_issued` DATE NOT NULL AFTER `not_declared_share_code`, ADD `not_declared_date_checked` DATE NOT NULL AFTER `not_declared_date_issued`, ADD `country_of_issue` VARCHAR(512) NOT NULL AFTER `not_declared_date_checked`;
ALTER TABLE `admin` ADD `licence_class` VARCHAR(512) NOT NULL AFTER `driving_licence_number`, ADD `date_of_expiry` DATE NOT NULL AFTER `licence_class`, ADD `first_aider` VARCHAR(512) NOT NULL AFTER `date_of_expiry`, ADD `first_responder` VARCHAR(512) NOT NULL AFTER `first_aider`, ADD `manager` VARCHAR(512) NOT NULL AFTER `first_responder`, ADD `user_admin` VARCHAR(512) NOT NULL AFTER `manager`, ADD `leave_entitlement_days` VARCHAR(50) NOT NULL AFTER `user_admin`;
ALTER TABLE `admin` ADD `title` INT(11) NOT NULL AFTER `allow_delete`;