-------=======DONE======------
ALTER TABLE `tbl_project_stg0` CHANGE `estimated_project_start_date` `estimated_project_start_date` DATE NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg0` CHANGE `sector_id` `sector_id` INT(11) NULL DEFAULT NULL COMMENT 'FK tbl_sectors';
ALTER TABLE `tbl_project_stg0` CHANGE `sub_sector_id` `sub_sector_id` INT(11) NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg0` CHANGE `location_id` `location_id` INT(11) NULL DEFAULT NULL COMMENT 'FK tbl_locations';
ALTER TABLE `tbl_project_stg0` CHANGE `department_id` `department_id` INT(11) NULL DEFAULT NULL COMMENT 'FK tbl_departments';
ALTER TABLE `tbl_project_stg0` CHANGE `project_owner` `project_owner` INT(11) NULL DEFAULT NULL COMMENT 'FK tbl_user_master';
ALTER TABLE `tbl_project_stg0` CHANGE `project_manager` `project_manager` INT(11) NULL DEFAULT NULL COMMENT 'FK tbl_user_master';
ALTER TABLE `tbl_project_stg0` CHANGE `ballpark_project_cost` `ballpark_project_cost` INT(11) NULL DEFAULT NULL, CHANGE `estimated_project_duration_years` `estimated_project_duration_years` INT(11) NULL DEFAULT NULL, CHANGE `estimated_project_duration_months` `estimated_project_duration_months` INT(11) NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stages` ADD `additional_check` ENUM('yes','no') NOT NULL DEFAULT 'no' COMMENT 'Stage intermediate status' AFTER `active_gate`;

INSERT INTO `tbl_project_stages` (`project_stage_id`, `project_id`, `stage_id`, `stage_status`, `active_gate`, `stage_startdate`, `stage_enddate`, `prerequisites_stages`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('21', '101', 'project_stage_0', 'stage_status_2', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('22', '101', 'project_stage_1', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('23', '101', 'project_stage_2', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('24', '101', 'project_stage_3', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('25', '101', 'project_stage_4', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('26', '101', 'project_stage_5', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('27', '101', 'project_stage_6', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('28', '101', 'project_stage_7', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('29', '101', 'project_stage_8', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL), ('30', '101', 'project_stage_9', 'stage_status_1', '1', NULL, NULL, '', '2025-07-11 10:26:08', NULL, NULL, NULL);

UPDATE `tbl_lookup_detail` SET `item_data` = '{\"stage_name\":\"Stage 0\",\"duration_in_days\":5,\"add_check\":\"yes\",\"add_check_name\":\"Project Pipeline\"}' WHERE `tbl_lookup_detail`.`optionid` = 11;
UPDATE `tbl_lookup_detail` SET `item_data` = '{\"add_check\":\"yes\",\"add_check_name\":\"Project Pipeline\"}' WHERE `tbl_lookup_detail`.`optionid` = 11;
UPDATE `tbl_lookup_detail` SET `item_data` = '{\"add_check\":\"yes\",\"add_check_name\":\"Prioritise Projects\"}' WHERE `tbl_lookup_detail`.`optionid` = 12;

CREATE TABLE `tbl_stage_approvers` (`stg_data_key` VARCHAR(50) NOT NULL , `user_id` INT(11) NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_stage_approvers` ADD PRIMARY KEY(`stg_data_key`, `user_id`);
ALTER TABLE `tbl_project_stages` ADD `approver_id` INT NOT NULL COMMENT 'tbl_user_master (user_id)' AFTER `additional_check`;




