-- Oasis Assistant DB Schema (v0.1.0)
-- This database stores assistant-specific data only.
-- CORE system (oasis_core) remains unchanged.

CREATE DATABASE IF NOT EXISTS `oasis_assistant` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `oasis_assistant`;

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` varchar(50) NOT NULL,
  `applied_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_prefs` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `lang_mode` enum('emirati','msa') NOT NULL DEFAULT 'msa',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `conversations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `meta` json DEFAULT NULL,
  `started_at` datetime NOT NULL,
  `ended_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` int(10) UNSIGNED NOT NULL,
  `sender` enum('user','assistant','system') NOT NULL,
  `message_type` enum('text','choice','card','event') NOT NULL DEFAULT 'text',
  `content` longtext NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `fk_msg_conv` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `flows` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(80) NOT NULL,
  `name` varchar(200) NOT NULL,
  `flow_json` longtext NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- ============================
-- Reports Module (v0.2.0)
-- ============================

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`),
  CONSTRAINT `fk_action_report` FOREIGN KEY (`report_id`) REFERENCES `reports` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `fk_att_report` FOREIGN KEY (`report_id`) REFERENCES `reports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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


-- Upgrade Oasis Assistant DB to v0.2.6 (Report Wizard Q/A + Builder)
-- This upgrade touches ONLY oasis_assistant database.


