CREATE TABLE `swi_job_area` (
    `job_area_id` integer PRIMARY KEY,
    `job_area_name` varchar(255)
);
CREATE TABLE `swi_document_status` (
    `document_status_id` integer PRIMARY KEY,
    `document_status_name` varchar(255)
);
CREATE TABLE `swi_document` (
    `document_id` integer,
    `document_number` integer,
    `document_name` varchar(255),
    `document_status_id` integer,
    `assigned_to_user_id` integer,
    `previous_document_id` integer,
    `location_id` integer,
    `department_id` integer,
    `job_area_id` integer,
    `process_name` varchar(255),
    `page_count` integer,
    `issued_at` timestamp,
    `assigned_at` timestamp,
    `completed_at` timestamp,
    `form_draft` json,
    PRIMARY KEY (`document_id`, `document_number`)
);
CREATE TABLE `swi_text_blocks` (
    `text_block_id` integer PRIMARY KEY AUTO_INCREMENT,
    `page_id` integer,
    `block_index` tinyint NOT NULL DEFAULT 1,
    `text_block` text,
    `block_height_px` smallint unsigned DEFAULT NULL,
    `logo_slot_count` tinyint unsigned DEFAULT NULL
);
CREATE TABLE `swi_text_block_icons` (
    `block_icon_id` integer PRIMARY KEY AUTO_INCREMENT,
    `text_block_id` integer,
    `icon_id` integer
);
CREATE TABLE `swi_images` (
    `image_id` integer PRIMARY KEY AUTO_INCREMENT,
    `page_id` integer,
    `slot_index` tinyint NOT NULL DEFAULT 1,
    `block_index` tinyint unsigned DEFAULT NULL,
    `cell_row_span` tinyint unsigned NOT NULL DEFAULT 1,
    `image` longblob,
    `pos_x` decimal(6,2) DEFAULT NULL,
    `pos_y` decimal(6,2) DEFAULT NULL,
    `width` decimal(6,2) DEFAULT NULL,
    `height` decimal(6,2) DEFAULT NULL,
    `z_index` smallint DEFAULT NULL,
    `label` smallint DEFAULT NULL
);
CREATE TABLE `swi_icons` (
    `icon_id` integer PRIMARY KEY AUTO_INCREMENT,
    `icon` blob,
    `icon_name` varchar(255),
    `mime_type` varchar(64),
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `created_by_user_id` integer
);
CREATE TABLE `swi_document_page_map` (
    `document_id` integer,
    `page_id` integer PRIMARY KEY AUTO_INCREMENT,
    `page_header` varchar(255),
    `page_num` integer,
    `marker_tips` tinyint(1) NOT NULL DEFAULT 0,
    `marker_quality` tinyint(1) NOT NULL DEFAULT 0,
    `marker_safety` tinyint(1) NOT NULL DEFAULT 0,
    `marker_visual` tinyint(1) NOT NULL DEFAULT 0
);
ALTER TABLE `swi_document`
ADD CONSTRAINT `swi_job_area_swi_document` FOREIGN KEY (`job_area_id`) REFERENCES `swi_job_area` (`job_area_id`);
ALTER TABLE `swi_document`
ADD CONSTRAINT `swi_document_status_swi_document` FOREIGN KEY (`document_status_id`) REFERENCES `swi_document_status` (`document_status_id`);
ALTER TABLE `swi_document`
ADD CONSTRAINT `users_swi_document` FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users` (`user_id`);
ALTER TABLE `swi_document`
ADD CONSTRAINT `locations_swi_document` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`);
ALTER TABLE `swi_document`
ADD CONSTRAINT `departments_swi_document` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`);
ALTER TABLE `swi_document_page_map`
ADD CONSTRAINT `swi_document_swi_document_page_map` FOREIGN KEY (`document_id`) REFERENCES `swi_document` (`document_id`);
ALTER TABLE `swi_text_blocks`
ADD CONSTRAINT `swi_document_page_map_swi_text_blocks` FOREIGN KEY (`page_id`) REFERENCES `swi_document_page_map` (`page_id`);
ALTER TABLE `swi_images`
ADD CONSTRAINT `swi_document_page_map_swi_images` FOREIGN KEY (`page_id`) REFERENCES `swi_document_page_map` (`page_id`);
ALTER TABLE `swi_text_block_icons`
ADD CONSTRAINT `swi_text_blocks_swi_text_block_icons` FOREIGN KEY (`text_block_id`) REFERENCES `swi_text_blocks` (`text_block_id`);
ALTER TABLE `swi_text_block_icons`
ADD CONSTRAINT `swi_icons_swi_text_block_icons` FOREIGN KEY (`icon_id`) REFERENCES `swi_icons` (`icon_id`);