-- run only once
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('106', 'Wingfield Interchange Project', 'The project integrates with the broader Cape Town Integrator plan to enhance connectivity between key urban and industrial zones.', '9', '21', '3', '4', '1', '2', '', '', '0', '0', '0', NULL, '1', NULL, NULL, NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('101', 'Leeuloop Precinct Development', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:38:21', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('102', 'Founders Garden Artscape', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:38:50', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('103', 'Oude Molen Precinct', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('104', 'Provincial Pavement Testing Laboratory', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('105', 'Stikland Mixed Housing', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('107', 'Cape Town Integrator R300 Ring Road', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('108', 'Transhex Housing Development', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('109', 'Sanddrift Mixed Housing Development', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('110', 'Ecological Infrastructure Investment within Strategic watersource areas of the Western Cape', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('111', 'Provincial Public-sector Strategic Offset Bank', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('112', 'Saldanha Free Port Harbour Development', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('113', 'George Western Bypass', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('114', 'Infrastructure Expansion for Clanwilliam, Kayalitsha, and Citrusdal Development (Test project)', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);
INSERT INTO `tbl_project_stg0` (`project_id`, `name`, `desc`, `sector_id`, `sub_sector_id`, `location_id`, `department_id`, `project_owner`, `project_manager`, `background_justification`, `scope_of_work`, `ballpark_project_cost`, `estimated_project_duration_years`, `estimated_project_duration_months`, `estimated_project_start_date`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('115', 'Infrastructure Expansion for Clanwilliam, Kayalitsha, and Citrusdal Development (Test project)11111', '', NULL, NULL, NULL, NULL, NULL, NULL, '', '', NULL, NULL, NULL, NULL, '1', '2025-07-15 09:39:24', '1', NULL, NULL);


CREATE TABLE `tbl_maxids` (`id` INT(11) NOT NULL , `tbl_name` VARCHAR(100) NOT NULL , `max_id` BIGINT NOT NULL ) ENGINE = InnoDB;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('1', 'tbl_project_stg0_objective', '3');
ALTER TABLE `tbl_maxids` ADD PRIMARY KEY(`id`);


--========NEW START=====-------
ALTER TABLE `tbl_project_stages` CHANGE `approver_id` `approver_id` INT(11) NULL DEFAULT NULL COMMENT 'tbl_user_master (user_id)';
ALTER TABLE `tbl_project_stg0_objective` CHANGE `objective_name` `objective_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `tbl_project_users` DROP `total_questions`,`attempted_questions`,`eval_status`;
ALTER TABLE `tbl_project_users` ADD `project_rights` VARCHAR(25) NULL DEFAULT 'view' AFTER `role_id`;
ALTER TABLE `tbl_project_stg0_objective` ADD `row_id` INT(11) NOT NULL FIRST;


-- Date - 16 July 2025
ALTER TABLE `tbl_categories` ADD `type` VARCHAR(100) NULL AFTER `cat_name`;
UPDATE `tbl_categories` SET `type` = 'panoptic';
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Finance', 'project', '2025-07-16 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Initiation Report', 'project', '2025-07-16 23:38:13', NULL, NULL, NULL);

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('9', 'project_team_roles', 'Project Team Roles', 'project-team-roles-ml', 'project_team_roles', '1', 'No', '2025-07-14 16:11:03', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('42', '9', '1', 'project_team_roles_1', 'Project Lead', 'project-lead-ml', '1', '{\"val\":1}', 'active', '2025-07-14 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('43', '9', '2', 'project_team_roles_2', 'Team member', 'team-member-ml', '2', '{\"val\":2}', 'active', '2025-07-14 16:17:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('44', '9', '3', 'project_team_roles_3', '⁠Project Owner', 'project-owner-ml', '3', '{\"val\":3}', 'active', '2025-07-14 16:18:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('45', '9', '4', 'project_team_roles_4', 'Project Manager', 'project-manager-ml', '4', '{\"val\":4}', 'active', '2025-07-14 16:19:46', '1', NULL, NULL);

ALTER TABLE `tbl_project_users` CHANGE `role_id` `role_id` VARCHAR(50) NOT NULL COMMENT 'FK - tbl_roles';
UPDATE `tbl_project_users` SET `role_id` = 'project_team_roles_3';

SELECT count(*) into  from tbl_documents;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('2', 'tbl_documents', );

ALTER TABLE `tbl_project_docs` CHANGE `doc_type` `doc_type` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

INSERT INTO `tbl_stage_approvers` (`stg_data_key`, `user_id`) 
VALUES 
('project_stage_0', '1'),
('project_stage_1', '2'),
('project_stage_2', '3'),
('project_stage_3', '4'),
('project_stage_4', '5'), 
('project_stage_5', '6'), 
('project_stage_6', '7'),
('project_stage_7', '101'),
('project_stage_8', '107'),
('project_stage_9', '112');


CREATE TABLE `tbl_project_stages_logs` (
    `id` INT NOT NULL , `project_id` INT NOT NULL , 
    `stage_datakey` VARCHAR(50) NOT NULL , 
    `comment` TEXT NOT NULL , 
    `created_date` DATETIME NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stages_logs` ADD PRIMARY KEY(`id`);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('46', '5', '6', 'stage_status_6', 'Rejected', 'rejected-ml', '6', '{\"val\":6}', 'active', '2025-07-16 15:43:33', '1', NULL, NULL);

ALTER TABLE `tbl_project_stages_logs` ADD `stage_status_datakey` VARCHAR(50) NOT NULL AFTER `stage_datakey`;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('3', 'tbl_project_stages_logs', '0');

INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('4', 'tbl_lookup_detail', '46');

-- tbl_project_stg1 definition

CREATE TABLE `tbl_project_stg1` (
  `project_id` int(11) NOT NULL,
  `hardware_details` text DEFAULT NULL,
  `software_details` text DEFAULT NULL,
  `critical_details` text DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `updation_date` datetime NOT NULL,
  `updation_by` int(11) NOT NULL,
  PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- tbl_project_stg1_keys definition

CREATE TABLE `tbl_project_stg1_keys` (
  `project_id` int(11) NOT NULL,
  `key_name` varchar(100) NOT NULL,
  `key_value` varchar(100) NOT NULL,
  `creation_date` datetime DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`project_id`,`key_name`,`key_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- tbl_project_stg1_outcomes definition

CREATE TABLE `tbl_project_stg1_outcomes` (
  `row_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `sector_id` int(11) NOT NULL,
  `outcome_datakey` varchar(50) NOT NULL,
  `priotities_datakey` varchar(50) NOT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- tbl_maxids definition
INSERT INTO tbl_maxids (id,tbl_name,max_id) VALUES
	 (5,'tbl_project_stg1_outcomes',0);

ALTER TABLE `tbl_project_stg1_keys` ADD `free_text` TEXT NOT NULL AFTER `key_value`;


CREATE TABLE `tbl_project_additonal_stages` (`additional_check_datakey` VARCHAR(100) NOT NULL , `project_id` INT NOT NULL , `stage_id` VARCHAR(100) NOT NULL COMMENT 'Before given stages we need approvals from approvers.' , `user_id` INT NOT NULL , `remarks` TEXT NOT NULL , `approval_status` VARCHAR(50) NOT NULL ) ENGINE = InnoDB;
CREATE TABLE `tbl_additional_stages_users` (`additional_check_datakey` VARCHAR(100) NOT NULL , `user_id` INT NOT NULL ) ENGINE = InnoDB;

UPDATE `tbl_lookup_detail` SET `display_name`='Financial Systems' WHERE  `optionid`=96;

-- ****************************************************************************************

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `creation_date`, `created_by`) VALUES (25, 'procurement_plan', 'Procurement Plan', 'procurement-plan-ml', 'Procurement Plan', 1, NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`,`keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (98,25, '1', 'procurement_plan_1', 'Decentralized Preference Criteria', 'decentralized-preference-criteria-ml', 1, '{"sub_type_1":"Departments","sub_type_2":"Municipalities","sub_type_3":"Other"}', 'active',  NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`,`data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (99, 25, '2', 'procurement_plan_2', 'Designated Groups', 'designated-groups-ml', 2, '{"sub_type_1":"Historically disadvantaged individuals","sub_type_2":"Women-owned businesses","sub_type_3":"Youth-owned businesses","sub_type_4":"Persons with disabilities"}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (100, 25, '3', 'procurement_plan_3', 'Local Content and Production', 'local-content-and-production-ml', 3, '{"val":3}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (101, 25, '4', 'procurement_plan_4', 'Broad-Based Black Economic Empowerment (B-BBEE)', 'broad-based-black-economic-ml', 4, '{"val":4}', 'active', '2025-07-20 15:35:13', 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (102, 25, '5', 'procurement_plan_5', 'Other', 'other-ml', 5, '{"val":5}', 'active', NOW(), 1);


-- ****************************************************************************************

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `creation_date`, `created_by`) VALUES (26, 'procurement_action', 'Procurement Action', 'procurement-action-ml', 'Procurement Action', 1, NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (103, 26, '1', 'procurement_action_1', 'E-Procurement Solution (ePS)', 'e-procurement-solution-ml', 1, '{"val":1}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (104, 26, '2', 'procurement_action_2', 'Western Cape Supplier Evidence Bank (WCSEB)', 'western-cape-supplier-evidence-bank-ml', 2, '{"val":2}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (105, 26, '3', 'procurement_action_3', 'Procurement Planning', 'procurement-planning-ml', 3, '{"val":3}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (106, 26, '4', 'procurement_action_4', 'Preferential Procurement Regulations (2022)', 'preferential-procurement-regulations-ml', 4, '{"val":4}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (107, 26, '5', 'procurement_action_5', 'Sustainable Public Procurement (SPP)', 'sustainable-public-procurement-ml', 5, '{"val":5}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (108, 26, '6', 'procurement_action_6', 'Other', 'other-ml', 6, '{"val":6}', 'active', NOW(), 1);


-- ****************************************************************************************

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `creation_date`, `created_by`) VALUES (27, 'social_impact', 'Social Impact', 'social-impact-ml', 'Social Impact', 1, NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (109, 27, '1', 'social_impact_1', 'Inclusive Economic Participation', 'inclusive-economic-participation-ml', 1, '{"description":"Prioritizes procurement from small, medium, and micro enterprises (SMMEs), women-owned, and youth-owned businesses."}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (110, 27, '2', 'social_impact_2', 'Environmental Sustainability', 'environmental-sustainability-ml', 2, '{"description":"Supports green procurement practices aligned with the Western Cape’s Green Economy Strategy."}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (111, 27, '3', 'social_impact_3', 'Skills Development and Job Creation', 'skills-development-and-job-creation-ml', 3, '{"description":"Tenders often include requirements for local employment and training opportunities."}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (112, 27, '4', 'social_impact_4', 'Community Empowerment', 'community-empowerment-ml', 4, '{"description":"Encourages partnerships with community-based organizations and cooperatives."}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (113, 27, '5', 'social_impact_5', 'Ethical and Transparent Governance', 'ethical-and-transparent-governance-ml', 5, '{"description":"Emphasizes anti-corruption measures and fair competition in procurement processes."}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (114, 27, '6', 'social_impact_6', 'Other', 'other-ml', 6, '{"description":""}', 'active', NOW(), 1);

-- ****************************************************************************************

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('28', 'project_additional_stages', 'Project Additional Stages', 'project-additional-stages-ml', 'Project Additional Stages', '1', 'No', '2025-07-28 11:44:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('115', '28', '1', 'project_additional_stages_1', 'Create Project Pipeline', 'create-project-pipeline-ml', '1', '{\"description\":\"\"}', 'active', '2025-07-28 11:44:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('116', '28', '2', 'project_additional_stages_2', 'Prioritise Projects', 'prioritise-projects-ml', '2', '{\"description\":\"\"}', 'active', '2025-07-28 11:44:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('117', '28', '3', 'project_additional_stages_3', 'Reviews', 'reviews-ml', '2', '{\"description\":\"\"}', 'active', '2025-07-28 11:44:46', '1', NULL, NULL);

-- ****************************************************************************************

ALTER TABLE `tbl_project_stages` CHANGE `additional_check` `additional_check` ENUM('yes','no','na') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'no' COMMENT 'Stage intermediate status';
ALTER TABLE `tbl_project_additonal_stages` ADD `row_id` INT NOT NULL FIRST;
ALTER TABLE `tbl_project_additonal_stages` ADD PRIMARY KEY(`row_id`);
ALTER TABLE `tbl_project_additonal_stages` CHANGE `row_id` `row_id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tbl_project_stages` CHANGE `additional_check` `additional_check` ENUM('yes','no','na') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'na' COMMENT 'Stage intermediate status';


CREATE TABLE `tbl_project_stg2` (`project_id` INT NOT NULL , `geo_location` TEXT NULL DEFAULT NULL , `geo_lat` VARCHAR(100) NULL DEFAULT NULL , `geo_long` VARCHAR(100) NULL DEFAULT NULL , `property_identified` VARCHAR(25) NULL DEFAULT NULL , `property_remarks` INT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL , `updation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `updated_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg2` ADD PRIMARY KEY(`project_id`);

CREATE TABLE `tbl_project_stg2_keys` (`project_id` INT NOT NULL , `key_name` VARCHAR(100) NOT NULL , `key_value` VARCHAR(100) NOT NULL , `sub_type` VARCHAR(100) NULL DEFAULT NULL , `sub_type_name` TEXT NULL DEFAULT NULL , `free_text` TEXT NULL DEFAULT NULL , `creation_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg2` CHANGE `property_remarks` `property_remarks` TEXT NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg2_keys` ADD PRIMARY KEY(`project_id`, `key_name`, `key_value`);

UPDATE `tbl_lookup_detail` SET `item_data` = '{\"sub_type_1\":\"Aligned\",\"sub_type_2\":\"Not Aligned\"}' WHERE `tbl_lookup_detail`.`optionid` = 106;
UPDATE `tbl_lookup_detail` SET `item_data` = '{\"sub_type_1\":\"Aligned\",\"sub_type_2\":\"Not Aligned\"}' WHERE `tbl_lookup_detail`.`optionid` = 107;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 105;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 100;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 101;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 102;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 103;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 104;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 108;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 6;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 7;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 8;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 9;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` = 10;
UPDATE `tbl_lookup_detail` SET `item_data` = '' WHERE `tbl_lookup_detail`.`optionid` in (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,115,116,117);

ALTER TABLE `tbl_project_stages` ADD `send_to_approval` VARCHAR(25) NOT NULL DEFAULT 'no' AFTER `stage_status_remarks`;

CREATE TABLE `tbl_regions` (`region_id` INT NOT NULL , `name` VARCHAR(250) NOT NULL , `is_active` INT NULL DEFAULT '1' , `creation_date` DATETIME NULL DEFAULT NULL , `created_by` INT NULL DEFAULT NULL , `updation_date` DATETIME NULL DEFAULT NULL , `updated_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_regions` ADD PRIMARY KEY(`region_id`);

INSERT INTO `tbl_regions` (`region_id`, `name`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('1', 'Region1', '1', '2025-08-01 15:05:05', '1', NULL, NULL),
('2', 'Region2', '1', '2025-08-01 15:05:05', '1', NULL, NULL),
('3', 'Region3', '1', '2025-08-01 15:05:05', '1', NULL, NULL),
('4', 'Region4', '1', '2025-08-01 15:05:05', '1', NULL, NULL),
('5', 'Region5', '1', '2025-08-01 15:05:05', '1', NULL, NULL);


CREATE TABLE `tbl_municipality` (`m_id` INT NOT NULL , `region_id` INT NOT NULL , `code` VARCHAR(50) NULL DEFAULT NULL , `name` VARCHAR(250) NULL DEFAULT NULL , `address` VARCHAR(500) NULL DEFAULT NULL , `postal_address` VARCHAR(500) NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL , `updation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `updated_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_municipality` ADD `is_active` INT NOT NULL DEFAULT '1' AFTER `postal_address`;

ALTER TABLE `tbl_municipality` ADD PRIMARY KEY(`m_id`);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('1', '1', 'Metro', 'City of Cape Town', 'Fifth Floor, Podium Block,Civic Centre,12 Hertzog Blvd,Cape Town,8001', 'Private Bag X9181, Cape Town, 8000', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('2', '1', 'DC1', 'West Coast District Municipality ', '58 Lang Street, Moorreesburg,7310', 'PO Box 242, Moorreesburg', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('3', '2', 'WC011', 'Matzikama Municipality', '37 Church Street, Vredendal, 8160', 'PO Box 98, Vredendal, 8160', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('4', '2', 'WC012', 'Cederberg Municipality', '2A Voortrekker Street, Clanwilliam, 8135', 'Private Bag X2, Clanwilliam, 8135', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('5', '3', 'WC013', 'Bergrivier Municipality', '13 Kerk Straat, Piketberg, 7320', 'PO Box 60, Piketberg, 7320', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('6', '4', 'WC014', 'Saldanha Bay Municipality', '12 Main Street, Vredenburg, 7380', 'Private Bag X12, Vredenburg, 7380', current_timestamp(), '1', current_timestamp(), NULL);
INSERT INTO `tbl_municipality` (`m_id`, `region_id`, `code`, `name`, `address`, `postal_address`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('7', '4', 'WC015', 'Swartland Municipality', 'C/O Rainier Street and Church Street, Malmesbury, 7300', 'Private Bag X52, Malmesbury, 7300', current_timestamp(), '1', current_timestamp(), NULL);



CREATE TABLE `tbl_project_stg3_keys` (`project_id` INT NOT NULL , `key_name` VARCHAR(100) NULL DEFAULT NULL , `key_value` VARCHAR(100) NULL DEFAULT NULL , `sub_type` VARCHAR(100) NULL DEFAULT NULL , `sub_type_name` VARCHAR(200) NULL DEFAULT NULL , `sub_sub_type` VARCHAR(100) NULL DEFAULT NULL , `sub_sub_type_name` VARCHAR(200) NULL DEFAULT NULL , `free_text` TEXT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `credted_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg3_keys` ADD PRIMARY KEY(`project_id`, `key_name`, `key_value`);

CREATE TABLE `tbl_project_stg3` (`project_id` INT NOT NULL , `background_justification` TEXT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL , `updation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `updated_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg3` ADD PRIMARY KEY(`project_id`);

CREATE TABLE `tbl_project_stg3_geolocations` (`row_id` INT NOT NULL AUTO_INCREMENT , `project_id` INT NOT NULL , `region_id` INT NULL DEFAULT NULL , `municipalies_id` INT NULL DEFAULT NULL , `communities_id` INT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL , PRIMARY KEY (`row_id`)) ENGINE = InnoDB;

CREATE TABLE `tbl_communities` (`c_id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , PRIMARY KEY (`c_id`)) ENGINE = InnoDB;

ALTER TABLE `tbl_municipality` CHANGE `m_id` `m_id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `tbl_regions` CHANGE `region_id` `region_id` INT(11) NOT NULL AUTO_INCREMENT;

INSERT INTO `tbl_communities` (`c_id`, `name`) 
VALUES  (NULL, 'Community1'), (NULL, 'Community2'),
        (NULL, 'Community3'), (NULL, 'Community4');

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('198', '43', '4', 'sector_specific_analysis_3', 'Other', 'other-ml', '4', '', 'active', '2025-08-02 15:21:58', '1', NULL, NULL);

ALTER TABLE `tbl_project_stg3_keys` CHANGE `credted_by` `created_by` INT(11) NULL DEFAULT NULL;

ALTER TABLE `tbl_project_stg3` ADD `feasibility_study_gov_policy` VARCHAR(15) NULL AFTER `background_justification`;

ALTER TABLE `tbl_project_stg3` CHANGE `feasibility_study_gov_policy` `feasibility_study_gov_policy` VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'no';


UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\": \"Agri-processing: Support for infrastructure, logistics, and export readiness.\",\"sub_types\": [] },{\"sub_type_2\": \"Green Economy: Incentives for renewable energy, water efficiency, and waste-to-energy projects.\",\"sub_types\": [] },{\"sub_type_3\": \"Technology and Innovation: Support for ICT hubs, fintech, and digital startups.\",\"sub_types\": [] },{\"sub_type_4\": \"Tourism and Film: Rebates and grants for film production and tourism infrastructure.\",\"sub_types\": [] }]' WHERE `tbl_lookup_detail`.`optionid` = 166;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\": \"Municipal Investment Readiness Programme: Helps municipalities prepare for and attract investment.\",\"sub_types\": [] },{\"sub_type_2\": \"Sector IQ: Provides data-driven insights to guide investment decisions.\",\"sub_types\": [] },{\"sub_type_3\": \"Film Incentive Scheme: Assesses and supports the impact of film-related investments.\",\"sub_types\": [] }]' WHERE `tbl_lookup_detail`.`optionid` = 167;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\": \"Wesgro: The official investment promotion agency offers:\",\"sub_types\": [{\"sub_sub_type_1_1\": \"Site selection assistance\"},{\"sub_sub_type_1_2\": \"Regulatory guidance\"}, { \"sub_sub_type_1_3\": \"Market intelligence\" }, { \"sub_sub_type_1_4\": \"Access to government incentives\" }]}, {\"sub_type_2\": \"Investment Summits: Hosted by the Premier to connect investors with opportunities and funding partners\",\"sub_types\": [] }]' WHERE `tbl_lookup_detail`.`optionid` = 168;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\": \"12I Tax Allowance: For industrial projects.\",\"sub_types\": [] }, {\"sub_type_2\": \"SEZ Incentives: For businesses in Special Economic Zones.\",\"sub_types\": [] }, {\"sub_type_3\": \"Export Marketing and Investment Assistance (EMIA): Supports international marketing efforts.\",\"sub_types\": [] }, {\"sub_type_4\": \"Black Industrialists Scheme: Funding and support for black-owned manufacturing businesses.\",\"sub_types\": [] }]' WHERE `tbl_lookup_detail`.`optionid` = 169;

---
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\":\"Agri-processing: Support for infrastructure, logistics, and export readiness.\",\"sub_types\":[]},{\"sub_type_2\":\"Green Economy: Incentives for renewable energy, water efficiency, and waste-to-energy projects.\",\"sub_types\":[]},{\"sub_type_3\":\"Technology and Innovation: Support for ICT hubs, fintech, and digital startups.\",\"sub_types\":[]},{\"sub_type_4\":\"Tourism and Film: Rebates and grants for film production and tourism infrastructure.\",\"sub_types\":[]},{\"sub_type_5\":\"Other\",\"sub_types\":[]}]' WHERE `tbl_lookup_detail`.`optionid` = 166;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\":\"Municipal Investment Readiness Programme: Helps municipalities prepare for and attract investment.\",\"sub_types\":[]},{\"sub_type_2\":\"Sector IQ: Provides data-driven insights to guide investment decisions.\",\"sub_types\":[]},{\"sub_type_3\":\"Film Incentive Scheme: Assesses and supports the impact of film-related investments.\",\"sub_types\":[]},{\"sub_type_4\":\"Other\",\"sub_types\":[]}]' WHERE `tbl_lookup_detail`.`optionid` = 167;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\":\"Wesgro: The official investment promotion agency offers:\",\"sub_types\":[{\"sub_sub_type_1_1\":\"Site selection assistance\"},{\"sub_sub_type_1_2\":\"Regulatory guidance\"},{\"sub_sub_type_1_3\":\"Market intelligence\"},{\"sub_sub_type_1_4\":\"Access to government incentives\"},{\"sub_sub_type_1_5\":\"Other\"}]},{\"sub_type_2\":\"Investment Summits: Hosted by the Premier to connect investors with opportunities and funding partners\",\"sub_types\":[]},{\"sub_type_3\":\"Other\",\"sub_types\":[]}]' WHERE `tbl_lookup_detail`.`optionid` = 168;
UPDATE `tbl_lookup_detail` SET `item_data` = '[{\"sub_type_1\":\"12I Tax Allowance: For industrial projects.\",\"sub_types\":[]},{\"sub_type_2\":\"SEZ Incentives: For businesses in Special Economic Zones.\",\"sub_types\":[]},{\"sub_type_3\":\"Export Marketing and Investment Assistance (EMIA): Supports international marketing efforts.\",\"sub_types\":[]},{\"sub_type_4\":\"Black Industrialists Scheme: Funding and support for black-owned manufacturing businesses.\",\"sub_types\":[]},{\"sub_type_5\":\"Other\",\"sub_types\":[]}]' WHERE `tbl_lookup_detail`.`optionid` = 169;


INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('45', 'project_type', 'Project Type', 'project-type-ml', 'project_type', '1', 'No', '2025-08-06 17:15:36', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('202', '45', '1', 'project_type_1', 'Project Routine', 'project-routine-ml', '1', '', 'active', '2025-08-04 17:15:37', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('203', '45', '2', 'project_type_2', 'New Project', 'new-project-ml', '2', '', 'active', '2025-08-04 17:15:37', '1', NULL, NULL);

---

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('46', 'project_size', 'Project Size', 'project-size-ml', 'project_size', '1', 'No', '2025-08-06 17:15:36', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('204', '46', '1', 'project_size_1', 'Small to Medium - Below 30 Mill', 'small-medium-ml', '1', '', 'active', '2025-08-04 17:15:37', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('205', '46', '2', 'project_size_2', 'Large - Total of 30 Mill or more', 'large-ml', '2', '', 'active', '2025-08-04 17:15:37', '1', NULL, NULL);

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('206', '46', '3', 'project_size_3', 'Mega - Total of R1 Bill or more', 'mega-ml', '3', '', 'active', '2025-08-04 17:15:37', '1', NULL, NULL);


ALTER TABLE `tbl_project_stg3` ADD `project_type` VARCHAR(100) NULL DEFAULT NULL AFTER `feasibility_study_gov_policy`, ADD `project_size` VARCHAR(100) NULL DEFAULT NULL AFTER `project_type`;

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Prefeasibility Report', 'project', '2025-08-06 23:38:13', NULL, NULL, NULL);

ALTER TABLE `tbl_project_stg3_keys`
ADD `row_id` int NOT NULL AUTO_INCREMENT UNIQUE FIRST;

ALTER TABLE `tbl_project_stg1_keys` CHANGE `free_text` `free_text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

drop PROCEDURE if exists insert_project_questions;
DELIMITER $$

CREATE PROCEDURE insert_project_questions (
    IN p_project_id INT,
    IN p_user_id INT
)
BEGIN
    INSERT INTO tbl_project_questions (
        project_id,
        user_id,
        q_id,
        score,
        weight,
        weighted_score,
        attempted,
        comment,
        eval_status,
        creation_date,
        created_by
    )
    SELECT
        p_project_id AS project_id,
        p_user_id AS user_id,
        q_id,
        0 AS score,
        weight,
        0 AS weighted_score,
        0 AS attempted,
        NULL AS comment,
        'pending' AS eval_status,
        NOW() AS creation_date,
        created_by
    FROM tbl_panoptic_questions
    WHERE is_active = 1;
END$$

DELIMITER ;

INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('386', '6', '5', 'project_objectives_5', 'Add Project specific objectives', 'add-project-specific-objectives-ml', '12', '', 'active', '2025-07-09 17:22:30', '1', NULL, NULL);
UPDATE `tbl_lookup_detail` SET `sequence` = '4' WHERE `tbl_lookup_detail`.`optionid` = 36;
UPDATE `tbl_lookup_detail` SET `sequence` = '5' WHERE `tbl_lookup_detail`.`optionid` = 386;

ALTER TABLE `tbl_project_stg0_objective` CHANGE `focusarea_datakey` `focusarea_datakey` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE `tbl_project_stg3_keys` CHANGE `sub_type` `sub_type` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, CHANGE `sub_type_name` `sub_type_name` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, CHANGE `sub_sub_type` `sub_sub_type` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, CHANGE `sub_sub_type_name` `sub_sub_type_name` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `tbl_project_stg4_option_analysis` (
  `row_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `option_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `section_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `question` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `section_subtype_key` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`section_subtype_key`)),
  `appraisal_process_stage_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `updation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`row_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DELETE FROM `tbl_project_stg4_option_analysis`;
CREATE TABLE `tbl_project_stg4_keys` (`project_id` INT(11) NOT NULL , `key_name` VARCHAR(100) NOT NULL , `key_value` VARCHAR(100) NOT NULL , `free_text` TEXT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg4_keys` ADD PRIMARY KEY(`project_id`, `key_name`, `key_value`);
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('6', 'tbl_project_stg4_option_analysis', '1');

ALTER TABLE `tbl_project_stg4_option_analysis` CHANGE `appraisal_process_stage_key` `appraisal_process_stage_key` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;


DROP PROCEDURE IF EXISTS `sp_insertoptionsinstg4`;
DELIMITER $$
CREATE PROCEDURE `sp_insertoptionsinstg4`(
    IN p_project_id INT,
    IN p_created_by INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_section_key VARCHAR(100);
    DECLARE v_display_name VARCHAR(500);
    DECLARE v_item_data JSON;
    DECLARE v_question VARCHAR(500);
    DECLARE v_option_num INT DEFAULT 1;

    DECLARE v_max_id INT;
    DECLARE v_next_id INT;

    -- Cursor to get all active sections for keyid=47
    DECLARE cur CURSOR FOR
        SELECT d.data_key, d.display_name, d.item_data
        FROM tbl_lookup_detail d
        WHERE d.keyid = 47 AND d.is_active = 'active'
        ORDER BY d.optionid;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Get current max_id for tbl_project_stg4_option_analysis
    SELECT max_id INTO v_max_id
    FROM tbl_maxids
    WHERE tbl_name = 'tbl_project_stg4_option_analysis';

    SET v_next_id = v_max_id;

    WHILE v_option_num <= 3 DO
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO v_section_key, v_display_name, v_item_data;
            IF done THEN
                LEAVE read_loop;
            END IF;

            -- Extract description from item_data JSON
            SET v_question = JSON_UNQUOTE(JSON_EXTRACT(v_item_data, '$.description'));

            -- Fallback to display_name if description missing
            IF v_question IS NULL OR v_question = '' THEN
                SET v_question = v_display_name;
            END IF;

            -- Increment our manual ID
            SET v_next_id = v_next_id + 1;

            -- Insert row with manual row_id
            INSERT INTO tbl_project_stg4_option_analysis
                (row_id, project_id, option_name, section_key, question, appraisal_process_stage_key, created_by)
            VALUES
                (v_next_id, p_project_id, CONCAT('Option', v_option_num), v_section_key, v_question, NULL, p_created_by);

        END LOOP;
        CLOSE cur;

        -- Reset handler flag for next loop
        SET done = FALSE;
        SET v_option_num = v_option_num + 1;
    END WHILE;

    -- Update tbl_maxids with the new max_id
    UPDATE tbl_maxids
    SET max_id = v_next_id
    WHERE tbl_name = 'tbl_project_stg4_option_analysis';

END$$
DELIMITER ;

------ on dev server pending from here.


ALTER TABLE `tbl_project_stg4_cost_benefit_analysis` DROP PRIMARY KEY;
ALTER TABLE `tbl_project_stg4_cost_benefit_analysis` ADD PRIMARY KEY(`row_id`);
ALTER TABLE `tbl_project_stg4_option_analysis` CHANGE `section_subtype_key` `section_subtype_key` JSON NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg4_option_analysis` CHANGE `section_subtype_key` `section_subtype_key` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg4_option_analysis` CHANGE `section_subtype_key` `section_subtype_key` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;


ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` DROP PRIMARY KEY;
ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` ADD `row_id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`row_id`);
ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` CHANGE `row_id` `row_id` INT(11) NOT NULL;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('7', 'tbl_project_stg4_cost_effectiveness_analysis', '1');


ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` ADD `project_component_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `project_component`;
ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` ADD `intervention_type_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `intervention_type`;
ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` ADD `expected_outcome_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `expected_outcome`;
ALTER TABLE `tbl_project_stg4_cost_effectiveness_analysis` ADD `effectiveness_metric_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `effectiveness_metric`;

ALTER TABLE `tbl_project_stg4_risk_evaluation` DROP PRIMARY KEY;
ALTER TABLE `tbl_project_stg4_risk_evaluation` ADD `row_id` INT NOT NULL FIRST;
ALTER TABLE `tbl_project_stg4_risk_evaluation` ADD PRIMARY KEY(`row_id`);

ALTER TABLE `tbl_project_stg4_risk_evaluation` ADD `risk_category_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `risk_category`;
ALTER TABLE `tbl_project_stg4_risk_evaluation` ADD `risk_timeframe_other` VARCHAR(1000) NULL DEFAULT NULL AFTER `risk_timeframe`;

INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('8', 'tbl_project_stg4_risk_evaluation', '1');


INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('9', 'tbl_project_stg4_economic_multipliers', '1');
CREATE TABLE `tbl_project_stg4_highlevel_cost` (`row_id` INT NOT NULL , `project_id` INT NOT NULL , `cost_category` VARCHAR(100) NOT NULL , `sub_type` VARCHAR(100) NOT NULL , `sub_type_name` VARCHAR(500) NOT NULL , `estimated_cost` FLOAT(10,2) NULL DEFAULT NULL , `notes` INT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL , `updation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `updation_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg4_highlevel_cost` ADD `cost_ctegory_name` VARCHAR(500) NULL AFTER `cost_category`;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('10', 'tbl_project_stg4_highlevel_cost', '0');
ALTER TABLE `tbl_project_stg4_highlevel_cost` CHANGE `notes` `notes` TEXT NULL DEFAULT NULL;


drop PROCEDURE if EXISTS sp_insertoptionsinstg4;

DELIMITER $$
CREATE PROCEDURE `sp_insertoptionsinstg4`(
    IN p_project_id INT,
    IN p_created_by INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_section_key VARCHAR(100);
    DECLARE v_display_name VARCHAR(500);
    DECLARE v_item_data JSON;
    DECLARE v_question VARCHAR(500);
    DECLARE v_option_num INT DEFAULT 1;

    DECLARE v_max_id INT;
    DECLARE v_next_id INT;

    -- Cursor to get all active sections for keyid=47
    DECLARE cur CURSOR FOR
        SELECT d.data_key, d.display_name, d.item_data
        FROM tbl_lookup_detail d
        WHERE d.keyid = 47 AND d.is_active = 'active'
        ORDER BY d.optionid;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Get current max_id for tbl_project_stg4_option_analysis
    SELECT max_id INTO v_max_id
    FROM tbl_maxids
    WHERE tbl_name = 'tbl_project_stg4_option_analysis';

    SET v_next_id = v_max_id;

    WHILE v_option_num <= 3 DO
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO v_section_key, v_display_name, v_item_data;
            IF done THEN
                LEAVE read_loop;
            END IF;

            -- Extract description from item_data JSON
            SET v_question = JSON_UNQUOTE(JSON_EXTRACT(v_item_data, '$.description'));

            -- Fallback to display_name if description missing
            IF v_question IS NULL OR v_question = '' THEN
                SET v_question = v_display_name;
            END IF;

            -- Increment our manual ID
            SET v_next_id = v_next_id + 1;

            -- Insert row with manual row_id
            INSERT INTO tbl_project_stg4_option_analysis
                (row_id, project_id, option_name, section_key, question, appraisal_process_stage_key, created_by)
            VALUES
                (v_next_id, p_project_id, CONCAT('Option', v_option_num), v_section_key, v_question, NULL, p_created_by);

        END LOOP;
        CLOSE cur;

        -- Reset handler flag for next loop
        SET done = FALSE;
        SET v_option_num = v_option_num + 1;
    END WHILE;

    -- Update tbl_maxids with the new max_id
    UPDATE tbl_maxids
    SET max_id = v_next_id
    WHERE tbl_name = 'tbl_project_stg4_option_analysis';
	
    -- Insert empty row in stg4 table.
	INSERT INTO `tbl_project_stg4`(`project_id`, `preferred_option_name`, `reason_text`, `net_benefit_amount`, `net_benefit_pv`, `creation_date`, `created_by`) 
    VALUES (p_project_id,NULL,NULL,0,0,NOW(),p_created_by);
END$$
DELIMITER ;

ALTER TABLE `tbl_project_stg4_highlevel_cost` ADD PRIMARY KEY(`row_id`);


ALTER TABLE `tbl_project_stg4_cost_benefit_analysis`
	ADD COLUMN `other_category_desc` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `category`;
	
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES (11, 'tbl_project_stg4_cost_benefit_analysis', 0);

CREATE TABLE `tbl_project_stg4_macro_economic_benefits` (
    `row_id` INT(11) NOT NULL,
	`project_id` INT(11) NOT NULL,
	`benefit_category` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`description` VARCHAR(500) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`estimated_impact` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`sectoral_relevance` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`policy_alignment` JSON NULL,
	`creation_date` DATETIME NOT NULL DEFAULT current_timestamp(),
    `created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NOT NULL DEFAULT current_timestamp(),
	`updated_by` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`row_id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

CREATE TABLE `tbl_project_stg4_revenue_model` (
    `row_id` INT(11) NOT NULL,
	`project_id` INT(11) NOT NULL,
	`revenue_model_category` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`description` VARCHAR(500) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`specify` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`specify_other` VARCHAR(1000) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`creation_date` DATETIME NOT NULL DEFAULT current_timestamp(),
    `created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NOT NULL DEFAULT current_timestamp(),
	`updated_by` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`row_id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

ALTER TABLE `tbl_project_stg4_option_analysis` ADD `other_remarks` VARCHAR(1000) NULL DEFAULT NULL AFTER `section_subtype_key`;

ALTER TABLE `tbl_project_stg4_macro_economic_benefits` CHANGE `policy_alignment` `policy_alignment` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg4_macro_economic_benefits` ADD `benefit_category_other` VARCHAR(500) NULL DEFAULT NULL AFTER `benefit_category`;
ALTER TABLE `tbl_project_stg4_macro_economic_benefits` ADD `estimated_impact_other` VARCHAR(500) NULL DEFAULT NULL AFTER `estimated_impact`;
ALTER TABLE `tbl_project_stg4_macro_economic_benefits` ADD `sectoral_relevance_other` VARCHAR(500) NULL DEFAULT NULL AFTER `sectoral_relevance`;
ALTER TABLE `tbl_project_stg4_macro_economic_benefits` ADD `policy_alignment_other` VARCHAR(500) NULL DEFAULT NULL AFTER `policy_alignment`;
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('23', 'tbl_project_stg4_macro_economic_benefits', '1');
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES (12, 'tbl_project_stg4_revenue_model', 0);

CREATE TABLE `tbl_elevator_pitch` (
	`row_id` INT(11) NULL DEFAULT NULL,
	`project_id` INT(11) NULL DEFAULT NULL,
	`background_justification` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`objectives_focus_areas` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`project_summary_editor` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`project_status` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`project_size` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`project_type` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`estimated_cost` FLOAT NULL DEFAULT NULL,
	`financial_needs` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`private_sector_opportunities` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`estimated_years` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`estimated_months` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`estimated_project_start_date` DATE NULL DEFAULT NULL,
	`creation_date` DATETIME NULL DEFAULT current_timestamp(),
	`created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NULL DEFAULT NULL ON UPDATE current_timestamp(),
	`updated_by` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `tbl_elevator_pitch_key_objective` (
	`row_id` INT(11) NOT NULL,
	`project_id` INT(11) NOT NULL,
	`objective_datakey` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`focusarea_datakey` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`objective_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`is_active` TINYINT(4) NOT NULL DEFAULT '1',
	`creation_date` DATETIME NULL DEFAULT NULL ON UPDATE current_timestamp(),
	`created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`row_id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;


CREATE TABLE `tbl_elevator_pitch_key_parties` (
	`project_id` INT(11) NOT NULL,
	`key_name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`key_value` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`free_text` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`creation_date` DATETIME NULL DEFAULT NULL ON UPDATE current_timestamp(),
	`created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`project_id`, `key_name`, `key_value`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `tbl_elevator_pitch_timeline` (
	`ept_id` INT(11) NULL DEFAULT NULL,
	`row_id` INT(11) NULL DEFAULT NULL,
	`title` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`description` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`creation_date` DATETIME NULL DEFAULT NULL ON UPDATE current_timestamp(),
	`created_by` INT(11) NULL DEFAULT NULL,
	`updation_date` DATETIME NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

DELIMITER //
CREATE PROCEDURE `sp_insert_elevator_pitch`(
    IN `p_user_id` INT,
    IN `p_project_id` INT
)
BEGIN
    -- Declare variables for stage0/3 data
    DECLARE v_row_id INT;
    DECLARE v_background_justification TEXT;
    DECLARE v_estimated_years VARCHAR(50);
    DECLARE v_estimated_months VARCHAR(50);
    DECLARE v_estimated_start_date DATE;
    DECLARE v_project_type VARCHAR(100);
    DECLARE v_project_size VARCHAR(100);
    DECLARE v_estimated_cost FLOAT;
 
    -- Get next row_id
    SELECT IFNULL(MAX(row_id), 0) + 1
    INTO v_row_id
    FROM tbl_elevator_pitch;
 
    -- Fetch data for elevator pitch (stage0 + stage3)
    SELECT
        tps0.background_justification,
        tps0.estimated_project_duration_years,
        tps0.estimated_project_duration_months,
        tps0.estimated_project_start_date,
        tps3.project_type,
        tps3.project_size,
        tps0.ballpark_project_cost
    INTO
        v_background_justification,
        v_estimated_years,
        v_estimated_months,
        v_estimated_start_date,
        v_project_type,
        v_project_size,
        v_estimated_cost
    FROM tbl_projects tp
    LEFT JOIN tbl_project_stg0 tps0
        ON tp.project_id = tps0.project_id
    LEFT JOIN tbl_project_stg3 tps3
        ON tp.project_id = tps3.project_id
    LEFT JOIN tbl_project_users tpu
        ON tps0.project_id = tpu.project_id
    WHERE tpu.user_id = p_user_id
      AND tp.project_id = p_project_id
      AND tps0.is_active = 1;
 
    -- Insert main elevator pitch row
    INSERT INTO tbl_elevator_pitch (
        row_id,
        project_id,
        background_justification,
        project_size,
        project_type,
        estimated_cost,
        estimated_years,
        estimated_months,
        estimated_project_start_date,
        creation_date,
        created_by
    ) VALUES (
        v_row_id,
        p_project_id,
        v_background_justification,
        v_project_size,
        v_project_type,
        v_estimated_cost,
        v_estimated_years,
        v_estimated_months,
        v_estimated_start_date,
        NOW(),
        p_user_id
    );
 
    -- Insert stage1 keys directly (all rows at once)
    INSERT INTO tbl_elevator_pitch_key_parties (
        project_id,
        key_name,
        key_value,
        free_text,
        creation_date,
        created_by
    )
    SELECT
        p_project_id,
        psk.key_name,
        psk.key_value,
        psk.free_text,
        NOW(),
        p_user_id
    FROM tbl_project_stg1_keys psk
    WHERE psk.project_id = p_project_id;
    
    INSERT INTO tbl_elevator_pitch_key_objective (
          row_id,
        project_id,
        objective_datakey,
        focusarea_datakey,
        objective_name,
        creation_date,
        created_by
    )
    SELECT
          v_row_id,
        p_project_id,
        pso.objective_datakey,
        pso.focusarea_datakey,
        pso.objective_name,
        NOW(),
        p_user_id
    FROM tbl_project_stg0_objective pso
    WHERE pso.project_id = p_project_id;
 
END//
DELIMITER ;

INSERT INTO `tbl_lookup` (`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `creation_date`, `created_by`) VALUES (69, 'advanced_analysis', 'Advanced Analysis', 'advanced-analysis-ml', 'Advanced Analysis', 1, NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (387, 69, '1', 'advanced_analysis_1', 'Stakeholder Impact', 'stakeholder-impact-ml', 1, '{"description":"Who is affected and how ?"}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (388, 69, '2', 'advanced_analysis_2', 'Sensitivity Analysis', 'sensitivity-analysis-ml', 2, '{"description":"How changes in assumptions affect outcomes ?"}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (389, 69, '3', 'advanced_analysis_3', 'Non-Monetary Impacts', 'non-monetary-impacts-ml', 3, '{"description":"Qualitative benefits or costs (e.g., employee satisfaction, brand reputation)"}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (390, 69, '4', 'advanced_analysis_4', 'Environmental/Social Impact', 'environmental-social-impact-ml', 4, '{"description":"For sustainability-focused projects"}', 'active', NOW(), 1);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`) VALUES (391, 69, '5', 'advanced_analysis_5', 'Other', 'other-ml', 5, '', 'active', NOW(), 1);

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Technical Feasibility', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Master Plan Integration', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Alternative Evaluation', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Infrastructure Plan', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);

UPDATE `tbl_maxids` SET `id` = '13' WHERE `tbl_maxids`.`id` = 23;

ALTER TABLE `tbl_project_stg4` ADD `risk_assessment` TEXT NULL DEFAULT NULL AFTER `net_benefit_pv`, ADD `risk_sharing_strategy` TEXT NULL DEFAULT NULL AFTER `risk_assessment`;
ALTER TABLE `tbl_project_stg4` ADD `technical_text` TEXT NULL DEFAULT NULL AFTER `risk_sharing_strategy`, ADD `masterplan_text` TEXT NULL DEFAULT NULL AFTER `technical_text`, ADD `alternative_text` TEXT NULL DEFAULT NULL AFTER `masterplan_text`, ADD `infrastructure_text` TEXT NULL DEFAULT NULL AFTER `alternative_text`;

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Risk Assessment', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Risk Sharing Strategy', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Methodology', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Timeline and Budget', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
ALTER TABLE `tbl_project_stg4` ADD `bidding_process` TEXT NULL DEFAULT NULL AFTER `infrastructure_text`, ADD `supplier_selection` TEXT NULL DEFAULT NULL AFTER `bidding_process`;
ALTER TABLE `tbl_project_stg4` ADD COLUMN `scope_and_objective` TEXT NULL DEFAULT NULL AFTER `infrastructure_text`;
ALTER TABLE `tbl_project_stg4` ADD COLUMN `methodology` TEXT NULL DEFAULT NULL AFTER `scope_and_objective`;
 

ALTER TABLE `tbl_project_stg4` ADD `investor_enganement` TEXT NULL DEFAULT NULL AFTER `supplier_selection`, ADD `community_participation` TEXT NULL DEFAULT NULL AFTER `investor_enganement`, ADD `gov_and_regulatory` INT NULL DEFAULT NULL AFTER `community_participation`;

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Investor Engagement strategies', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Community Participation', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Government & Regulatory Interactions', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);

ALTER TABLE `tbl_project_stg4` CHANGE `gov_and_regulatory` `gov_and_regulatory` TEXT NULL DEFAULT NULL;


ALTER TABLE `tbl_project_stg4`
	ADD COLUMN `procurement_strategy` TEXT NULL DEFAULT NULL AFTER `methodology`;
ALTER TABLE `tbl_project_stg4_keys`
	ADD COLUMN `sub_type` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `key_value`,
	ADD COLUMN `sub_type_name` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `sub_type`,
	ADD COLUMN `sub_sub_type` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `sub_type_name`,
	ADD COLUMN `sub_sub_type_name` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `sub_sub_type`;


CREATE TABLE `tbl_project_stg4_legal_regulatory` ( `row_id` int(11) NOT NULL, `project_id` int(11) NOT NULL, `key_name` varchar(100) NOT NULL, `key_value` varchar(100) NOT NULL, `sub_type` varchar(500) DEFAULT NULL, `sub_type_name` varchar(1000) DEFAULT NULL, `sub_sub_type` varchar(500) DEFAULT NULL, `sub_sub_type_name` varchar(1000) DEFAULT NULL, `free_text` text DEFAULT NULL, `creation_date` datetime NOT NULL DEFAULT current_timestamp(), `created_by` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `tbl_project_stg4_legal_regulatory` ADD PRIMARY KEY (`row_id`);
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('14', 'tbl_project_stg4_legal_regulatory', '1');

CREATE TABLE `tbl_project_stg4_demand_forecasting` 
( 
`row_id` int(11) NOT NULL, 
`project_id` int(11) NOT NULL, 
`key_name` varchar(100) NOT NULL, 
`key_value` varchar(100) NOT NULL, 
`sub_type` varchar(500) DEFAULT NULL, 
`sub_type_name` varchar(1000) DEFAULT NULL, 
`sub_sub_type` varchar(500) DEFAULT NULL, 
`sub_sub_type_name` varchar(1000) DEFAULT NULL, 
`free_text` text DEFAULT NULL, 
`creation_date` datetime NOT NULL DEFAULT current_timestamp(), 
`created_by` int(11) DEFAULT NULL ) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;
 
ALTER TABLE `tbl_project_stg4_demand_forecasting` ADD PRIMARY KEY (`row_id`); 
 
INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('15', 'tbl_project_stg4_demand_forecasting', '0');
 
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Prefeasibility Executive Report', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);
-- INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Feasibility Report', 'project', '2025-08-06 23:38:13', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES (NULL, 'Feasibility Executive Report', 'project', '2025-08-13 23:38:13', NULL, NULL, NULL);


ALTER TABLE `tbl_sectors` ADD `sequence` INT NOT NULL DEFAULT '0' AFTER `parent_id`;
UPDATE `tbl_sectors` SET `sequence` = '1' WHERE `tbl_sectors`.`sector_id` = 6;
UPDATE `tbl_sectors` SET `sequence` = '2' WHERE `tbl_sectors`.`sector_id` = 3;
UPDATE `tbl_sectors` SET `sequence` = '3' WHERE `tbl_sectors`.`sector_id` = 9;
UPDATE `tbl_sectors` SET `sequence` = '4' WHERE `tbl_sectors`.`sector_id` = 11;
UPDATE `tbl_sectors` SET `sequence` = '5' WHERE `tbl_sectors`.`sector_id` = 10;



UPDATE `tbl_lookup_detail` SET `display_name` = 'Director: Special Projects' WHERE `tbl_lookup_detail`.`optionid` = 37;
UPDATE `tbl_lookup_detail` SET `display_name_ml` = 'director-special_projects-ml' WHERE `tbl_lookup_detail`.`optionid` = 37;
UPDATE `tbl_lookup_detail` SET `display_name` = 'Chief Director: Road Contracts (Act.)', `display_name_ml` = 'chief-director-road-contracts-ml' WHERE `tbl_lookup_detail`.`optionid` = 38;
UPDATE `tbl_lookup_detail` SET `display_name` = 'Project Manager, Regional Support Services', `display_name_ml` = 'project-manager-regional-support-services-ml' WHERE `tbl_lookup_detail`.`optionid` = 39;
UPDATE `tbl_lookup_detail` SET `display_name` = 'Director: Project Management Office (Acting)', `display_name_ml` = 'director-project-management-office-acting-ml' WHERE `tbl_lookup_detail`.`optionid` = 40;
UPDATE `tbl_lookup_detail` SET `display_name` = 'Director: Biodiversity & Coastal Management', `display_name_ml` = 'director-biodiversity-coastal-management-ml' WHERE `tbl_lookup_detail`.`optionid` = 41;
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('394', '8', '6', 'user_designation_6', 'Senior Manager: Coordination of Industrial Development', 'senior-manager-coordination-of-industrial-development-ml', '6', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('395', '8', '7', 'user_designation_7', 'Municipal Manager', 'municipal-manager-ml', '7', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('396', '8', '8', 'user_designation_8', 'Deputy Director: Connected Business', 'deputy-director-connected-business-ml', '8', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('397', '8', '9', 'user_designation_9', 'Deputy Director: Catalytic Infrastructure', 'deputy-director-catalytic-infrastructure-ml', '9', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('398', '8', '10', 'user_designation_10', 'Director: Western Cape Archives and Records Service', 'director-western-cape-archives-and-records-service-ml', '10', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('399', '8', '11', 'user_designation_11', 'Chief Director: Sport & Recreation', 'chief-director-sport-recreation-ml', '11', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);
INSERT INTO `tbl_lookup_detail` (`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('400', '8', '12', 'user_designation_12', 'Chief Director: Road Design (Act)', 'chief-director-road-design-ml', '12', '', 'active', '2025-08-25 16:16:46', '1', NULL, NULL);


UPDATE `tbl_departments` SET `department_name` = 'Department Of Infrastructure' WHERE `tbl_departments`.`department_id` = 4;
UPDATE `tbl_departments` SET `is_active` = '0' WHERE `tbl_departments`.`department_id` = 15;
INSERT INTO `tbl_departments` (`department_id`, `department_name`, `department_short_name`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('17', 'Western Cape Government', '', '1', '2025-08-25 13:23:48', '1', NULL, NULL);
INSERT INTO `tbl_departments` (`department_id`, `department_name`, `department_short_name`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('18', 'Other', '', '1', '2025-08-25 13:23:48', '1', NULL, NULL);



INSERT INTO `tbl_user_master` (`user_id`, `email`, `user_name`, `sso_id`, `title`, `first_name`, `last_name`, `designation_datakey`, `mobile`, `device_token`, `password`, `pwd_expiry_dt`, `pwd_expiry_rule`, `pin`, `role_id`, `isAdmin`, `is_active`, `isActive`, `gender`, `reg_id`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('157', 'Ilani.Heyns@pet.com', NULL, '0', NULL, 'Ilani', 'Heyns', NULL, NULL, NULL, '5c428d8875d2948607f3e3fe134d71b4', NULL, '{\"days\":120}', NULL, '2', NULL, '1', '0', 'M', '0', NULL, NULL, NULL, NULL);
INSERT INTO `tbl_register` (`reg_id`, `reg_type`, `user_id`, `request_ip`, `reg_status`, `reg_code`, `request_time`, `resend_time`, `resend_count`, `verify_time`, `verify_count`, `reg_log`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('187', 'register', '157', NULL, 'Success', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-05-21 05:15:25', '3', NULL, NULL);
UPDATE `tbl_user_master` SET `designation_datakey` = 'user_designation_4' WHERE `tbl_user_master`.`user_id` = 157;


-------=======on QA PENDING FROM HERE
CREATE TABLE `tbl_project_departments` (`project_id` INT NOT NULL , `department_id` INT NOT NULL , `creation_date` DATETIME NOT NULL , `created_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_departments` ADD PRIMARY KEY(`project_id`, `department_id`);
ALTER TABLE `tbl_project_departments` CHANGE `creation_date` `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;


CREATE TABLE `tbl_project_stg4_departments` (`project_id` INT NOT NULL , `department_id` INT NOT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg4_departments` ADD PRIMARY KEY(`project_id`, `department_id`);


ALTER TABLE `tbl_project_departments` ADD `other_department` VARCHAR(1000) NULL DEFAULT NULL AFTER `department_id`;
ALTER TABLE `tbl_project_stg4_departments` ADD `other_department` VARCHAR(1000) NULL DEFAULT NULL AFTER `department_id`;

ALTER TABLE `tbl_project_stg2` ADD `erf_number` VARCHAR(100) NULL DEFAULT NULL AFTER `geo_long`;


ALTER TABLE `tbl_project_stg3_geolocations` 
CHANGE `region_id` `region_id` VARCHAR(1000) NULL DEFAULT NULL, 
CHANGE `municipalies_id` `municipalies_id` VARCHAR(1000) NULL DEFAULT NULL, 
CHANGE `communities_id` `communities_id` VARCHAR(1000) NULL DEFAULT NULL;

--Till here Forwarded to bharat and tejas and updated on dev server
--------------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_deleteprojectusingprojectid $$
CREATE PROCEDURE sp_deleteprojectusingprojectid (
    IN p_project_id INT
)
BEGIN
    START TRANSACTION;

    -- Stage4
    DELETE FROM tbl_project_stg4 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_cost_benefit_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_cost_effectiveness_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_demand_forecasting WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_economic_multipliers WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_highlevel_cost WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_legal_regulatory WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_macro_economic_benefits WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_option_analysis WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_revenue_model WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_risk_evaluation WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg4_departments WHERE project_id = p_project_id;
    
    -- Stage3
    DELETE FROM tbl_project_stg3 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_geolocations WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg3_keys WHERE project_id = p_project_id;

    -- Stage2
    DELETE FROM tbl_project_stg2 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg2_keys WHERE project_id = p_project_id;

    -- Stage1
    DELETE FROM tbl_project_stg1 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg1_keys WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg1_outcomes WHERE project_id = p_project_id;
    DELETE FROM tbl_project_departments WHERE project_id = p_project_id;

    -- Stage0
    DELETE FROM tbl_project_stg0 WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stg0_objective WHERE project_id = p_project_id;

    -- documents
    DELETE d
    FROM tbl_documents d
    INNER JOIN tbl_project_docs pd ON d.doc_id = pd.doc_id
    WHERE pd.pr_id = p_project_id;

    DELETE FROM tbl_project_docs WHERE pr_id = p_project_id;

    -- Elevator pitch
    DELETE ept, ep FROM tbl_elevator_pitch ep
    LEFT JOIN tbl_elevator_pitch_timeline ept ON ept.row_id = ep.row_id
    WHERE ep.project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch_key_parties WHERE project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch_key_objective WHERE project_id = p_project_id;
    DELETE FROM tbl_elevator_pitch WHERE project_id = p_project_id;

    -- evaluators
    DELETE FROM tbl_project_evaluators WHERE project_id = p_project_id;
    DELETE FROM tbl_project_questions WHERE project_id = p_project_id;

    -- some project related tables
    DELETE FROM tbl_project_stages WHERE project_id = p_project_id;
    DELETE FROM tbl_project_stages_logs WHERE project_id = p_project_id;
    DELETE FROM tbl_project_users WHERE project_id = p_project_id;
    

    -- Finally the project itself
    DELETE FROM tbl_projects WHERE project_id = p_project_id;

    COMMIT;
END$$

DELIMITER ;

CREATE TABLE `tbl_project_stg2_locations` (
	`loc_id` INT(11) NOT NULL,
	`project_id` INT(11) NOT NULL,
	`geo_location` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`geo_lat` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`geo_long` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`erf_number` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`creation_date` DATETIME NOT NULL DEFAULT current_timestamp(),
	`created_by` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`loc_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES (16, 'tbl_project_stg2_locations', 0);


ALTER TABLE `tbl_project_questions` CHANGE `comment` `comment` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;


----------------------------------------------------------------------------------------
------- DATE 15th Oct 2025 DP-156
CREATE TABLE `tbl_web_pages` (`id` INT NOT NULL , `page_name` VARCHAR(50) NOT NULL , `page_version` VARCHAR(25) NOT NULL , `json_data` MEDIUMTEXT NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_web_pages` CHANGE `page_version` `page_version` INT(11) NOT NULL;
ALTER TABLE `tbl_web_pages` DROP `id`;


----------------------------------------------------------------------------------------
------- DATE 24th Oct 2025 Add Pending for eval_status for project table 


SET @next_optionid := (SELECT IFNULL(MAX(optionid), 0) + 1 FROM tbl_lookup_detail);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(@next_optionid, 2, '3', 'project_status_3', 'Pending', 'pending-ml', 3, '', 'active', NOW(), 1, NULL, NULL);

UPDATE `tbl_maxids` 
SET `max_id` = @next_optionid 
WHERE `tbl_name` = 'tbl_lookup_detail';

UPDATE `tbl_projects` SET `eval_status`='project_status_3' WHERE `eval_status` IS NULL

--------------------------------------------------------------------------------------------
------- Date 04 Nov 2025 Added Stage-5 Tables DEV[DONE], QA[DONE]

CREATE TABLE `tbl_project_stg5` (`project_id` INT(11) NOT NULL , `document_details` TEXT NULL DEFAULT NULL , `background_details` TEXT NULL DEFAULT NULL , `purpose_of_document` TEXT NOT NULL , `site_description` TEXT NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `created_by` INT(11) NULL DEFAULT NULL , `updation_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP , `updated_by` INT(11) NULL DEFAULT NULL ) ENGINE = InnoDB;
ALTER TABLE `tbl_project_stg5` ADD PRIMARY KEY(`project_id`);

CREATE TABLE `tbl_project_stg5_stakeholders` (
    `row_id` INT(11) NOT NULL , 
    `project_id` INT(11) NOT NULL , 
    `user_id` INT(11) NOT NULL , 
    `stakeholder_datakey` VARCHAR(100) NULL , 
    `type_datakey` VARCHAR(100) NULL , 
    `access_type` VARCHAR(25) NULL COMMENT 'Edit,View' , 
    `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , 
    `created_by` INT(11) NULL , `updation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , 
    `updated_by` INT(11) NULL , PRIMARY KEY (`row_id`)) ENGINE = InnoDB;


CREATE TABLE `tbl_project_stg5_locations` (
  `loc_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `geo_location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `geo_lat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `geo_long` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `erf_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB;

ALTER TABLE `tbl_project_stg5_locations` ADD PRIMARY KEY(`loc_id`);
----------------------------------------------------------------------------------------
------- DATE 31st Oct 2025 for is_invest_ready and eval_percentage for project table 
ALTER TABLE `tbl_projects` ADD `is_invest_ready` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '1 for Show Investment Ready ' AFTER `is_active`, ADD `eval_percentage` DECIMAL(5,2) NOT NULL AFTER `is_invest_ready`;

----------------------------------------------------------------------------------------
------- DATE 05 Nov 2025 for proj_ref_no for project_stg0 table DEV[DONE],QA[DONE]
ALTER TABLE `tbl_project_stg0` ADD `proj_ref_no` VARCHAR(50) NULL DEFAULT NULL AFTER `is_active`;

------------------------------------------------------------------------------------------
------- Date 06 Nov 2025 for stage5 ddr stakeholder table  DEV[DONE],QA[DONE]
ALTER TABLE `tbl_project_stg5_stakeholders` ADD `user_role` VARCHAR(250) NULL AFTER `type_datakey`;

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

INSERT INTO `tbl_maxids` (`id`, `tbl_name`, `max_id`) VALUES ('17', 'tbl_project_stg5_stakeholders', '0');

----------------------------------------------------------------------------------------
------ DATE 06 Nov 2025 for new category for stage-5 - site-description plus doc upload.DEV[DONE],QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('31', 'Step 5 site-desc', 'project', current_timestamp(), NULL, NULL, NULL);

--------------------------------------------------------------------------------------------
-------- DATE 07 Nov 2025 Added Stage-5 update tbl_project_stg5_locations for primary-auto-increment DEV[DONE], QA[DONE]
ALTER TABLE tbl_project_stg5_locations MODIFY loc_id INT NOT NULL AUTO_INCREMENT;

--------------------------------------------------------------------------------------------
------- DATE 07-11-2025 for tbl_project_stg5_stakeholders user_id NULL DEV[DONE], QA[DONE]
ALTER TABLE `tbl_project_stg5_stakeholders` CHANGE `user_id` `user_id` INT(11) NULL DEFAULT NULL; 

----------------------------------------------------------------------------------------------------
------- DATE 11-11-2025 for tbl_project_stg0 add new column capax(capital expenditure) and opax(operational expenditure) DEV[DONE], QA[DONE]
ALTER TABLE `tbl_project_stg0` ADD `capax` DECIMAL(25) NOT NULL DEFAULT '0' COMMENT 'capital_expenditure' AFTER `ballpark_project_cost`;
ALTER TABLE `tbl_project_stg0` ADD `opax` DECIMAL(25) NOT NULL DEFAULT '0' COMMENT 'operational_expenditure' AFTER `capax`;

--------------------------------------------------------------------------------------------
------- DATE 11-11-2025 for spatical analysis and pricipals DEV[DONE], QA[DONE]
ALTER TABLE `tbl_lookup_detail` ADD `parent_datakey` VARCHAR(50) NULL DEFAULT '0' AFTER `item_data`;

---- DEV-DONE
DROP TRIGGER IF EXISTS tbl_lookup_detail_before_insert;
DELIMITER $$

CREATE TRIGGER tbl_lookup_detail_before_insert
BEFORE INSERT ON tbl_lookup_detail
FOR EACH ROW
BEGIN
    DECLARE v_dev_name VARCHAR(255);
    IF NEW.parent_datakey IS NULL OR NEW.parent_datakey = '' THEN
        SELECT dev_name INTO v_dev_name
        FROM tbl_lookup
        WHERE keyid = NEW.keyid
        LIMIT 1;
    ELSE
        SET v_dev_name = NEW.parent_datakey;
    END IF;
    SET NEW.data_key = CONCAT(v_dev_name, '_', NEW.data_value);
END$$

DELIMITER ;


---- DEV-DONE
DROP TRIGGER IF EXISTS tbl_lookup_detail_before_update;
DELIMITER $$

CREATE TRIGGER tbl_lookup_detail_before_update
BEFORE UPDATE ON tbl_lookup_detail
FOR EACH ROW
BEGIN
    DECLARE v_dev_name VARCHAR(255);
    IF NEW.parent_datakey IS NULL OR NEW.parent_datakey = '' THEN
        SELECT dev_name INTO v_dev_name
        FROM tbl_lookup
        WHERE keyid = NEW.keyid
        LIMIT 1;
    ELSE
        SET v_dev_name = NEW.parent_datakey;
    END IF;
    SET NEW.data_key = CONCAT(v_dev_name, '_', NEW.data_value);
END$$
DELIMITER ;

-- DEV[DONE] QA[DONE]
ALTER TABLE tbl_lookup_detail
MODIFY parent_datakey VARCHAR(50) NULL DEFAULT NULL;

-- DEV[DONE] QA[DONE]
UPDATE tbl_lookup_detail
SET parent_datakey = NULL
WHERE parent_datakey = '0' OR parent_datakey = '';


---- DEV-PENDING
ALTER TABLE `tbl_lookup_detail`
	DROP INDEX `keyid_data_value`,
	ADD UNIQUE INDEX `keyid_data_value` (`keyid`, `parent_datakey`, `data_value`) USING BTREE;
---- DEV-DONE
CREATE TABLE `tbl_project_stg5_keys` (
  `row_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `key_name` varchar(100) NOT NULL COMMENT 'dev_name from tbl_lookup',
  `key_value` varchar(100) NOT NULL COMMENT 'data_key from tbl_lookup_detail',
  `sub_type` varchar(100) DEFAULT NULL,
  `sub_type_name` text DEFAULT NULL,
  `free_text` text DEFAULT NULL,
  `creation_date` datetime DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB;
---- DEV-DONE
ALTER TABLE `tbl_project_stg5_keys` MODIFY `row_id` INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`row_id`);
----- DEV-DONE
ALTER TABLE `tbl_project_stg5_keys` ADD `document_ids` VARCHAR(100) NOT NULL AFTER `free_text`;
------ DEV-DONE
INSERT INTO tbl_categories 
    (cat_id, cat_name, type, creation_date, created_by, updation_date, updated_by)
VALUES
    (32, 'Core Principles for Spatial Analysis', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL),
    (33, 'Opportunities of Spatial Analysis', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL),
    (34, 'Constraints and Challenges', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);
----- DEV-DONE
ALTER TABLE `tbl_project_stg5_keys` ADD `conclusion` TEXT NOT NULL AFTER `document_ids`;
 
---------------------------------------------------------------------------------------------
---- DATE 13-11-2025 tbl_categories add new categories for 2.2.2 DEV[DONE], QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('35', 'Heritage Design Considerations', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('36', 'Stakeholder Engagement', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

---- new table for spatial indicators for moniroting DEV[DONE], QA[DONE]
CREATE TABLE `tbl_project_stg5_spatial_indicators_for_monitoring` (
    `row_id` INT NOT NULL AUTO_INCREMENT,
    `project_id` INT NULL,
    `category` VARCHAR(150) NULL DEFAULT NULL,
    `principle_addresses` VARCHAR(150) NULL DEFAULT NULL,
    `spatial_indicator` TEXT NULL,
    `data_source` TEXT NULL,
    PRIMARY KEY (`row_id`)
);
---- tbl lookup and lookup details entries 2.1. DEV[DONE], QA[DONE]

/* Core Principles for Spatial Analysis and its 7 options  */

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(70, 'core_principles_for_spatial_analysis', 'Core Principles for Spatial Analysis', 'core-principles-for-spatial-analysis-ml', 'These principles should guide the entire project lifecycle, from conception to commissioning', 1, 'No', '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(678, 70, 1, 'core_principles_for_spatial_analysis_1', 'Spatial Justice and Equity', 'spatial-justice-and-equity-ml', 1, '{"description":"Actively use spatial analysis to redress past spatial inequalities and ensure fair access to infrastructure services for all communities, particularly historically disadvantaged townships and remote rural areas."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(679, 70, 2, 'core_principles_for_spatial_analysis_2', 'Environmental and Climate Resilience', 'environmental-and-climate-resilience-ml', 2, '{"description":"Integrate critical environmental data (biodiversity, water sources, fire risk) and climate projections (flooding, sea-level rise, drought) to minimise ecological damage and ensure long-term viability."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(680, 70, 3, 'core_principles_for_spatial_analysis_3', 'Strategic Integration', 'strategic-integration-ml', 3, '{"description":"Ensure the project is not an isolated intervention but is strategically aligned with the **Western Cape Provincial Spatial Development Framework (PSDF)**, Municipal SDFs, and the **Western Cape Infrastructure Framework**."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(681, 70, 4, 'core_principles_for_spatial_analysis_4', 'Evidence-Based Decision Making', 'evidence-based-decision-making-ml', 4, '{"description":"Base all locational and design decisions on robust, current, and verifiable spatial data, using techniques like Multi-Criteria Decision Analysis (MCDA) to objectively compare options."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(682, 70, 5, 'core_principles_for_spatial_analysis_5', 'Economic Efficiency and Catalysis', 'economic-efficiency-and-catalysis-ml', 5, '{"description":"Locate infrastructure to maximise economic return, reduce long-term operational costs, and catalyse sustainable local economic development."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(683, 70, 6, 'core_principles_for_spatial_analysis_6', 'Risk-Aware Planning', 'risk-aware-planning-ml', 6, '{"description":"Proactively identify and mitigate spatial risks, including geohazards (sinkholes, landslides), service backlogs, and social vulnerabilities."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(684, 70, 7, 'core_principles_for_spatial_analysis_7', 'Collaborative Governance', 'collaborative-governance-ml', 7, '{"description":"Use maps and spatial data as a \"common language\" to facilitate transparent communication and collaboration across government departments and with the public."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

/*  Opportunities of Spatial Analysis and its 7 options DEV[DONE] QA[DONE]*/ 

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(71, 'opportunities_of_spatial_analysis', 'Opportunities of Spatial Analysis', 'opportunities-of-spatial-analysis-ml', 'Leveraging spatial analysis presents significant opportunities to enhance project value and impact.', 1, 'No', '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(685, 71, 1, 'opportunities_of_spatial_analysis_1', 'Optimised Site Selection', 'optimised-site-selection-ml', 1, '{"description":"Identify the \'sweet spot\' that balances cost, service delivery, environmental impact, and social benefit, avoiding costly mistakes and community opposition."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(686, 71, 2, 'opportunities_of_spatial_analysis_2', 'Enhanced Climate Adaptation', 'enhanced-climate-adaptation-ml', 2, '{"description":"Design with future climate in mind. For example, by using sea-level rise models for coastal infrastructure or fire-risk maps for positioning buildings and access roads in the Cape Winelands."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(687, 71, 3, 'opportunities_of_spatial_analysis_3', 'Targeted Poverty Alleviation', 'targeted-poverty-alleviation-ml', 3, '{"description":"Precisely identify spatial pockets of poverty and service deprivation, ensuring infrastructure investment directly addresses the most critical needs and supports spatial integration."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(688, 71, 4, 'opportunities_of_spatial_analysis_4', 'Unlocking Economic Potential', 'unlocking-economic-potential-ml', 4, '{"description":"Identify locations that can stimulate local economic development, connect workers to jobs, improve agricultural logistics, or boost tourism corridors."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(689, 71, 5, 'opportunities_of_spatial_analysis_5', 'Improved Disaster Management and Resilience', 'improved-disaster-management-and-resilience-ml', 5, '{"description":"Model evacuation routes, site emergency services for optimal coverage, and plan infrastructure that can withstand and facilitate recovery from natural disasters."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(690, 71, 6, 'opportunities_of_spatial_analysis_6', 'Streamlined Approvals', 'streamlined-approvals-ml', 6, '{"description":"A robust, data-driven spatial analysis provides a strong evidence base for Environmental Impact Assessments (EIAs) and planning approvals, demonstrating due diligence."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(691, 71, 7, 'opportunities_of_spatial_analysis_7', '"Future-Proofing" via Scenario Planning', 'future-proofing-via-scenario-planning-ml', 7, '{"description":"Model different future scenarios (e.g., population growth, water scarcity, economic shifts) to ensure infrastructure remains fit-for-purpose for decades."}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

/* Constraints and Challenges and its 4 options and their sub types DEV[DONE] QA[DONE] */

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(72, 'constraints_and_challenges', 'Constraints and Challenges', 'constraints-and-challenges-ml', 'Being aware of these constraints is crucial for realistic planning and effective execution.', 1, 'No', '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(692, 72, 1, 'constraints_and_challenges_1', 'Data-Related Constraints', 'data-related-constraints-ml', 1, '{"description":""}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(693, 0, '1', 'constraints_and_challenges_1_1', 'Availability and Quality', 'availability-and-quality-ml', 1, '{"description":"Critical datasets may be outdated, incomplete, or non-existent, especially for informal settlements or sensitive ecological areas."}', 'constraints_and_challenges_1', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(694, 0, '2', 'constraints_and_challenges_1_2', 'Format and Compatibility', 'format-and-compatibility-ml', 2, '{"description":"Data may be held in different formats by various departments (national, provincial, municipal), creating integration challenges (\"silos\")."}', 'constraints_and_challenges_1', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(695, 0, '3', 'constraints_and_challenges_1_3', 'Cost', 'cost-ml', 3, '{"description":"Access to high-resolution satellite imagery or specialised commercial data can be expensive."}', 'constraints_and_challenges_1', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(696, 72, 2, 'constraints_and_challenges_2', 'Technical and Capacity Constraints', 'technical-and-capacity-constraints-ml', 2, '{"description":""}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(697, 0, '1', 'constraints_and_challenges_2_1', 'Specialist Skills Gap', 'specialist-skills-gap-ml', 1, '{"description":"A shortage of in-house GIS analysts, data scientists, and spatial planners who can interpret complex data and run advanced models."}', 'constraints_and_challenges_2', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(698, 0, '2', 'constraints_and_challenges_2_2', 'Software and IT Infrastructure', 'software-an-it-infrastructure-ml', 2, '{"description":"Limited licenses for professional GIS software and inadequate hardware for processing large spatial datasets."}', 'constraints_and_challenges_2', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);



INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(699, 72, 3, 'constraints_and_challenges_3', 'Structural and Governance Constraints', 'structural-and-governance-constraints-ml', 3, '{"description":""}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(700, 0, '1', 'constraints_and_challenges_3_1', 'Jurisdictional Complexity', 'jurisdictional-complexity-ml', 1, '{"description":"Overlapping mandates between provincial and local municipalities can lead to conflicting spatial plans and delays."}', 'constraints_and_challenges_3', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(701, 0, '2', 'constraints_and_challenges_3_2', 'Legacy Spatial Patterns', 'legacy-spatial-patterns-ml', 2, '{"description":"The enduring apartheid-era spatial structure makes integration and redress incredibly complex and expensive to achieve physically."}', 'constraints_and_challenges_3', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(702, 0, '3', 'constraints_and_challenges_3_3', 'Community Opposition', 'community-opposition-ml', 3, '{"description":"Even a technically optimal site, identified through spatial analysis, may face significant social or political resistance."}', 'constraints_and_challenges_3', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);



INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(703, 72, 4, 'constraints_and_challenges_4', 'Environmental & Physical Constraints', 'environmental-and-physical-constraints-ml', 4, '{"description":""}', '', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(704, 0, '1', 'constraints_and_challenges_4_1', 'Fragile and Unique Ecosystems', 'fragile-and-unique-ecosystems-ml', 1, '{"description":"The Western Cape\'s high levels of biodiversity (e.g., Cape Floristic Region) severely constrain development options and require careful, sensitive analysis."}', 'constraints_and_challenges_4', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(705, 0, '2', 'constraints_and_challenges_4_2', 'Mountainous Terrain and Limited Land', 'mountainous-terrain-and-limited-land-ml', 2, '{"description":"The physical geography naturally limits suitable, stable, and affordable land for large-scale infrastructure projects."}', 'constraints_and_challenges_4', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(706, 0, '3', 'constraints_and_challenges_4_3', 'Water Scarcity', 'water-scarcity-ml', 3, '{"description":"The arid climate is a fundamental constraint that must be central to all spatial analysis for water-intensive projects."}', 'constraints_and_challenges_4', 1, '2025-11-10 17:00:00', 1, '2025-11-10 17:00:00', 1);


/* section 2.2 Heritage design and spatial indicators  addition of options DEV[DONE] QA[DONE] */

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(73, 'heritage_design_and_spatial_indicators', 'Heritage design and spatial indicators', 'heritage-design-and-spatial-indicators-ml', '', 1, 'No', '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(707, 73, 1, 'heritage_design_and_spatial_indicators_1', 'Heritage Design Considerations', 'heritage-design-considerations-ml', 1, '{"description":"Heritage design goes beyond preserving old buildings. It is about managing change in a way that respects and enhances the cultural significance of a place."}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(708, 0, 1, 'heritage_design_and_spatial_indicators_1_1', 'Pre-Development Assessment', 'pre-development-assessment-ml', 1, '{"description":""}', 'heritage_design_and_spatial_indicators_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(709, 0, 1, 'heritage_design_and_spatial_indicators_1_1_1', 'Spatial Screening', 'spatial-screening-ml', 1, '{"description":"Use GIS to overlay the project area with heritage registers: the **National Heritage Resources Act (NHRA) Section 34 & 38** databases, Provincial Heritage Site maps, and Municipal Inventories."}', 'heritage_design_and_spatial_indicators_1_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(710, 0, 2, 'heritage_design_and_spatial_indicators_1_1_2', 'Grading of Heritage Significance', 'grading-of-heritage-significance-ml', 2, '{"description":"Identify if the site or affected resources are a National Heritage Site, Provincial Heritage Site, or a structure older than 60 years (protected under NHRA)."}', 'heritage_design_and_spatial_indicators_1_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(711, 0, 3, 'heritage_design_and_spatial_indicators_1_1_3', 'Archaeological Potential', 'archaeological-potential-ml', 3, '{"description":"Use historical maps and soil data to assess the likelihood of subsurface archaeological deposits (e.g., near old waterways, historical settlements)."}', 'heritage_design_and_spatial_indicators_1_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(712, 0, 2, 'heritage_design_and_spatial_indicators_1_2', 'Design Response and Integration', 'design-response-and-integration-ml', 2, '{"description":""}', 'heritage_design_and_spatial_indicators_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(713, 0, 1, 'heritage_design_and_spatial_indicators_1_2_1', 'Respect Context and Setting', 'respect-context-and-setting-ml', 1, '{"description":"The new design should respond to the historical *genius loci* (spirit of the place). This includes respecting historical sightlines, street patterns, and landscape features."}', 'heritage_design_and_spatial_indicators_1_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(714, 0, 2, 'heritage_design_and_spatial_indicators_1_2_2', 'Scale Massing and Height', 'scale-massing-and-height-ml', 2, '{"description":"The new infrastructure\'s scale and bulk should be sympathetic to the existing heritage structures, not dominating them."}', 'heritage_design_and_spatial_indicators_1_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(715, 0, 3, 'heritage_design_and_spatial_indicators_1_2_3', 'Materiality and Detailing', 'materiality-and-detailing-ml', 3, '{"description":"Use complementary materials, colours, and textures. While replication is not always necessary, a contemporary design that harmonises is often more successful than a poor imitation."}', 'heritage_design_and_spatial_indicators_1_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(716, 0, 4, 'heritage_design_and_spatial_indicators_1_2_4', 'Adaptive Re-use', 'adaptive-re-use-ml', 4, '{"description":"Where possible, explore options to incorporate and re-use existing heritage structures for new functions, giving them a viable future (e.g., converting an old warehouse into a community facility)."}', 'heritage_design_and_spatial_indicators_1_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);



INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(717, 73, 2, 'heritage_design_and_spatial_indicators_2', 'Stakeholder Engagement', 'stakeholder-engagement-ml', 2, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(718, 0, 1, 'heritage_design_and_spatial_indicators_2_1', 'Stakeholder Engagement', 'stakeholder-engagement-ml', 1, '{"description":"Proactively engage with Heritage Western Cape, local municipalities, and affected communities, including groups with intangible cultural heritage connections to the land."}', 'heritage_design_and_spatial_indicators_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(719, 73, 3, 'heritage_design_and_spatial_indicators_3', 'Spatial Indicators for Monitoring and Evaluation', 'spatial-indicators-for-monitoring-and-evaluation-ml', 2, '{"description":"Spatial indicators are measurable, geographically referenced metrics used to set targets, evaluate project success, and monitor long-term impact. They translate strategic principles into quantifiable data."}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

/* section 2.2 Built Environment  addition of options DEV[DONE] QA[DONE] */

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(74, 'built_environment', 'Built Environment', 'built-environment-ml', '', 1, 'No', '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(720, 74, 1, 'built_environment_1', 'Core Components of Built Design for Spatial Analysis', 'core-components-of-built-design-for-spatial-analysis-ml', 1, '{"description":"Have you considered all the relevant Built Design aspects below? Please provide a brief explanation, and upload supporting documentation as evidence."}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(721, 0, 1, 'built_environment_1_1', 'Form and Massing', 'form-and-massing-ml', 1, '{"description":"The three-dimensional shape, scale, and volume of the structure, analysed for its impact on sun paths, wind patterns, shadows, and visual amenity."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(722, 0, 2, 'built_environment_1_2', 'Placement and Orientation', 'placement-and-orientation-ml', 2, '{"description":"The exact siting of the building or structure on the land, optimized based on solar gain, views, access, noise, and integration with the natural topography."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(723, 0, 3, 'built_environment_1_3', 'Function and Programme', 'function-and-programme-ml', 3, '{"description":"The internal layout and organisation of spaces, designed to ensure operational efficiency and a positive user experience, informed by spatial behaviour patterns."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(724, 0, 4, 'built_environment_1_4', 'Materiality and Texture', 'materiality-and-texture-ml', 4, '{"description":"The selection of construction materials based on local availability, environmental performance (thermal mass, reflectivity), durability, and cultural appropriateness."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(725, 0, 5, 'built_environment_1_5', 'Connectivity and Circulation', 'connectivity-and-circulation-ml', 5, '{"description":"The design of movement systems (pedestrian, vehicle, service) that connect the asset to the broader urban or rural network, based on desire-line analysis and traffic flow data."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(726, 0, 6, 'built_environment_1_6', 'Interface with the Public Realm', 'interface-with-the-public-realm-ml', 6, '{"description":"The design of the transition between the building and public spaces, creating active edges, safe entrances, and contributing to the quality of the street."}', 'built_environment_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);




INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(727, 74, 2, 'built_environment_2', 'Principles for Built Design in Western Cape Government Infrastructure Projects', 'principles-for-built-design-in-western-cape-government-infrastructure-projects-ml', 2, '{"description":"These principles ensure that the physical design of the infrastructure is responsive, resilient, and respectful of the unique character and challenges of the Western Cape."}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(728, 0, 1, 'built_environment_2_1', 'Principle 1: Contextual Responsiveness', 'principle-1-contextual-responsiveness-ml', 1, '{"description":"Aim: The design must respond sensitively to its specific physical, cultural, and historical context."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(729, 0, 1, 'built_environment_2_1_1', 'Topography', 'topography-ml', 1, '{"description":"Design with the land, not against it. Use split-levels, terraces, and stepped foundations to minimise cut-and-fill and preserve natural drainage patterns."}', 'built_environment_2_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(730, 0, 2, 'built_environment_2_1_2', 'Local Character', 'local-character-ml', 2, '{"description":"Draw inspiration from local vernacular architecture (e.g., Cape Dutch proportions, Karoo farmstead layouts) in a contemporary way, using modern materials and techniques."}', 'built_environment_2_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(731, 0, 3, 'built_environment_2_1_3', 'Visual Impact', 'visual-impact-ml', 3, '{"description":"Conduct viewshed analyses to understand the visual prominence of the structure and design its form and colour palette to blend harmoniously with the landscape."}', 'built_environment_2_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);




INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(732, 0, 2, 'built_environment_2_2', 'Principle 2: Climate Resilience and Environmental Performance', 'principle-2-climate-resilience-and-environmental-performance-ml', 2, '{"description":"Aim: The built form must be optimised for the Western Cape\'s Mediterranean climate and resource constraints."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(733, 0, 1, 'built_environment_2_2_1', 'Water Sensitivity', 'water-sensitivity-ml', 1, '{"description":"Integrate built-in rainwater harvesting systems, permeable paving, and stormwater retention features to align with the \"Water Sensitive Urban Design\" approach crucial for a water-scarce region."}', 'built_environment_2_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(734, 0, 2, 'built_environment_2_2_2', 'Passive Design', 'passive-design-ml', 2, '{"description":"Orient buildings for optimal solar exposure (north-facing in the Southern Hemisphere), design for cross-ventilation to reduce cooling costs, and use shading devices (e.g., brise-soleil) to manage summer heat."}', 'built_environment_2_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(735, 0, 3, 'built_environment_2_2_3', 'Fire-Resistant Materials', 'fire-Resistant-materials-ml', 3, '{"description":"In high-risk Interface Zones (e.g., near the Cape Floral Kingdom), mandate non-combustible roofing, ember-proof vents, and defensible space landscaping."}', 'built_environment_2_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);



INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(736, 0, 3, 'built_environment_2_3', 'Principle 3: Spatial Justice and Universal Access', 'principle-3-spatial-justice-and-universal-access-ml', 3, '{"description":"Aim: The design must actively promote inclusivity, safety, and dignity for all users, reversing legacies of exclusion."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(737, 0, 1, 'built_environment_2_3_1', 'Universal Access', 'universal-access-ml', 1, '{"description":"Exceed basic compliance by providing seamless, dignified access for people with disabilities, the elderly, and children through ramps, tactile paving, and clear signage."}', 'built_environment_2_3', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(738, 0, 2, 'built_environment_2_3_2', 'Eyes on the Street', 'eyes-on-the-street-ml', 2, '{"description":"Design active frontages and windows that face public spaces to create natural surveillance and enhance safety, particularly in public buildings and transport hubs."}', 'built_environment_2_3', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(739, 0, 3, 'built_environment_2_3_3', 'Dignified Public Interfaces', 'dignified-public-interfaces-ml', 3, '{"description":"Avoid blank walls and fortress-like designs. Create welcoming, human-scaled entrances, covered waiting areas, and public plazas that encourage community use."}', 'built_environment_2_3', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);




INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(740, 0, 4, 'built_environment_2_4', 'Principle 4: Heritage Integration and Cultural Expression', 'principle-4-heritage-integration-and-cultural-expression-ml', 4, '{"description":"Aim: New construction must respectfully engage with the layered history of its site."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(741, 0, 1, 'built_environment_2_4_1', 'Interpretive Design', 'interpretive-design-ml', 1, '{"description":"Instead of replicating historical styles, use contemporary design to interpret heritage. This could mean reflecting a historical building\'s roofline in a new form or using local stone in a modern way."}', 'built_environment_2_4', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(742, 0, 2, 'built_environment_2_4_2', 'Conservation and Adaptation', 'conservation-and-adaptation-ml', 2, '{"description":"Where heritage structures exist, prioritise adaptive re-use. Sensitively integrate new functions, clearly distinguishing between old and new while preserving cultural significance."}', 'built_environment_2_4', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(743, 0, 3, 'built_environment_2_4_3', 'Memorialisation', 'Memorialisation-ml', 3, '{"description":"In areas with significant intangible heritage, use built design to commemorate history through public art, landscape features, or interpretive plaques."}', 'built_environment_2_4', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(744, 0, 5, 'built_environment_2_5', 'Principle 5: Economic Pragmatism and Buildability', 'principle-5-economic-pragmatism-and-buildability-ml', 5, '{"description":"Aim: The design must be cost-effective over its entire lifecycle and feasible to build with local capacity."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(745, 0, 1, 'built_environment_2_5_1', 'Local Labour and Materials', 'local-labour-and-materials-ml', 1, '{"description":"Specify locally sourced, sustainable materials (e.g., locally quarried stone, FSC-certified timber) to reduce transport costs and support the local economy."}', 'built_environment_2_5', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(746, 0, 2, 'built_environment_2_5_2', 'Design for Maintenance', 'design-for-maintenance-ml', 2, '{"description":"Choose durable, low-maintenance materials and simple, robust details that are easy for local municipal teams to maintain and repair."}', 'built_environment_2_5', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(747, 0, 3, 'built_environment_2_5_3', 'Phased Implementation', 'phased-implementation-ml', 3, '{"description":"Design the project in clear, self-contained phases that allow for parts of the infrastructure to be completed and become operational, providing early benefits."}', 'built_environment_2_5', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);




INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(748, 0, 6, 'built_environment_2_6', 'Principle 6: Future Flexibility and Adaptability', 'principle-6-future-flexibility-and-adaptability-ml', 6, '{"description":"Aim: Infrastructure must be designed to accommodate future change without requiring costly demolition."}', 'built_environment_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(749, 0, 1, 'built_environment_2_6_1', 'Modular and Open-Ended Design', 'modular-and-open-ended-design-ml', 6, '{"description":"Use standardised, modular components that allow for easy expansion or reconfiguration as needs change."}', 'built_environment_2_6', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(750, 0, 2, 'built_environment_2_6_2', 'Multi-Functional Spaces', 'multi-functional-spaces-ml', 2, '{"description":"Design public spaces and structural systems that can support multiple uses over time (e.g., a community hall that can also serve as an emergency centre)."}', 'built_environment_2_6', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(751, 0, 3, 'built_environment_2_6_3', 'Service Flexibility', 'service-flexibility-ml', 3, '{"description":"Design service cores and conduits with extra capacity and easy access for future upgrades or technological changes."}', 'built_environment_2_6', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);


----------------------------------------------------------------------------------------------------------------
----- tbl lookup and lookup details entries 2.2. DEV[DONE], QA[DONE]

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(75, 'special_indicators_category', 'Special Indicators Category', 'special-indicators-category-ml', '', 1, 'No', '2025-11-12 17:00:00', 1, '2025-11-12 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(752, 75, 1, 'special_indicators_category_1', 'Social Equity', 'social-equity-ml', 1, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(753, 75, 2, 'special_indicators_category_2', 'Economic Efficiency', 'economic-efficiency-ml', 2, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(754, 75, 3, 'special_indicators_category_3', 'Environmental Resilience', 'environmental-resilience-ml', 3, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(755, 75, 4, 'special_indicators_category_4', 'Heritage and Culture', 'heritage-and-culture-ml', 4, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(756, 75, 5, 'special_indicators_category_5', 'Service Delivery', 'service-delivery-ml', 5, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(76, 'special_indicators_principle_addressed', 'Special Indicators Principle Addressed', 'special-indicators-principle-addressed-ml', '', 1, 'No', '2025-11-12 17:00:00', 1, '2025-11-12 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(757, 76, 1, 'special_indicators_principle_addressed_1', 'Spatial Justice', 'spatial-justice-ml', 1, '{"description":""}', 'special_indicators_category_1', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(758, 76, 2, 'special_indicators_principle_addressed_2', 'Strategic Integration', 'strategic-integration-ml', 2, '{"description":""}', 'special_indicators_category_2', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(759, 76, 3, 'special_indicators_principle_addressed_3', 'Climate Resilience', 'climate-resilience-ml', 3, '{"description":""}', 'special_indicators_category_3', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(760, 76, 4, 'special_indicators_principle_addressed_4', 'Heritage-Sensitive Development', 'heritage-sensitive-development-ml', 4, '{"description":""}', 'special_indicators_category_4', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(761, 76, 5, 'special_indicators_principle_addressed_5', 'Evidence-Based Decision Making', 'evidence-based-decision-making-ml', 5, '{"description":""}', 'special_indicators_category_5', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);





INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(77, 'special_indicators', 'Special Indicators', 'special-indicators-ml', '', 1, 'No', '2025-11-12 17:00:00', 1, '2025-11-12 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(762, 77, 1, 'special_indicators_1', 'Visual Impact-How the development affects views to-from heritage sites', 'visual-impact-how-the-development-affects-views-to-from-heritage-sites-ml', 1, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(763, 77, 2, 'special_indicators_2', 'Proximity to Heritage Sites-Distance from protected buildings landscapes or archaeological zones', 'proximity-to-heritage-sites-distance-from-protected-buildings-landscapes-or-archaeological-zones-ml', 2, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(764, 77, 3, 'special_indicators_3', 'Land Use Compatibility-Whether the proposed use aligns with heritage conservation goals', 'land-use-compatibility-whether-the-proposed-use-aligns-with-heritage-conservation-goals-ml', 3, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(765, 77, 4, 'special_indicators_4', 'Density and Scale-Building height massing and footprint compared to heritage surroundings', 'density-and-scale-Building-height-massing-and-footprint-compared-to-heritage-surroundings-ml', 4, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(767, 77, 5, 'special_indicators_5', 'Materiality-Use of traditional or compatible materials in new construction', 'materiality-Use-of-traditional-or-compatible-materials-in-new-construction-ml', 5, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(768, 77, 6, 'special_indicators_6', 'Access and Circulation-Changes to pedestrian or vehicular movement around heritage assets', 'access-and-Circulation-changes-to-pedestrian-or-vehicular-movement-around-heritage-assets-ml', 6, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(769, 77, 7, 'special_indicators_7', 'Environmental Sensitivity-Impact on natural heritage (e.g. wetlands indigenous vegetation)', 'environmental-sensitivity-impact-on-natural-heritage-e-g-wetlands-indigenous-vegetation-ml', 7, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

-- Failed 770 due to too long display_name_ml
INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(770, 77, 8, 'special_indicators_8', 'Cultural Landscape Integrity-Preservation of spatial patterns pathways and relationships in the landscape', 'cultural-landscape-integrity-preservation-of-spatial-patterns-pathways-and-relationships-in-the-landscape-ml', 8, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(78, 'data_sources', 'Data Sources', 'data-sources-ml', '', 1, 'No', '2025-11-12 17:00:00', 1, '2025-11-12 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(771, 78, 1, 'data_sources_1', 'Network Analysis', 'network-analysis-ml', 1, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(772, 78, 2, 'data_sources_2', 'Census Data', 'census-data-ml', 2, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(773, 78, 3, 'data_sources_3', 'GIS Buffering', 'gis-buffering-ml', 3, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(774, 78, 4, 'data_sources_4', 'Pedestrian Network Data', 'pedestrian-network-data-ml', 4, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(775, 78, 5, 'data_sources_5', 'GIS Measurement', 'gis-measurement-ml', 5, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(776, 78, 6, 'data_sources_6', 'Provincial Biodiversity Plan', 'provincial-biodiversity-plan-ml', 6, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(777, 78, 7, 'data_sources_7', 'Land Use Maps', 'land-use-maps-ml', 7, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(778, 78, 8, 'data_sources_8', 'National Flood Hazard Datasets', 'national-flood-hazard-datasets-ml', 8, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(779, 78, 9, 'data_sources_9', 'HWC Registry', 'hwc-registry-ml', 9, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(780, 78, 10, 'data_sources_10', 'Project As-Built Drawings', 'project-as-built-drawings-ml', 10, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(781, 78, 11, 'data_sources_11', 'Archaeological Survey Reports', 'archaeological-survey-reports-ml', 11, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(782, 78, 12, 'data_sources_12', 'GIS', 'gis-ml', 12, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(783, 78, 13, 'data_sources_13', 'Municipal Service Data', 'municipal-service-data-ml', 13, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(784, 78, 14, 'data_sources_14', 'Aerial Imagery', 'aerial-imagery-ml', 14, '{"description":""}', '', 1, '2025-11-11 17:00:00', 1, '2025-11-11 17:00:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(79, 'landscape_design_informants', 'Landscape Design informants', 'landscape-design-informants-ml', 'Here are the key categories of informants, with specific examples relevant to the Western Cape.', 1, 'No', '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(785, 79, 1, 'landscape_design_informants_1', 'Biophysical and Environmental Informants', 'biophysical-and-environmental-informants-ml', 1, '{"description":"These informants relate to the natural systems and resources of the site."}', '', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(786, 0, 1, 'landscape_design_informants_1_1', 'Topography and Geology', 'topography-and-geology-ml', 1, '{"description":"Data: Contour lines, slope analysis, soil types, seismic activity, presence of dolomite or unstable slopes.\n Design Implication: Determines building placement, earthworks, foundation design, drainage strategies, and landslide risk mitigation. Steep slopes in Cape Town\'s suburbs constrain development but offer view corridors."}', 'landscape_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(787, 0, 2, 'landscape_design_informants_1_2', 'Hydrology and Water Systems', 'hydrology-and-water-systems-ml', 2, '{"description":"Data: Location of rivers, wetlands, aquifers, floodplains (1:100-year flood line), and seasonal watercourses. \n Design Implication: Critical for Water Sensitive Urban Design (WSUD). Informs stormwater management (e.g., swales, retention ponds), avoids building in flood-prone areas, and protects water quality in ecologically sensitive areas like the Cape Floristic Region."}', 'landscape_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(788, 0, 3, 'landscape_design_informants_1_3', 'Climate and Microclimate', 'climate-and-microclimate-ml', 3, '{"description":"Data: Sun path analysis, prevailing summer and winter winds (e.g., the "Cape Doctor"), rainfall patterns, temperature variations, and fire risk maps. \n Design Implication: Influences building orientation for passive solar gain and cooling, placement of windows for ventilation, design of public spaces for sun and wind protection, and the selection of fire-resistant landscaping."}', 'landscape_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(789, 0, 4, 'landscape_design_informants_1_4', 'Ecology and Biodiversity', 'ecology-and-biodiversity-ml', 4, '{"description":"Data: Vegetation maps (e.g., remnant Renosterveld, Fynbos), critical biodiversity areas, wildlife corridors, and protected species habitats. \n Design Implication: Mandates the protection and rehabilitation of sensitive ecosystems. Informs planting palettes to use indigenous, water-wise species and designs that maintain ecological connectivity."}', 'landscape_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(790, 79, 2, 'landscape_design_informants_2', 'Socio-Cultural and Heritage Informants', 'socio-cultural-and-heritage-informants-ml', 2, '{"description":"These informants relate to the people, their history, and cultural practices."}', '', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(791, 0, 1, 'landscape_design_informants_2_1', 'Spatial History & Heritage', 'spatial-history-and-heritage-ml', 1, '{"description":"Data: Heritage registers (National and Provincial), archaeological potential, historical maps, and significant view sheds. \n Design Implication: Preserves and interprets heritage resources. Influences building height, massing, and materiality to be sensitive to historical context. Informs memorialisation or reconciliation through design."}', 'landscape_design_informants_2', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(792, 0, 2, 'landscape_design_informants_2_2', 'Community Structure & Needs', 'community-structure-and-needs-ml', 2, '{"description":"Data: Census data, community survey results, public participation feedback, and identified needs for specific age groups (youth, elderly). \n Design Implication: Ensures facilities are tailored to the demographic (e.g., playgrounds, clinics, community halls). Identifies locations for social infrastructure to serve the greatest need."}', 'landscape_design_informants_2', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(793, 0, 3, 'landscape_design_informants_2_3', 'Movement & Access Patterns', 'movement-and-access-patterns-ml', 3, '{"description":"Data: Desire lines (informal paths), pedestrian and vehicle traffic counts, public transport routes, and analysis of safe/unsafe routes. \n Design Implication: Shapes the layout of paths, roads, and nodes. Prioritizes non-motorized transport (NMT) and ensures safe access to public transport interchanges."}', 'landscape_design_informants_2', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(80, 'urban_design_informants', 'Urban Design informants', 'urban-design-informants-ml', 'Here are the key categories of informants, with specific examples relevant to the Western Cape.', 1, 'No', '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(794, 80, 1, 'urban_design_informants_1', 'Spatial & Urban Structural Informants', 'spatial-and-urban-structural-informants-ml', 1, '{"description":"These informants relate to the man-made environment and its planned future."}', '', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(795, 0, 1, 'urban_design_informants_1_1', 'Existing Land Use & Infrastructure', 'existing-land-use-and & Infrastructure-ml', 1, '{"description":"Data: Zoning maps, location of existing services (water, sewerage, electricity), and adjacent land uses. \n Design Implication: Ensures compatibility with surrounding uses. Identifies opportunities for infill development and connections to existing service networks."}', 'urban_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(796, 0, 2, 'urban_design_informants_1_2', 'Policy & Regulatory Frameworks', 'policy-and-regulatory-frameworks-ml', 2, '{"description":"Data: Municipal Spatial Development Frameworks (SDFs), the **Western Cape Provincial Spatial Development Framework (PSDF)**, Zoning Schemes, and Environmental Management Frameworks. \n Design Implication: Provides the legal and strategic basis for the project\'s location, density, and land use, ensuring alignment with broader government goals."}', 'urban_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(797, 0, 3, 'urban_design_informants_1_3', 'Visual & Perceptual Qualities', 'visual-and-perceptual-qualities-ml', 3, '{"description":"Data: Viewshed analysis, landmark identification, and character studies of the urban or rural landscape. \n Design Implication: Protects important public views (e.g., of Table Mountain). Ensures new development contributes positively to the sense of place and local character."}', 'urban_design_informants_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(81, 'common_to_landscape_and_urban', 'Common to Landscape and Urban', 'common-to-landscape-and-urban-ml', 'Here are the key categories of informants, with specific examples relevant to the Western Cape.', 1, 'No', '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(798, 81, 1, 'common_to_landscape_and_urban_1', 'Economic and Functional Informants', 'economic-and-functional-informants-ml', 1, '{"description":"These informants relate to the project\'s viability and operational logic."}', '', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(799, 0, 1, 'common_to_landscape_and_urban_1_1', 'Economic Nodes & Market Forces', 'economic-nodes-and-market-forces-ml', 1, '{"description":"Data: Location of major employment centres, retail hubs, tourism routes, and agricultural land. \n Design Implication: Informs the design of economic infrastructure and ensures good connectivity between people and job opportunities."}', 'common_to_landscape_and_urban_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(800, 0, 2, 'common_to_landscape_and_urban_1_2', 'Life-Cycle Costs & Maintenance', 'life-cycle-costs-and-maintenance-ml', 2, '{"description":"Data: Costs of materials, construction, and long-term maintenance. \n Design Implication: Promotes the use of durable, locally-sourced, and low-maintenance materials, especially in resource-constrained municipal environments."}', 'common_to_landscape_and_urban_1', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(801, 81, 2, 'common_to_landscape_and_urban_2', 'Synthesis: From Informants to Design', 'synthesis-from-informants-to-design-ml', 2, '{"description":"The process is not linear but iterative. The informants are layered using GIS and expert analysis to reveal synergies and conflicts."}', '', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(802, 0, 1, 'common_to_landscape_and_urban_2_1', 'Slope analysis (biophysical)', 'slope-analysis-biophysical-ml', 1, '{"description":"might show that the most buildable land is also a critical **agricultural land** (economic) and contains a **heritage resource** (socio-cultural). The design must navigate this tension."}', 'common_to_landscape_and_urban_2', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(803, 0, 2, 'common_to_landscape_and_urban_2_2', 'Desire line (socio-cultural)', 'desire-line-socio-cultural-ml', 2, '{"description":"cutting across a field might indicate the ideal location for a new path, but the design must also ensure it doesn\'t damage a **sensitive wetland** (biophysical)."}', 'common_to_landscape_and_urban_2', 1, '2025-11-13 10:30:00', 1, '2025-11-13 10:30:00', 1);


--- Added categories for stage 5 LANDSCAPE & URBAN DESIGN INFORMANTS DEV[DONE] QA[DONE]-------
-- 2.3.1
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('37', 'Biophysical & Environmental Informants', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('38', 'Socio-Cultural & Heritage Informants', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

-- 2.3.2
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('39', 'Spatial & Urban Structural Informants', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

-- 2.3.3
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('40', 'Economic & Functional Informants', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('41', 'Synthesis: From Informants to Design', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

-----------------------------------------------------------------------------------------------------------------------
--- DATE 14-11-2025 Added categories for stage 5 Core Principles for Spatial Analysis in Western Cape Infrastructure Projects DEV[DONE] QA[DONE]-------
-- 2.4
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('42', 'Spatial Principles', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

-- 3 Added categories for stage 5 Conceptual Development Options DEV[DONE] QA[DONE]-------
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('43', 'Conceptual Development Options', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);

-- 4 Added categories for stage 5 Specialists' Preliminary Assessment of Development Options
-- INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES 
-- (44, 'SPADO Summary Tab 1', 'project', NOW(), NULL, NULL, NULL),
-- (45, 'SPADO Summary Tab 2', 'project', NOW(), NULL, NULL, NULL),
-- (46, 'SPADO Summary Tab 3', 'project', NOW(), NULL, NULL, NULL),
-- (47, 'SPADO Financial Tab 1', 'project', NOW(), NULL, NULL, NULL),
-- (48, 'SPADO Financial Tab 2', 'project', NOW(), NULL, NULL, NULL),
-- (49, 'SPADO Financial Tab 3', 'project', NOW(), NULL, NULL, NULL);


---------------------------------------------------------------------------------------------------------------------------
--- DATE 17-11-2025 Added new columns to tbl_project_stg5 table financial-feasibility-preffered-option and motivation DEV[DONE] QA  [PENDING]

ALTER TABLE `tbl_project_stg5`
	ADD COLUMN `summary_of_preliminary_impacts_and_ranking` VARCHAR(50) NULL DEFAULT NULL AFTER `site_description`,
	ADD COLUMN `summary_of_preliminary_impacts_and_ranking_motivation` TEXT NULL AFTER `summary_of_preliminary_impacts_and_ranking`,
	ADD COLUMN `financial_feasibility_preferred_option` VARCHAR(50) NULL DEFAULT NULL AFTER `summary_of_preliminary_impacts_and_ranking_motivation`,
	ADD COLUMN `financial_feasibility_preferred_option_motivation` TEXT NULL AFTER `financial_feasibility_preferred_option`;

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

----------------------------------------------------------------------------------------------------------------------------------
-- DATE 18-11-2025 Added new table tbl_project_stg5_quality_verified_by DEV[DONE] QA[DONE]
CREATE TABLE tbl_project_stg5_quality_verified_by (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    company VARCHAR(255) NOT NULL,
    representative_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(255),
    created_by INT,
    created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1
);

-- Add new category in tbl_categories table for quality verification certificates DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES ('50', 'Quality Verification Certificates', 'project', '2025-11-17 10:10:59', NULL, NULL, NULL);
    
-- Add new category for stage-5 preferred development options and guidelines. DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('51', 'Preferred development Options and Guidelines', 'project', '2025-11-17 10:10:59', NULL, NULL, NULL);

-- DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('52', 'Spatial Principles', 'project', '2025-11-17 10:10:59', NULL, NULL, NULL);

-- DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) 
VALUES ('53', 'Landscape and urban design informants', 'project', '2025-11-17 10:10:59', NULL, NULL, NULL);


---------------------------------------------------------------------------------------------------------------
-- DATE 19-11-2025 Added new columns tbl_project_stg5_stakeholders DEV[DONE] QA[DONE]
ALTER TABLE `tbl_project_stg5_stakeholders` ADD `user_email` VARCHAR(150) NOT NULL AFTER `user_role`;

ALTER TABLE `tbl_project_stg5_stakeholders` ADD `user_name` VARCHAR(150) NOT NULL AFTER `user_email`;

ALTER TABLE tbl_project_stg5_stakeholders
ADD COLUMN report_send_status ENUM('pending', 'shared') NOT NULL DEFAULT 'pending'
AFTER access_type;

-- Lookup 2.4 

/*  2.4 section  DEV[DONE] QA[DONE] */

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(82, 'spatial_principles', 'Spatial Principles', 'spatial-principles-ml', 'These principles are designed to align with the unique challenges and opportunities within the Western Cape, such as its diverse landscapes, socio-economic disparities, water scarcity, and biodiversity.', 1, 'No', '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(804, 82, 1, 'spatial_principles_1', 'Principle 1: Spatial Integration and Alignment', 'principle-1-spatial-integration-and-alignment-ml', 1, '{"description":"Aim: Ensure the project is not an isolated intervention but a strategically integrated component of the broader spatial landscape.<br>
Action: <br>
<ul>
<li>Align with the Provincial Spatial Development Framework (PSDF), Municipal Spatial Development Frameworks(SDFs), and the Western Cape Infrastructure Framework.</li>
<li>Analyse how the project connects with and strengthens existing networks of social infrastructure (schools,clinics), economic nodes, and transport corridors.</li>
<li>Identify and mitigate potential spatial conflicts with other planned developments or protected areas.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(805, 82, 2, 'spatial_principles_2', 'Principle 2: Spatial Justice and Equitable Access', 'principle-2-spatial-justice-and-equitable-access-ml', 2, '{"description":"Aim: Actively use spatial analysis to redress past spatial inequalities and promote social and economic inclusion.<br>
Action: <br>
<ul>
<li>Conduct an **accessibility analysis** to identify "service deserts" and prioritise underserved communities, particularly historically disadvantaged townships and rural areas.</li>
<li>Model the project\'s impact on travel times and costs for different population groups.</li>
<li>Ensure the location provides safe and dignified access for all, including people with disabilities.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(806, 82, 3, 'spatial_principles_3', 'Principle 3: Environmental and Climate Resilience', 'principle-3-environmental-and-climate-resilience-ml', 3, '{"description":"Aim: Integrate environmental and climate data to minimise ecological damage and ensure long-term project viability.<br>
Action: <br>
<ul>
<li> Overlay project sites with critical biodiversity areas, wetland maps, and agricultural land to avoid or minimise impact on sensitive ecosystems.</li>
<li>Conduct a **flood risk and sea-level rise analysis** using topographical and climate projection data.</li>
<li>Factor in **water scarcity** and **fire risk** maps, especially relevant for the Cape Floristic Region and Karoo biomes.</li>
<li>Promote green infrastructure solutions where possible (e.g., permeable pavements, natural water management).</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(807, 82, 4, 'spatial_principles_4', 'Principle 4: Evidence-Based and Data-Driven Decision Making', 'principle-4-evidence-based-and-data-driven-decision-making-ml', 4, '{"description":"Aim: Base all locational and design decisions on robust, current, and verifiable spatial data.<br>
Action: <br>
<ul>
<li>Create a centralised, project-specific spatial data hub containing all relevant datasets (e.g., Census, land use, geology, infrastructure networks).</li>
<li>Use Multi-Criteria Decision Analysis (MCDA) to objectively compare and score potential sites against weighted criteria (e.g., cost, environmental impact, social benefit).</li>
<li>Validate findings with ground-truthing and community engagement.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(808, 82, 5, 'spatial_principles_5', 'Principle 5: Economic Efficiency and Sustainable Growth', 'principle-5-economic-eEfficiency-and-sustainable-growth-ml', 5, '{"description":"Aim: Locate infrastructure to maximise its economic return and catalyse sustainable local economic development.<br>
Action: <br>
<ul>
<li>Analyse proximity to key economic drivers (e.g., industrial zones, tourism routes, agricultural hubs) to enhance connectivity and reduce logistics costs.</li>
<li>Assess the potential for the project to stimulate local economic opportunities and job creation.</li>
<li>Optimise routing for linear infrastructure (pipelines, roads) to reduce long-term operational and maintenance costs.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(809, 82, 6, 'spatial_principles_6', 'Principle 6: Risk Mitigation and Future-Proofing', 'principle-6-risk-mitigation-and-future-proofing-ml', 6, '{"description":"Aim: Proactively identify and mitigate spatial risks to protect the infrastructure investment over its entire lifecycle.<br>
Action: <br>
<ul>
<li>Conduct a geotechnical and geohazard susceptibility analysis (e.g., sinkholes, landslides, expansive soils).</li>
<li>Model future scenarios, such as population growth or climate change impacts, to ensure the infrastructure remains fit-for-purpose.</li>
<li>Identify land ownership constraints and potential for land invasion or conflict through historical spatial analysis.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(810, 82, 7, 'spatial_principles_7', 'Principle 7: Collaborative and Transparent Governance', 'principle-7-collaborative-and-transparent-governance-ml', 7, '{"description":"Aim: Use spatial analysis as a tool for clear communication and collaborative planning across departments and with the public. <br>
Action: <br>
<ul>
<li> Develop easy-to-understand maps and visualisations to facilitate stakeholder engagement and public participation processes.</li>
<li>Share spatial data and findings with other government departments (e.g., Environmental Affairs & Development Planning, Human Settlements) to ensure a "Whole of Government" approach.</li>
<li>Document the spatial methodology clearly so that decisions are transparent and reproducible.</li>
</ul>"}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


/*  3 4 and 5 section DEV[DONE] QA[DONE]  */


INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(83, 'conceptual_development_options', 'Conceptual Development Options', 'conceptual-development-options-ml', '', 1, 'No', '2025-11-14 11:30:00', 1, '2025-11-14 11:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(811, 83, 1, 'conceptual_development_options_1', 'Concept Designs', 'concept-designs-ml', 1, '{"description":""}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(812, 0, 1, 'conceptual_development_options_1_1', 'Description of development options', 'description-of-development-options-ml', 1, '{"description":""}', 'conceptual_development_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(813, 0, 2, 'conceptual_development_options_1_2', 'Common Elements', 'common-elements-ml', 2, '{"description":""}', 'conceptual_development_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(814, 0, 3, 'conceptual_development_options_1_3', 'Qualifications and limitations from a design perspective', 'qualifications-and-limitations-from-a-design-perspective-ml', 3, '{"description":""}', 'conceptual_development_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(815, 0, 4, 'conceptual_development_options_1_4', 'Characterisitics of the conceptual development options', 'characterisitics-of-the-conceptual-development-options-ml', 4, '{"description":""}', 'conceptual_development_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);



INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(816, 83, 2, 'conceptual_development_options_2', 'Financial Feasibility', 'Financial Feasibility-ml', 2, '{"description":""}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(817, 0, 1, 'conceptual_development_options_2_1', 'Background', 'Background-ml', 1, '{"description":""}', 'conceptual_development_options_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(818, 0, 2, 'conceptual_development_options_2_2', 'Land Valuation Approach', 'land-valuation-approach-ml', 2, '{"description":""}', 'conceptual_development_options_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(819, 0, 3, 'conceptual_development_options_2_3', 'Highest and Best Use Option', 'highest-and-best-Use-option-ml', 3, '{"description":""}', 'conceptual_development_options_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(84, 'preliminary_assessment_of_dev_options', 'Specialists Preliminary Assessment of Dev Options', 'preliminary-assessment-of-dev-options-ml', '', 1, 'No', '2025-11-14 11:30:00', 1, '2025-11-14 11:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(820, 84, 1, 'preliminary_assessment_of_dev_options_1', 'Preliminary Impacts and Assessments', 'preliminary-impacts-and-assessments-ml', 1, '{"description":""}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(821, 0, 1, 'preliminary_assessment_of_dev_options_1_1', 'Technical Viability', 'technical-viability-ml', 1, '{"description":"Ensures the design meets engineering standards and regulatory requirements. \n Assesses constructability and integration with existing infrastructure."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(822, 0, 2, 'preliminary_assessment_of_dev_options_1_2', 'Financial Implications', 'financial-implications-ml', 2, '{"description":"Evaluates cost estimates, lifecycle costs, and budget alignment. \n Identifies potential cost‑saving opportunities or risks."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);



INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(85, 'preferred_development_option_and_guidelines', 'Preferred Development Option and Guidelines', 'preferred-development-option-and-guidelines-ml', '', 1, 'No', '2025-11-14 11:30:00', 1, '2025-11-14 11:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(823, 85, 1, 'preferred_development_option_and_guidelines_1', 'Motivation for preferred Development Plan (Option 1/2 or 3)', 'motivation-for-preferred-development-plan-option-1-2-or-3)-ml', 1, '{"description":""}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(824, 85, 2, 'preferred_development_option_and_guidelines_2', 'Design / Architectural / Planning Guidelines', 'design-architectural-planning-guidelines)-ml', 2, '{"description":""}', '', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(825, 0, 1, 'preferred_development_option_and_guidelines_2_1', 'Design', 'design-ml', 2, '{"description":""}', 'preferred_development_option_and_guidelines_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(826, 0, 2, 'preferred_development_option_and_guidelines_2_2', 'Architecture', 'architecture-ml', 2, '{"description":""}', 'preferred_development_option_and_guidelines_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(827, 0, 3, 'preferred_development_option_and_guidelines_2_3', 'Planning Guidelines', 'planning-guidelines-ml', 3, '{"description":""}', 'preferred_development_option_and_guidelines_2', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(828, 85, 3, 'preferred_development_option_and_guidelines_3', 'Framework and Guidelines', 'framework-and-guidelines-ml', 3, '{"description":""}', '', 1, '2025-11-18 10:30:00', 1, '2025-11-18 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(829, 0, 1, 'preferred_development_option_and_guidelines_3_1', 'Design Principles', 'design-principles-ml', 1, '{"description":""}', 'preferred_development_option_and_guidelines_3', 1, '2025-11-18 10:30:00', 1, '2025-11-18 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(830, 0, 3, 'preliminary_assessment_of_dev_options_1_3', 'Socio‑Economic Impact', 'socio‑economic-impact-ml', 3, '{"description":"Considers job creation (e.g., EPWP targets), community upliftment, and service delivery improvements. \n Aligns with provincial development priorities."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(831, 0, 4, 'preliminary_assessment_of_dev_options_1_4', 'Environmental Sustainability', 'environmental-sustainability-ml', 4, '{"description":"Reviews environmental impact assessments and mitigation strategies. \n Promotes green building principles and climate resilience."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(832, 0, 5, 'preliminary_assessment_of_dev_options_1_5', 'Risk Management', 'risk-management-ml', 5, '{"description":"Identifies design‑related risks and proposes mitigation measures. \n Supports informed decision‑making by stakeholders."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(833, 0, 6, 'preliminary_assessment_of_dev_options_1_6', 'Compliance and Governance', 'compliance-and-governance-ml', 6, '{"description":"Aligns with WCG policies, including Infrastructure Delivery Management System (IDMS) and PRMG guidelines. \n Ensures transparency and accountability."}', 'preliminary_assessment_of_dev_options_1', 1, '2025-11-14 10:30:00', 1, '2025-11-14 10:30:00', 1);

-- Stage 5 section 5 DEV[DONE] QA [DONE]

INSERT INTO `tbl_lookup`
(`keyid`, `dev_name`, `display_name`, `display_name_ml`, `description`, `is_active`, `translate`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(86, 'preliminary_impacts_and_assessments_parameters', 'Preliminary Impacts and Assessments Parameters', 'preliminary-impacts-and-assessments-parameters-ml', '', 1, 'No', '2025-11-19 11:30:00', 1, '2025-11-19 11:30:00', 1);


INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(834, 86, 1, 'preliminary_impacts_and_assessments_parameters_1', 'Completeness', 'completeness-ml', 1, '{"description":"Are all required sections and data included?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(835, 86, 2, 'preliminary_impacts_and_assessments_parameters_2', 'Accuracy', 'accuracy-ml', 2, '{"description":"Are the technical and financial details correct and well-supported?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(836, 86, 3, 'preliminary_impacts_and_assessments_parameters_3', 'Alignment with Strategy', 'alignment-with-strategy-ml', 3, '{"description":"Does the design support WCG infrastructure goals and ministerial priorities?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(837, 86, 4, 'preliminary_impacts_and_assessments_parameters_4', 'Feasibility', 'feasibility-ml', 4, '{"description":"Is the project realistically deliverable within time, budget, and scope?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(838, 86, 5, 'preliminary_impacts_and_assessments_parameters_5', 'Stakeholder Engagement', 'stakeholder-engagement-ml', 5, '{"description":"Have relevant departments, communities, and experts been consulted?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(839, 86, 6, 'preliminary_impacts_and_assessments_parameters_6', 'Sustainability Measures', 'sustainability-measures-ml', 6, '{"description":"Are environmental and long-term maintenance factors addressed?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(840, 86, 7, 'preliminary_impacts_and_assessments_parameters_7', 'Risk Identification', 'risk-identification-ml', 7, '{"description":"Are risks clearly identified and mitigation plans provided?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);

INSERT INTO `tbl_lookup_detail`
(`optionid`, `keyid`, `data_value`, `data_key`, `display_name`, `display_name_ml`, `sequence`, `item_data`, `parent_datakey`, `is_active`, `creation_date`, `created_by`, `updation_date`, `updated_by`)
VALUES
(841, 86, 8, 'preliminary_impacts_and_assessments_parameters_8', 'Innovation and Efficiency', 'innovation-and-efficiency-ml', 8, '{"description":"Does the design incorporate modern, efficient, or innovative solutions?"}', '', 1, '2025-11-19 10:30:00', 1, '2025-11-19 10:30:00', 1);


-- Add new column options to stg5_keys table. DEV[DONE] QA[DONE]
ALTER TABLE `tbl_project_stg5_keys` ADD `options` VARCHAR(15) NULL DEFAULT NULL AFTER `sub_type_name`;

-- Add new category DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('44', 'Preliminary Impacts and Assessments', 'project', '2025-11-06 18:49:38', NULL, NULL, NULL);


----------------------------------------------------------------------------------------------------------------
-- DATE 20-11-2025 adding new table for priliminary assesment data store. DEV[DONE] QA[DONE]
CREATE TABLE `tbl_project_stg5_preferred_development_plan` (
    `row_id` INT(11) NOT NULL AUTO_INCREMENT,
    `project_id` INT(11) NOT NULL,
    `preferred_development_option` VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `preferred_development_justification` TEXT COLLATE utf8mb4_unicode_ci NOT NULL,
    `preferred_development_table_option` TEXT COLLATE utf8mb4_unicode_ci NOT NULL,
    `created_by` INT(11) NOT NULL,
    `updated_by` INT(11) NOT NULL,
    `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

---------------------------------------------------------------------------------------------------
-- DATE 21-11-2025 Updates in stg5_stakeholders table for allow null in columns DEV[DONE] QA[DONE]
ALTER TABLE `tbl_project_stg5_stakeholders` CHANGE `user_email` `user_email` VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg5_stakeholders` CHANGE `user_name` `user_name` VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';
ALTER TABLE `tbl_project_stg5_keys` CHANGE `conclusion` `conclusion` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `tbl_project_stg5_keys` CHANGE `document_ids` `document_ids` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';
 
---------------------------------------------------------------------------------------------------
-- DATE 21-11-2025 Adding new category for design development report DEV[DONE] QA[DONE]
INSERT INTO `tbl_categories` (`cat_id`, `cat_name`, `type`, `creation_date`, `created_by`, `updation_date`, `updated_by`) VALUES ('54', 'Design Development Report', 'project', '2025-11-17 10:10:59', NULL, NULL, NULL);

-----------------------------------------------------------------------------------------------------
-- DATE 24-11-2025 Add and updated tables. DEV[DONE] QA[DONE]
ALTER TABLE `tbl_departments` ADD `descrpition` TEXT NULL AFTER `department_short_name`;

-- New Table tbl_department_roles DEV[DONE] QA[DONE] : ignore this and use tbl-department-roles.sql file for creating table and inserting data.
CREATE TABLE tbl_department_roles (`id` INT NOT NULL AUTO_INCREMENT , `department_id` INT NOT NULL , `role_id` INT NOT NULL , `is_active` TINYINT NOT NULL DEFAULT '1' , PRIMARY KEY (`id`)) ENGINE = InnoDB;
ALTER TABLE tbl_department_roles ADD UNIQUE KEY uniq_dept_role (department_id, role_id);

-- New table tbl_project_dept_phase_tasks DEV[DONE] QA[DONE]
CREATE TABLE tbl_project_dept_phase_tasks (
        `id` INT NOT NULL AUTO_INCREMENT,
        `project_id` INT NOT NULL DEFAULT '0',
        `department_id` INT NOT NULL,
        `phase_id` INT NOT NULL,
        `task_name` VARCHAR(500) NOT NULL,
        `parent_id` INT NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
ALTER TABLE tbl_project_dept_phase_tasks ADD `dependent_task_id` INT NULL DEFAULT NULL AFTER `parent_id`;

-- Table updates: table: role_master.

-- add role_type and set default type project DEV[DONE] QA[DONE]
ALTER TABLE `tbl_role_master` ADD `role_type` ENUM('global','project') NOT NULL DEFAULT 'project' AFTER `app_id`;
-- update role_type global for role_id  1 and 2 DEV[DONE] QA[DONE]
UPDATE `tbl_role_master` SET `role_type`='global' where `role_id` in (1,2)

-- delete manager role DEV[DONE] QA[DONE]
DELETE FROM tbl_role_master WHERE `tbl_role_master`.`role_id` = 7;

-- Insert new roles DEV[DONE] QA[DONE] // Ignore this and use tbl-department-roles.sql file for inserting roles.
INSERT INTO `tbl_role_master` VALUES ('7', '1', 'project', 'Project Manager', 'Project Manager', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('8', '1', 'project', 'Agricultural Engineer', 'Agricultural Engineer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('9', '1', 'project', 'Agricultural Strategist', 'Agricultural Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('10', '1', 'project', 'Agri-Economist', 'Agri-Economist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('11', '1', 'project', 'Agri-Tech Designer', 'Agri-Tech Designer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('12', '1', 'project', 'Agri-Tech Lead', 'Agri-Tech Lead', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('13', '1', 'project', 'Architect', 'Architect', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('14', '1', 'project', 'Community Liaison', 'Community Liaison', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('15', '1', 'project', 'Community Planner', 'Community Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('16', '1', 'project', 'Construction Manager', 'Construction Manager', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('17', '1', 'project', 'Crime Analyst', 'Crime Analyst', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('18', '1', 'project', 'Cultural Strategist', 'Cultural Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('19', '1', 'project', 'Data Analyst', 'Data Analyst', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('20', '1', 'project', 'Development Coordinator', 'Development Coordinator', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('21', '1', 'project', 'Digital Infrastructure Planner', 'Digital Infrastructure Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('22', '1', 'project', 'Digital Planner', 'Digital Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('23', '1', 'project', 'ECD Specialist', 'ECD Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('24', '1', 'project', 'Education Economist', 'Education Economist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('25', '1', 'project', 'Education Planner', 'Education Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('26', '1', 'project', 'Education Strategist', 'Education Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('27', '1', 'project', 'Engineers', 'Engineers', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('28', '1', 'project', 'Engineers (Civil, Structural, etc.)', 'Engineers (Civil, Structural, etc.)', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('29', '1', 'project', 'Environmental & Social Risk Assessor', 'Environmental & Social Risk Assessor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('30', '1', 'project', 'Environmental Assessor', 'Environmental Assessor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('31', '1', 'project', 'Environmental Risk Assessor', 'Environmental Risk Assessor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('32', '1', 'project', 'Extension Officer', 'Extension Officer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('33', '1', 'project', 'Facility Manager', 'Facility Manager', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('34', '1', 'project', 'GIS Analyst', 'GIS Analyst', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('35', '1', 'project', 'Health IT Specialist', 'Health IT Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('36', '1', 'project', 'Health Planner', 'Health Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('37', '1', 'project', 'Heritage Advisor', 'Heritage Advisor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('38', '1', 'project', 'Housing Economist', 'Housing Economist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('39', '1', 'project', 'Housing Strategist', 'Housing Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('40', '1', 'project', 'HR Strategist', 'HR Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('41', '1', 'project', 'ICT Installer', 'ICT Installer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('42', '1', 'project', 'ICT Planner', 'ICT Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('43', '1', 'project', 'Inclusion Specialist', 'Inclusion Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('44', '1', 'project', 'Infrastructure Manager', 'Infrastructure Manager', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('45', '1', 'project', 'Infrastructure Strategist', 'Infrastructure Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('46', '1', 'project', 'Innovation Lead', 'Innovation Lead', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('47', '1', 'project', 'Irrigation Specialist', 'Irrigation Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('48', '1', 'project', 'Legal Advisor', 'Legal Advisor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('49', '1', 'project', 'Mobility Strategist', 'Mobility Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('50', '1', 'project', 'Monitoring Specialist', 'Monitoring Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('51', '1', 'project', 'Operations Manager', 'Operations Manager', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('52', '1', 'project', 'Operations Planner', 'Operations Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('53', '1', 'project', 'Procurement Officer', 'Procurement Officer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('55', '1', 'project', 'Quality Officer', 'Quality Officer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('56', '1', 'project', 'Resilience Specialist', 'Resilience Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('57', '1', 'project', 'Safety Analyst', 'Safety Analyst', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('58', '1', 'project', 'Safety Planner', 'Safety Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('59', '1', 'project', 'Safety Strategist', 'Safety Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('60', '1', 'project', 'Security Engineer', 'Security Engineer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('61', '1', 'project', 'Security Installer', 'Security Installer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('62', '1', 'project', 'Skills Coordinator', 'Skills Coordinator', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('63', '1', 'project', 'Skills Development Coordinator', 'Skills Development Coordinator', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('64', '1', 'project', 'Smart Mobility Engineer', 'Smart Mobility Engineer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('65', '1', 'project', 'Smart Mobility Lead', 'Smart Mobility Lead', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('66', '1', 'project', 'Social Impact Assessor', 'Social Impact Assessor', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('67', '1', 'project', 'Social Infrastructure Strategist', 'Social Infrastructure Strategist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('68', '1', 'project', 'Social Officer', 'Social Officer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('69', '1', 'project', 'Social Services Planner', 'Social Services Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('70', '1', 'project', 'Stakeholder Engagement Lead', 'Stakeholder Engagement Lead', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('71', '1', 'project', 'Sustainability Engineer', 'Sustainability Engineer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('72', '1', 'project', 'Sustainability Specialist', 'Sustainability Specialist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('73', '1', 'project', 'Traffic Modeller', 'Traffic Modeller', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('74', '1', 'project', 'Training Coordinator', 'Training Coordinator', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('75', '1', 'project', 'Transport Economist', 'Transport Economist', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('76', '1', 'project', 'Transport Engineer', 'Transport Engineer', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('77', '1', 'project', 'Transport Planner', 'Transport Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('78', '1', 'project', 'Urban Planner', 'Urban Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);
INSERT INTO `tbl_role_master` VALUES ('79', '1', 'project', 'Workforce Planner', 'Workforce Planner', '0', '1', '2025-07-16 17:59:47', '1', '2025-11-24 11:16:06', NULL);

-- Table structure for table `tbl_phases` DEV[DONE] QA[DONE]
CREATE TABLE `tbl_phases` (`id` int(11) NOT NULL,`phase_name` varchar(500) NOT NULL,`is_active` tinyint(4) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table `tbl_phases` DEV[DONE] QA[DONE]

INSERT INTO `tbl_phases` (`id`, `phase_name`, `is_active`) VALUES(1, 'PHASE 1: SITE ENABLEMENT', 1),(2, 'PHASE 2: CONSTRUCTION', 1),(3, 'PHASE 3: HANDOVER', 1);

-- Indexes for table `tbl_phases` DEV[DONE] QA[DONE]
ALTER TABLE `tbl_phases` ADD PRIMARY KEY (`id`); COMMIT;

-- Table entries DEV[DONE] QA[DONE]

TRUNCATE TABLE tbl_project_dept_phase_tasks;
START TRANSACTION;

INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Prepare the site for construction with safety and accessibility in mind', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Site selection based on crime data and community needs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Environmental and social impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Stakeholder consultations (SAPS, CPF, local communities)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Utility connections (electricity, water, ICT for surveillance systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 1, 'Planning for service continuity and temporary safety measures', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Build infrastructure that supports policing oversight and community safety', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Foundation and structural works (safety centers, mobile command units, community safety offices)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Construction of public safety amenities (safe zones, lighting, emergency kiosks)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Installation of surveillance systems (CCTV, license plate recognition, panic buttons)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'ICT infrastructure for data sharing, case tracking, and public reporting', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Accessibility features (universal design, signage, multilingual interfaces)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Renewable energy systems (solar-powered lighting, backup systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 2, 'Safety systems (fire suppression, secure access control, perimeter monitoring)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Operationalize the infrastructure and transfer to oversight and community teams', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Handover documentation (as-built drawings, O&M manuals, surveillance protocols)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Staff training and induction (oversight officers, community safety volunteers)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Community orientation and safety awareness campaigns', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (14, 3, 'Official launch and stakeholder engagement event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Prepare the site for transport infrastructure development', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Route and site selection based on transport demand and spatial equity', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Environmental and traffic impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Stakeholder consultations (municipalities, commuters, operators)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Temporary access roads, fencing, and signage', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Utility connections (electricity, water, ICT for smart systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 1, 'Planning for service continuity and detour routes', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Build core mobility infrastructure', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Roadworks and paving (bus lanes, cycle paths, pedestrian walkways)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Construction of transport hubs (interchanges, taxi ranks, bus terminals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Installation of smart mobility systems (real-time tracking, ticketing tech)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Electrical and mechanical systems (lighting, ventilation, solar panels)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Accessibility features (ramps, tactile paving, multilingual signage)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Safety systems (CCTV, emergency call points, traffic calming measures)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 2, 'Landscaping and urban integration (green buffers, seating, shade structures)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Operationalize the infrastructure and transfer to transport authorities', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Staff training and induction for operators and maintenance teams', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Public orientation and commuter education campaigns', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (7, 3, 'Official launch and stakeholder engagement event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Prepare the site for housing development', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Land acquisition and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Environmental and social impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Community engagement and relocation planning', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Utility connections (water, electricity, sanitation, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 1, 'Planning for service continuity and transitional housing if needed', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Build housing and supporting infrastructure', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Foundation and structural works (housing units, multi-storey buildings)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Installation of basic services (water, sanitation, electricity)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Construction of public amenities (parks, community halls, schools)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Road and transport infrastructure (internal roads, pedestrian paths)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'ICT infrastructure for digital inclusion', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Safety systems (street lighting, fire safety, surveillance)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 2, 'Landscaping and green infrastructure (trees, stormwater systems, recreational spaces)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Transfer completed housing and infrastructure to residents and municipalities', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Handover documentation (title deeds, service manuals, O&M plans)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Resident orientation and community integration programs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (12, 3, 'Official handover and community launch event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Prepare the site and ensure readiness for construction', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Site selection and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Environmental and health impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Stakeholder consultations (health workers, communities, planners)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 1, 'Planning for service continuity during construction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Build core health infrastructure', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Foundation and structural works (clinics, hospitals, mobile units)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Specialized areas (emergency rooms, operating theatres, maternity wards)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Electrical and mechanical systems (HVAC, backup power, medical gas)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'ICT infrastructure for digital health records, telemedicine, diagnostics', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Accessibility features (universal design, ramps, signage, inclusive toilets)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Renewable energy systems (solar panels, energy-efficient lighting)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 2, 'Safety systems (fire suppression, infection control zones, surveillance)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Operationalize the facility and transfer to health service teams', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Staff training and induction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Community orientation and health education programs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (2, 3, 'Official handover and launch event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Prepare the site with minimal ecological disruption', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Site selection based on environmental sensitivity', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Environmental impact assessments (EIA) and heritage screening', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Stakeholder consultations (communities, conservation bodies, planners)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Site clearance with biodiversity preservation protocols', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Installation of erosion and sediment control systems', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Temporary access roads and fencing with minimal footprint', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 1, 'Utility connections using low-impact methods (solar, greywater systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Build infrastructure that supports environmental and planning goals', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Foundation and structural works using sustainable materials', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Construction of green buildings (offices, monitoring stations, eco-centers)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Installation of renewable energy systems (solar, wind, biogas)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Stormwater management systems (bioswales, permeable paving)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Waste management infrastructure (recycling stations, composting units)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'ICT systems for environmental monitoring and spatial planning', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 2, 'Landscaping with indigenous vegetation and habitat restoration', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Operationalize the facility and integrate with planning systems', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Final inspections and environmental compliance audits', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Handover documentation (as-built drawings, environmental management plans)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Staff training on sustainable operations and monitoring tools', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Community orientation and environmental education programs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (16, 3, 'Official handover and awareness launch event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Prepare the site and ensure readiness for construction', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Site selection and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Environmental and social impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Stakeholder consultations (school boards, communities, learners)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 1, 'Planning for learning continuity during construction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Build core education infrastructure', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Foundation and structural works (classrooms, admin blocks, libraries)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Specialized spaces (science labs, computer rooms, inclusive education units)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'ICT infrastructure for e-learning, smart boards, and connectivity', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Accessibility features (universal design, ramps, signage, inclusive toilets)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Renewable energy systems (solar panels, energy-efficient lighting)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Safety systems (fire suppression, emergency exits, surveillance)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 2, 'Landscaping and green infrastructure (playgrounds, gardens, stormwater systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Operationalize the facility and transfer to education teams', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Staff training and induction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Learner and community orientation', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (3, 3, 'Official handover and school opening event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Prepare the site for construction and ensure regulatory readiness', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Site selection and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Environmental and economic impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Stakeholder consultations (local businesses, tourism boards, communities)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 1, 'Planning for business continuity and local engagement', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Build infrastructure that supports economic and tourism development', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Foundation and structural works (business parks, tourism centers, marketplaces)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Construction of tourism amenities (visitor centers, trails, signage, rest areas)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'ICT infrastructure for digital tourism and smart business services', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Accessibility features (universal design, transport links, signage)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Renewable energy systems and green building practices', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Safety systems (fire suppression, surveillance, crowd control)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 2, 'Landscaping and beautification (green spaces, public art, lighting)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Operationalize the facility and transfer to economic development teams or operators', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Staff and operator training', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Community orientation and business activation programs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (10, 3, 'Official launch and promotional event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Prepare the site and ensure readiness for construction', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Site selection and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Environmental and heritage impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Community engagement and stakeholder consultations', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 1, 'Planning for service continuity during construction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Build core infrastructure for cultural and sports activities', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Foundation and structural works (community halls, libraries, museums, sports fields)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Indoor and outdoor recreational spaces (courts, stages, amphitheaters)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Accessibility features (universal design, ramps, signage)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Electrical and mechanical systems (lighting, sound, HVAC, backup power)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'ICT infrastructure for digital exhibitions, e-learning, and event management', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Safety systems (fire suppression, crowd control, surveillance)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 2, 'Landscaping and green infrastructure (gardens, walking paths, stormwater systems)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Operationalize the facility and transfer to service teams', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Staff training and induction', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Community orientation and program launch', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (15, 3, 'Official handover and cultural/sporting launch event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Prepare the site for construction and ensure regulatory compliance', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Land acquisition and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Environmental impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Soil and water testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Community engagement and stakeholder consultations', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Temporary access roads and fencing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Build core infrastructure to support agricultural operations', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Foundation and structural works (storage, processing units, admin buildings)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Irrigation systems and water storage facilities', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Renewable energy installations (solar, biogas)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Cold chain and logistics infrastructure', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'ICT infrastructure for smart farming', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Safety systems and compliance inspections', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 2, 'Landscaping and erosion control', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Operationalize the infrastructure and transfer to managing entities', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Final inspections and performance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Handover documentation (as-built drawings, manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Training for facility operators and farmers', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Community induction and support programs', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (1, 3, 'Official handover ceremony', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Prepare the site for construction with community and environmental considerations', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Site selection based on community needs and accessibility', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Environmental and social impact assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Stakeholder engagement (NGOs, community leaders, service users)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Utility connections (water, electricity, sanitation, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 1, 'Planning for service continuity (temporary facilities or mobile units)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Build infrastructure that supports social development services', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Foundation and structural works (ECD centres, shelters, youth care centres, service offices)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Construction of multipurpose spaces (training rooms, counselling areas, kitchens)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Accessibility features (universal design, ramps, signage, inclusive toilets)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'ICT infrastructure for case management, digital learning, and remote services', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Renewable energy systems (solar panels, energy-efficient lighting)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Safety systems (fire suppression, secure access, child protection features)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 2, 'Landscaping and community integration (playgrounds, gardens, safe outdoor areas)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Operationalize the facility and transfer to service providers or departmental teams', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Handover documentation (as-built drawings, O&M manuals, service protocols)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Staff training and induction (social workers, caregivers, admin staff)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Community orientation and awareness campaigns', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (11, 3, 'Official handover and community launch event', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Prepare the site for construction and ensure regulatory readiness', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Site selection and zoning verification', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Environmental and geotechnical assessments', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Stakeholder consultations (municipalities, engineers, communities)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Site clearance and grading', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Temporary access roads, fencing, and security', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Utility connections (water, electricity, ICT)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 1, 'Planning for service continuity and traffic management', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Build core infrastructure assets', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Foundation and structural works (roads, bridges, public buildings)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Installation of utility systems (stormwater, sewerage, electrical)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Construction of transport links and logistics corridors', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'ICT infrastructure for smart systems and asset monitoring', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Accessibility features (universal design, signage, transport integration)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Renewable energy systems (solar, energy-efficient lighting)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Safety systems (fire suppression, structural integrity, surveillance)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 2, 'Landscaping and urban integration (green buffers, pedestrian zones)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Operationalize the infrastructure and transfer to managing entities', 0);
SET @p = LAST_INSERT_ID();
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Final inspections and compliance testing', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Defect resolution and quality assurance', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Handover documentation (as-built drawings, O&M manuals)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Staff training and induction (municipal teams, operators)', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Community orientation and public awareness campaigns', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Monitoring and evaluation framework setup', @p);
INSERT INTO tbl_project_dept_phase_tasks (department_id, phase_id, task_name, parent_id) VALUES (4, 3, 'Official launch and stakeholder engagement event', @p);
COMMIT;

-- Table tbl_project_dept_phase_tasks updated DEV[DONE] QA[DONE]
ALTER TABLE `tbl_project_dept_phase_tasks` ADD `document_id` INT NULL DEFAULT NULL AFTER `parent_id`, 
    ADD `role_id` INT NULL DEFAULT NULL AFTER `document_id`, 
    ADD `no_of_resources` INT NULL DEFAULT NULL AFTER `role_id`, 
    ADD `allocated_budget` DECIMAL(15,2) NOT NULL DEFAULT '0.00' AFTER `no_of_resources`, 
    ADD `rate_per_hour` DECIMAL(10,2) NOT NULL DEFAULT '0.00' AFTER `allocated_budget`, 
    ADD `hours_of_work` DECIMAL(10,2) NOT NULL DEFAULT '0' AFTER `rate_per_hour`, 
    ADD `type` ENUM('capex','opex') NULL DEFAULT NULL AFTER `hours_of_work`, 
    ADD `start_date` DATE NULL DEFAULT NULL AFTER `type`, 
    ADD `end_date` DATE NULL DEFAULT NULL AFTER `start_date`, 
    ADD `progress` DECIMAL(5,2) NULL DEFAULT '0.00' AFTER `end_date`,
    ADD `status` VARCHAR(50) NULL DEFAULT NULL AFTER `progress`, 
    ADD `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, 
    ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  
 -- Add new table tbl_project_plan_data DEV[DONE] QA[DONE]