CREATE TABLE IF NOT EXISTS `report_sections` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(60) NOT NULL,
  `title_ar` varchar(200) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `is_active` (`is_active`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `report_questions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `section_id` int(10) UNSIGNED NOT NULL,
  `question_ar` longtext NOT NULL,
  `answer_type` varchar(30) NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `allow_na` tinyint(1) NOT NULL DEFAULT 1,
  `help_text_ar` longtext DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `section_id` (`section_id`),
  KEY `sort_order` (`sort_order`),
  CONSTRAINT `fk_rq_section` FOREIGN KEY (`section_id`) REFERENCES `report_sections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `report_question_options` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `question_id` int(10) UNSIGNED NOT NULL,
  `label_ar` varchar(255) NOT NULL,
  `value` varchar(120) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `question_id` (`question_id`),
  KEY `sort_order` (`sort_order`),
  CONSTRAINT `fk_rqo_question` FOREIGN KEY (`question_id`) REFERENCES `report_questions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed sections (editable in admin)
INSERT IGNORE INTO report_sections(code,title_ar,is_active,sort_order,created_at,updated_at) VALUES
('cleanliness','النظافة العامة',1,10,NOW(),NOW()),
('waste','إزالة المخلفات',1,20,NOW(),NOW()),
('maintenance','أعمال الصيانة',1,30,NOW(),NOW()),
('wells_electric','الآبار والكهرباء',1,40,NOW(),NOW()),
('irrigation','الري وشبكة المياه',1,50,NOW(),NOW()),
('pests','الآفات والمكافحة',1,60,NOW(),NOW()),
('safety','السلامة العامة',1,70,NOW(),NOW()),
('resources','العمالة والمعدات',1,80,NOW(),NOW()),
('other','ملاحظات عامة',1,90,NOW(),NOW());

-- Seed questions (derived from القسم / نماذج التقارير اليومية)
-- NOTE: IDs may differ per DB, so we insert by section code lookups.

-- Helpers: insert question if not exists
-- Cleanliness
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'تقييم النظافة العامة للموقع','single',1,1,'اختر: جيدة/متوسطة/سيئة',10,NOW(),NOW()
FROM report_sections s WHERE s.code='cleanliness'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='تقييم النظافة العامة للموقع');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل توجد مخلفات ظاهرة؟','single',1,1,'اختر مستوى المخلفات',20,NOW(),NOW()
FROM report_sections s WHERE s.code='cleanliness'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل توجد مخلفات ظاهرة؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'ملاحظات النظافة','text_long',0,1,NULL,90,NOW(),NOW()
FROM report_sections s WHERE s.code='cleanliness'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='ملاحظات النظافة');

-- Waste
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد المزارع/المواقع التي تمت إزالة مخلفاتها اليوم','number',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='waste'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد المزارع/المواقع التي تمت إزالة مخلفاتها اليوم');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'كمية المخلفات (تقديري)','single',0,1,'اختر: قليلة/متوسطة/كبيرة',20,NOW(),NOW()
FROM report_sections s WHERE s.code='waste'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='كمية المخلفات (تقديري)');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل تم نقل المخلفات إلى موقع مخصص؟','yesno',0,1,NULL,30,NOW(),NOW()
FROM report_sections s WHERE s.code='waste'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل تم نقل المخلفات إلى موقع مخصص؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد الأشجار/الأغصان الساقطة التي تم قصّها','number',0,1,NULL,40,NOW(),NOW()
FROM report_sections s WHERE s.code='waste'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد الأشجار/الأغصان الساقطة التي تم قصّها');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'ملاحظات إزالة المخلفات','text_long',0,1,NULL,90,NOW(),NOW()
FROM report_sections s WHERE s.code='waste'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='ملاحظات إزالة المخلفات');

-- Maintenance
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'أعمال الصيانة المنجزة اليوم (مختصر)','text_long',1,1,'اذكر أهم الأعمال المنجزة',10,NOW(),NOW()
FROM report_sections s WHERE s.code='maintenance'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='أعمال الصيانة المنجزة اليوم (مختصر)');

-- Wells/Electric
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل تم المرور اليومي على الآبار؟','yesno',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='wells_electric'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل تم المرور اليومي على الآبار؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد أعمال صيانة خطوط المياه','number',0,1,NULL,20,NOW(),NOW()
FROM report_sections s WHERE s.code='wells_electric'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد أعمال صيانة خطوط المياه');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد أعمال صيانة التوصيلات الكهربائية','number',0,1,NULL,30,NOW(),NOW()
FROM report_sections s WHERE s.code='wells_electric'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد أعمال صيانة التوصيلات الكهربائية');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل تم استخراج/إنزال مضخة غاطسة؟','single',0,1,'اختر: لا/استخراج/إنزال',40,NOW(),NOW()
FROM report_sections s WHERE s.code='wells_electric'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل تم استخراج/إنزال مضخة غاطسة؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'تفاصيل إضافية للآبار/الكهرباء','text_long',0,1,NULL,90,NOW(),NOW()
FROM report_sections s WHERE s.code='wells_electric'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='تفاصيل إضافية للآبار/الكهرباء');

-- Irrigation
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'حالة شبكة الري','single',0,1,'اختر: جيدة/تحتاج صيانة/متوقفة',10,NOW(),NOW()
FROM report_sections s WHERE s.code='irrigation'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='حالة شبكة الري');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل توجد تسريبات مياه؟','yesno',0,1,NULL,20,NOW(),NOW()
FROM report_sections s WHERE s.code='irrigation'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل توجد تسريبات مياه؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'ملاحظات الري وشبكة المياه','text_long',0,1,NULL,90,NOW(),NOW()
FROM report_sections s WHERE s.code='irrigation'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='ملاحظات الري وشبكة المياه');

-- Pests
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل توجد آفات ملحوظة؟','yesno',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='pests'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل توجد آفات ملحوظة؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'نوع الآفة (إن وجدت)','multi',0,1,'اختر واحداً أو أكثر',20,NOW(),NOW()
FROM report_sections s WHERE s.code='pests'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='نوع الآفة (إن وجدت)');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل تم إجراء مكافحة/رش اليوم؟','yesno',0,1,NULL,30,NOW(),NOW()
FROM report_sections s WHERE s.code='pests'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل تم إجراء مكافحة/رش اليوم؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'ملاحظات الآفات والمكافحة','text_long',0,1,NULL,90,NOW(),NOW()
FROM report_sections s WHERE s.code='pests'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='ملاحظات الآفات والمكافحة');

-- Safety
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'هل توجد مخاطر سلامة في الموقع؟','yesno',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='safety'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='هل توجد مخاطر سلامة في الموقع؟');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'الإجراءات المتخذة (إن وجدت)','text_long',0,1,NULL,20,NOW(),NOW()
FROM report_sections s WHERE s.code='safety'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='الإجراءات المتخذة (إن وجدت)');

-- Resources (manpower/equipment)
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد العمال','number',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='resources'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد العمال');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'عدد السائقين','number',0,1,NULL,20,NOW(),NOW()
FROM report_sections s WHERE s.code='resources'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='عدد السائقين');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'المركبات/المعدات المستخدمة','text_long',0,1,'مثال: سيارة 2 طن، 3 طن، منشار...',30,NOW(),NOW()
FROM report_sections s WHERE s.code='resources'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='المركبات/المعدات المستخدمة');

-- Other (general notes)
INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'ملاحظات عامة','text_long',0,1,NULL,10,NOW(),NOW()
FROM report_sections s WHERE s.code='other'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='ملاحظات عامة');

INSERT INTO report_questions(section_id,question_ar,answer_type,is_required,allow_na,help_text_ar,sort_order,created_at,updated_at)
SELECT s.id,'التوصيات','text_long',0,1,NULL,20,NOW(),NOW()
FROM report_sections s WHERE s.code='other'
AND NOT EXISTS (SELECT 1 FROM report_questions q WHERE q.section_id=s.id AND q.question_ar='التوصيات');

-- Seed options for some questions
-- (1) cleanliness: تقييم النظافة العامة للموقع
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'جيدة','good',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='تقييم النظافة العامة للموقع'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='good');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'متوسطة','mid',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='تقييم النظافة العامة للموقع'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='mid');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'سيئة','bad',30 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='تقييم النظافة العامة للموقع'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='bad');

-- cleanliness: مخلفات ظاهرة
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'لا توجد','none',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='هل توجد مخلفات ظاهرة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='none');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'قليلة','low',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='هل توجد مخلفات ظاهرة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='low');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'متوسطة','mid',30 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='هل توجد مخلفات ظاهرة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='mid');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'كثيرة','high',40 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='cleanliness' AND q.question_ar='هل توجد مخلفات ظاهرة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='high');

-- waste: كمية المخلفات
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'قليلة','low',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='waste' AND q.question_ar='كمية المخلفات (تقديري)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='low');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'متوسطة','mid',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='waste' AND q.question_ar='كمية المخلفات (تقديري)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='mid');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'كبيرة','high',30 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='waste' AND q.question_ar='كمية المخلفات (تقديري)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='high');

-- wells: استخراج/إنزال مضخة
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'لا','no',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='wells_electric' AND q.question_ar='هل تم استخراج/إنزال مضخة غاطسة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='no');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'استخراج','pull',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='wells_electric' AND q.question_ar='هل تم استخراج/إنزال مضخة غاطسة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='pull');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'إنزال','drop',30 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='wells_electric' AND q.question_ar='هل تم استخراج/إنزال مضخة غاطسة؟'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='drop');

-- irrigation: حالة شبكة الري
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'جيدة','good',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='irrigation' AND q.question_ar='حالة شبكة الري'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='good');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'تحتاج صيانة','need_fix',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='irrigation' AND q.question_ar='حالة شبكة الري'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='need_fix');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'متوقفة','stopped',30 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='irrigation' AND q.question_ar='حالة شبكة الري'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='stopped');

-- pests: نوع الآفة
INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'سوسة النخيل','rpw',10 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='pests' AND q.question_ar='نوع الآفة (إن وجدت)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='rpw');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'دوباس النخيل','dubas',20 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='pests' AND q.question_ar='نوع الآفة (إن وجدت)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='dubas');

INSERT INTO report_question_options(question_id,label_ar,value,sort_order)
SELECT q.id,'حشرات/آفات أخرى','other',90 FROM report_questions q
JOIN report_sections s ON s.id=q.section_id
WHERE s.code='pests' AND q.question_ar='نوع الآفة (إن وجدت)'
AND NOT EXISTS (SELECT 1 FROM report_question_options o WHERE o.question_id=q.id AND o.value='other');

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