-- Upgrade script for oasis_assistant DB to v0.2.0 (Reports)
-- Safe to run multiple times.

USE `oasis_assistant`;

CREATE TABLE IF NOT EXISTS `reports` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_no` int(10) UNSIGNED NOT NULL,
  `template_code` varchar(80) NOT NULL DEFAULT 'visit_report',
  `status` enum('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
  `ref_type` enum('farm','falaj','oasis') NOT NULL,
  `ref_id` int(10) UNSIGNED NOT NULL,
  `visit_date` date NOT NULL,
  `title` varchar(200) NOT NULL,
  `content_json` longtext NOT NULL,
  `generated_text` longtext NOT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_no` (`report_no`),
  KEY `status` (`status`),
  KEY `created_by` (`created_by`),
  KEY `ref` (`ref_type`,`ref_id`),
  KEY `visit_date` (`visit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `report_actions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id` int(10) UNSIGNED NOT NULL,
  `action_type` varchar(40) NOT NULL,
  `note` longtext NOT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `report_attachments` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id` int(10) UNSIGNED NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `mime` varchar(120) NOT NULL,
  `size` int(10) UNSIGNED NOT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO migrations(version, applied_at) VALUES ('v0.2.0', NOW());

-- Add foreign keys (optional). Run only if you want strict referential integrity.
-- ALTER TABLE report_actions ADD CONSTRAINT fk_action_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE;
-- ALTER TABLE report_attachments ADD CONSTRAINT fk_att_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE;