-- Ensures `orac_form_details` and `orac_form_section_map` exist in the hse database
-- and seeds a default form (form_id = 1) linked to section_id = 1.
-- Safe to re-run: uses CREATE TABLE IF NOT EXISTS and INSERT IGNORE.

CREATE TABLE IF NOT EXISTS `orac_form_details` (
  `form_id`       INT          NOT NULL AUTO_INCREMENT,
  `form_parent_id` INT         DEFAULT NULL,
  `title`         VARCHAR(255) DEFAULT NULL,
  `description`   TEXT,
  `created_by`    INT          DEFAULT NULL,
  `created_date`  DATETIME     DEFAULT NULL,
  PRIMARY KEY (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orac_form_section_map` (
  `form_id`    INT NOT NULL,
  `section_id` INT NOT NULL,
  PRIMARY KEY (`form_id`, `section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default ORAC form (idempotent)
INSERT IGNORE INTO `orac_form_details` (`form_id`, `title`, `created_date`)
VALUES (1, 'Default ORAC Form', NOW());

-- Link default form to section 1 (adjust section_id if needed)
INSERT IGNORE INTO `orac_form_section_map` (`form_id`, `section_id`)
VALUES (1, 1);
