-- PTW (Permit to Work) Admin Dashboard schema.
-- Aligned with the live `hse` database ERD (no ptw_section_type / workflow extension tables).

-- ------------------------------------------------------------------
-- MASTER TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `job_title` (
  `job_title_id` INT AUTO_INCREMENT PRIMARY KEY,
  `job_title_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` INT AUTO_INCREMENT PRIMARY KEY,
  `role_name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(500) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `account_status` (
  `status_id` INT AUTO_INCREMENT PRIMARY KEY,
  `status_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `departments` (
  `department_id` INT AUTO_INCREMENT PRIMARY KEY,
  `department_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `country` (
  `country_id` INT AUTO_INCREMENT PRIMARY KEY,
  `country_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `city` (
  `city_id` INT AUTO_INCREMENT PRIMARY KEY,
  `city_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tickets_status` (
  `status_id` INT AUTO_INCREMENT PRIMARY KEY,
  `status_type` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tickets_type` (
  `type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `type_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_form_type` (
  `form_type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `form_type_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_job_type` (
  `job_type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `job_type_name` VARCHAR(255) NOT NULL,
  `job_colour_code` VARCHAR(7) NULL DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_question_type` (
  `question_type_id` INT AUTO_INCREMENT PRIMARY KEY,
  `question_type` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_permit_status` (
  `permit_status_id` INT AUTO_INCREMENT PRIMARY KEY,
  `permit_status_name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- LOCATION TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `locations` (
  `location_id` INT AUTO_INCREMENT PRIMARY KEY,
  `country_id` INT NULL,
  `city_id` INT NULL,
  `location_name` VARCHAR(255) NOT NULL,
  CONSTRAINT `fk_locations_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`),
  CONSTRAINT `fk_locations_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- USER MANAGEMENT TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` VARCHAR(255) NULL,
  `status_id` INT NULL,
  `employee_name` VARCHAR(255) NOT NULL,
  `job_title_id` INT NULL,
  `email_id` VARCHAR(255) NULL,
  `phonenumber` VARCHAR(50) NULL,
  `department_id` INT NULL,
  `location_id` INT NULL,
  `photo` LONGBLOB NULL,
  `password` VARCHAR(255) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_status` FOREIGN KEY (`status_id`) REFERENCES `account_status` (`status_id`),
  CONSTRAINT `fk_users_job_title` FOREIGN KEY (`job_title_id`) REFERENCES `job_title` (`job_title_id`),
  CONSTRAINT `fk_users_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `fk_users_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user_role` (
  `user_role_id` INT AUTO_INCREMENT PRIMARY KEY,
  `role_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  CONSTRAINT `fk_user_role_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`),
  CONSTRAINT `fk_user_role_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `users_logs` (
  `log_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `employee_id` VARCHAR(255) NULL,
  `role` VARCHAR(500) NULL,
  `status` VARCHAR(255) NULL,
  `employee_name` VARCHAR(255) NULL,
  `phonenumber` VARCHAR(50) NULL,
  `department` VARCHAR(255) NULL,
  `location` VARCHAR(255) NULL,
  `photo` LONGBLOB NULL,
  `password` VARCHAR(255) NULL,
  `action` VARCHAR(50) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `location_departments` (
  `department_id` INT NOT NULL,
  `location_id` INT NOT NULL,
  PRIMARY KEY (`department_id`, `location_id`),
  CONSTRAINT `fk_loc_dep_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `fk_loc_dep_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `admin_level_user_multi_city_map` (
  `user_id` INT NOT NULL,
  `city_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `city_id`),
  CONSTRAINT `fk_multi_city_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_multi_city_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- TICKETING TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `ticket_type_id` INT NULL,
  `status_id` INT NULL,
  `description` TEXT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `closed_at` DATETIME NULL,
  CONSTRAINT `fk_tickets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_tickets_type` FOREIGN KEY (`ticket_type_id`) REFERENCES `tickets_type` (`type_id`),
  CONSTRAINT `fk_tickets_status` FOREIGN KEY (`status_id`) REFERENCES `tickets_status` (`status_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `tickets_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NULL,
  `user_id` INT NULL,
  `status_id` INT NULL,
  `ticket_type_id` INT NULL,
  `description` TEXT NULL,
  `created_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  CONSTRAINT `fk_tickets_logs_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- PTW FORM TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ptw_form_details` (
  `form_id` INT AUTO_INCREMENT PRIMARY KEY,
  `form_parent_id` INT NULL,
  `form_type_id` INT NULL,
  `job_type_id` INT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `created_by` INT NULL,
  `created_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_form_type` FOREIGN KEY (`form_type_id`) REFERENCES `ptw_form_type` (`form_type_id`),
  CONSTRAINT `fk_form_job_type` FOREIGN KEY (`job_type_id`) REFERENCES `ptw_job_type` (`job_type_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_location_forms_details` (
  `form_id` INT NOT NULL,
  `location_id` INT NOT NULL,
  PRIMARY KEY (`form_id`, `location_id`),
  CONSTRAINT `fk_lfd_form` FOREIGN KEY (`form_id`) REFERENCES `ptw_form_details` (`form_id`),
  CONSTRAINT `fk_lfd_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_archive_form` (
  `form_id` INT NOT NULL PRIMARY KEY,
  `archived_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_archive_form` FOREIGN KEY (`form_id`) REFERENCES `ptw_form_details` (`form_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_form_logs` (
  `log_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `form_id` INT NULL,
  `form_parent_id` INT NULL,
  `title` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `form_type_name` VARCHAR(255) NULL,
  `job_type_name` VARCHAR(255) NULL,
  `created_by` INT NULL,
  `created_date` DATETIME NULL,
  `location_name` VARCHAR(500) NULL,
  `action` VARCHAR(50) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_form_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- PTW SECTION TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ptw_section_details` (
  `section_id` INT AUTO_INCREMENT PRIMARY KEY,
  `section_parent_id` INT NULL,
  `title` VARCHAR(255) NOT NULL,
  `created_by` INT NULL,
  `created_date` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_archive_section` (
  `section_id` INT NOT NULL PRIMARY KEY,
  `archived_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_archive_section` FOREIGN KEY (`section_id`) REFERENCES `ptw_section_details` (`section_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_section_logs` (
  `log_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `section_id` INT NULL,
  `section_parent_id` INT NULL,
  `title` VARCHAR(255) NULL,
  `created_by` INT NULL,
  `created_date` DATETIME NULL,
  `location_name` VARCHAR(500) NULL,
  `action` VARCHAR(50) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_section_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- PTW QUESTION TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ptw_questions` (
  `question_id` INT AUTO_INCREMENT PRIMARY KEY,
  `question` VARCHAR(1000) NOT NULL,
  `question_type_id` INT NULL,
  `allows_media` TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT `fk_question_type` FOREIGN KEY (`question_type_id`) REFERENCES `ptw_question_type` (`question_type_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_question_options` (
  `question_options_id` INT AUTO_INCREMENT PRIMARY KEY,
  `question_id` INT NOT NULL,
  `_options` VARCHAR(255) NULL,
  CONSTRAINT `fk_option_question` FOREIGN KEY (`question_id`) REFERENCES `ptw_questions` (`question_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_section_question_map` (
  `section_id` INT NOT NULL,
  `question_id` INT NOT NULL,
  PRIMARY KEY (`section_id`, `question_id`),
  CONSTRAINT `fk_sqm_section` FOREIGN KEY (`section_id`) REFERENCES `ptw_section_details` (`section_id`),
  CONSTRAINT `fk_sqm_question` FOREIGN KEY (`question_id`) REFERENCES `ptw_questions` (`question_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_form_section_map` (
  `form_id` INT NOT NULL,
  `section_id` INT NOT NULL,
  `section_in_form_order` INT NULL,
  `skipable` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = user may skip this section for this form',
  PRIMARY KEY (`form_id`, `section_id`),
  CONSTRAINT `fk_fsm_form` FOREIGN KEY (`form_id`) REFERENCES `ptw_form_details` (`form_id`),
  CONSTRAINT `fk_fsm_section` FOREIGN KEY (`section_id`) REFERENCES `ptw_section_details` (`section_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_alternative_sections` (
  `skipable_section_id` INT NOT NULL,
  `other_section_id` INT NOT NULL,
  PRIMARY KEY (`skipable_section_id`, `other_section_id`),
  CONSTRAINT `fk_alt_skipable` FOREIGN KEY (`skipable_section_id`) REFERENCES `ptw_section_details` (`section_id`),
  CONSTRAINT `fk_alt_other` FOREIGN KEY (`other_section_id`) REFERENCES `ptw_section_details` (`section_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- PTW PERMIT TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ptw_permits` (
  `permit_id` INT AUTO_INCREMENT PRIMARY KEY,
  `permit_number` VARCHAR(255) NULL,
  `uploader_user_id` INT NOT NULL,
  `permit_status_id` INT NULL,
  `country_id` INT NULL,
  `location_id` INT NULL,
  `department_id` INT NULL,
  `valid_from` DATETIME NULL,
  `valid_until` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_permit_uploader` FOREIGN KEY (`uploader_user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_permit_status` FOREIGN KEY (`permit_status_id`) REFERENCES `ptw_permit_status` (`permit_status_id`),
  CONSTRAINT `fk_permit_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`),
  CONSTRAINT `fk_permit_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  CONSTRAINT `fk_permit_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_submissions` (
  `submission_id` INT AUTO_INCREMENT PRIMARY KEY,
  `permit_id` INT NOT NULL,
  `submitter_id` INT NOT NULL,
  `form_id` INT NULL,
  `submission_role` VARCHAR(32) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_submission_permit` FOREIGN KEY (`permit_id`) REFERENCES `ptw_permits` (`permit_id`),
  CONSTRAINT `fk_submission_submitter` FOREIGN KEY (`submitter_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_submission_form` FOREIGN KEY (`form_id`) REFERENCES `ptw_form_details` (`form_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_opening_closing_map` (
  `permit_id` INT NOT NULL,
  `opening_sub_id` INT NOT NULL,
  `closing_sub_id` INT NULL,
  PRIMARY KEY (`permit_id`, `opening_sub_id`),
  CONSTRAINT `fk_ocm_permit` FOREIGN KEY (`permit_id`) REFERENCES `ptw_permits` (`permit_id`),
  CONSTRAINT `fk_ocm_opening` FOREIGN KEY (`opening_sub_id`) REFERENCES `ptw_submissions` (`submission_id`),
  CONSTRAINT `fk_ocm_closing` FOREIGN KEY (`closing_sub_id`) REFERENCES `ptw_submissions` (`submission_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- PTW ANSWER TABLES
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ptw_answers` (
  `answers_id` INT AUTO_INCREMENT PRIMARY KEY,
  `submission_id` INT NOT NULL,
  `question_id` INT NOT NULL,
  `is_media` TINYINT(1) DEFAULT 0,
  `answer_value` TEXT NULL,
  CONSTRAINT `fk_answers_submission` FOREIGN KEY (`submission_id`) REFERENCES `ptw_submissions` (`submission_id`),
  CONSTRAINT `fk_answers_question` FOREIGN KEY (`question_id`) REFERENCES `ptw_questions` (`question_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `ptw_answers_media` (
  `answers_id` INT NOT NULL,
  `occurrence` INT NOT NULL,
  `media` LONGBLOB NULL,
  PRIMARY KEY (`answers_id`, `occurrence`),
  CONSTRAINT `fk_answers_media_answer` FOREIGN KEY (`answers_id`) REFERENCES `ptw_answers` (`answers_id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------
-- SEED: job types (GENERAL first, then specialized)
-- ------------------------------------------------------------------
INSERT IGNORE INTO `ptw_job_type` (`job_type_id`, `job_type_name`) VALUES
  (1, 'GENERAL'),
  (2, 'HOT WORK');

-- ------------------------------------------------------------------
-- SEED: question types
-- ------------------------------------------------------------------
INSERT IGNORE INTO `ptw_question_type` (`question_type`) VALUES
  ('Authorisation acceptance'),
  ('Permit Authorization');

-- ------------------------------------------------------------------
-- SEED: permit statuses (FK target for ptw_permits.permit_status_id)
-- ------------------------------------------------------------------
INSERT IGNORE INTO `ptw_permit_status` (`permit_status_id`, `permit_status_name`) VALUES
  (1, 'Draft'),
  (2, 'Active'),
  (3, 'Closed');
