-- Oasis & Aflaj Management - schema.sql
-- Charset: utf8mb4

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','supervisor','data_entry','viewer') NOT NULL DEFAULT 'viewer',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS oases (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  area VARCHAR(200) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  status ENUM('active','inactive','under_maintenance') NOT NULL DEFAULT 'active',
  notes TEXT NULL,
    palm_trees_count INT NOT NULL DEFAULT 0,
  falaj_outlets_count INT NOT NULL DEFAULT 0,
created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_oases_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS aflaj (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  oasis_id INT UNSIGNED NOT NULL,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  type ENUM('surface','underground','mixed') NOT NULL DEFAULT 'surface',
  length_m DECIMAL(10,2) NULL,
  status ENUM('operational','partial','stopped','under_maintenance') NOT NULL DEFAULT 'operational',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_aflaj_oasis_status (oasis_id, status),
  CONSTRAINT fk_aflaj_oasis FOREIGN KEY (oasis_id) REFERENCES oases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS farms (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  oasis_id INT UNSIGNED NOT NULL,
  code VARCHAR(50) NOT NULL UNIQUE,
  owner_name VARCHAR(200) NULL,
    owner_phone VARCHAR(30) NULL,
  owner_emirates_id VARCHAR(15) NULL,
area_m2 DECIMAL(12,2) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_farms_oasis_status (oasis_id, status),
  CONSTRAINT fk_farms_oasis FOREIGN KEY (oasis_id) REFERENCES oases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_type ENUM('oasis','falaj','farm') NOT NULL,
  owner_id INT UNSIGNED NOT NULL,
  category VARCHAR(80) NOT NULL,
  tag VARCHAR(80) NOT NULL UNIQUE,
  serial_number VARCHAR(120) NULL,
  installed_at DATE NULL,
  status ENUM('ok','needs_service','out_of_order') NOT NULL DEFAULT 'ok',
  location_text VARCHAR(255) NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_assets_owner (owner_type, owner_id),
  INDEX idx_assets_cat_status (category, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  asset_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  priority ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  status ENUM('open','assigned','in_progress','done','cancelled') NOT NULL DEFAULT 'open',
  reported_at DATETIME NOT NULL,
  due_date DATE NULL,
  closed_at DATETIME NULL,
  reported_by INT UNSIGNED NULL,
  assigned_to INT UNSIGNED NULL,
  contractor_id INT UNSIGNED NULL,
  contract_id INT UNSIGNED NULL,
  contractor_assigned_at DATETIME NULL,
  contractor_assigned_by INT UNSIGNED NULL,
  estimated_cost DECIMAL(12,2) NULL,
  actual_cost DECIMAL(12,2) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_mr_status_priority (status, priority),
  CONSTRAINT fk_mr_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
  CONSTRAINT fk_mr_reported_by FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_mr_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_mr_contractor FOREIGN KEY (contractor_id) REFERENCES contractors(id) ON DELETE SET NULL,
  CONSTRAINT fk_mr_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE SET NULL,
  CONSTRAINT fk_mr_contractor_assigned_by FOREIGN KEY (contractor_assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  maintenance_request_id INT UNSIGNED NOT NULL,
  action VARCHAR(200) NOT NULL,
  notes TEXT NULL,
  cost DECIMAL(12,2) NULL,
  performed_at DATETIME NOT NULL,
  performed_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_ml_request FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_ml_performed_by FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- RBAC: Permissions & Roles
CREATE TABLE IF NOT EXISTS permissions (
  perm_key VARCHAR(100) PRIMARY KEY,
  perm_group VARCHAR(100) NOT NULL,
  label VARCHAR(200) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role VARCHAR(50) NOT NULL,
  perm_key VARCHAR(100) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY(role, perm_key),
  CONSTRAINT fk_role_permissions_perm FOREIGN KEY (perm_key) REFERENCES permissions(perm_key) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id INT UNSIGNED NULL,
  old_data MEDIUMTEXT NULL,
  new_data MEDIUMTEXT NULL,
  ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed permissions (idempotent: uses INSERT IGNORE)
INSERT IGNORE INTO permissions (perm_key, perm_group, label, description, created_at) VALUES
('view_dashboard','Dashboard','عرض لوحة التحكم','', NOW()),
('view_oases','Oases','عرض الواحات','', NOW()),
('manage_oases','Oases','إدارة الواحات','', NOW()),
('view_aflaj','Aflaj','عرض الأفلاج','', NOW()),
('manage_aflaj','Aflaj','إدارة الأفلاج','', NOW()),
('view_farms','Farms','عرض المزارع','', NOW()),
('manage_farms','Farms','إدارة المزارع','', NOW()),
('view_assets','Assets','عرض الأصول','', NOW()),
('manage_assets','Assets','إدارة الأصول','', NOW()),
('view_maintenance','Maintenance','عرض الصيانة','', NOW()),
('manage_maintenance','Maintenance','إدارة الصيانة','', NOW()),
('export_maintenance_pdf','Maintenance','تصدير تقرير الصيانة PDF','', NOW()),
('view_reports','Reports','عرض التقارير','', NOW()),
('export_reports_pdf','Reports','تصدير تقارير PDF','', NOW()),
('manage_users','Security','إدارة المستخدمين','', NOW()),
('manage_permissions','Security','إدارة الصلاحيات','', NOW()),
('view_audit','Security','عرض سجل التدقيق','', NOW())
('view_contractors','Contractors','عرض المقاولين','', NOW()),

('manage_contractors','Contractors','إدارة المقاولين','', NOW()),

('view_contracts','Contracts','عرض العقود','', NOW()),

('manage_contracts','Contracts','إدارة العقود','', NOW()),
;

-- Seed role-permissions matrix (idempotent)
-- admin: all allowed (we still store rows for completeness)
INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at)
SELECT 'admin', perm_key, 1, NOW(), NULL FROM permissions;

-- supervisor
INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at) VALUES
('supervisor','view_dashboard',1,NOW(),NULL),
('supervisor','view_oases',1,NOW(),NULL),
('supervisor','manage_oases',1,NOW(),NULL),
('supervisor','view_aflaj',1,NOW(),NULL),
('supervisor','manage_aflaj',1,NOW(),NULL),
('supervisor','view_farms',1,NOW(),NULL),
('supervisor','manage_farms',1,NOW(),NULL),
('supervisor','view_assets',1,NOW(),NULL),
('supervisor','manage_assets',1,NOW(),NULL),
('supervisor','view_maintenance',1,NOW(),NULL),
('supervisor','manage_maintenance',1,NOW(),NULL),
('supervisor','export_maintenance_pdf',1,NOW(),NULL),
('supervisor','view_reports',1,NOW(),NULL),
('supervisor','export_reports_pdf',1,NOW(),NULL),
('supervisor','manage_users',1,NOW(),NULL),
('supervisor','view_audit',1,NOW(),NULL),
('supervisor','view_contractors',1,NOW(),NULL),
('supervisor','manage_contractors',1,NOW(),NULL),
('supervisor','view_contracts',1,NOW(),NULL),
('supervisor','manage_contracts',1,NOW(),NULL),
('supervisor','manage_permissions',0,NOW(),NULL);

-- data_entry
INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at) VALUES
('data_entry','view_dashboard',1,NOW(),NULL),
('data_entry','view_oases',1,NOW(),NULL),
('data_entry','manage_oases',1,NOW(),NULL),
('data_entry','view_aflaj',1,NOW(),NULL),
('data_entry','manage_aflaj',1,NOW(),NULL),
('data_entry','view_farms',1,NOW(),NULL),
('data_entry','manage_farms',1,NOW(),NULL),
('data_entry','view_assets',1,NOW(),NULL),
('data_entry','manage_assets',1,NOW(),NULL),
('data_entry','view_maintenance',1,NOW(),NULL),
('data_entry','manage_maintenance',1,NOW(),NULL),
('data_entry','export_maintenance_pdf',0,NOW(),NULL),
('data_entry','view_reports',1,NOW(),NULL),
('data_entry','export_reports_pdf',0,NOW(),NULL),
('data_entry','manage_users',0,NOW(),NULL),
('data_entry','view_audit',0,NOW(),NULL),
('data_entry','view_contractors',1,NOW(),NULL),
('data_entry','manage_contractors',0,NOW(),NULL),
('data_entry','view_contracts',1,NOW(),NULL),
('data_entry','manage_contracts',0,NOW(),NULL),
('data_entry','manage_permissions',0,NOW(),NULL);

-- viewer
INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at) VALUES
('viewer','view_dashboard',1,NOW(),NULL),
('viewer','view_oases',1,NOW(),NULL),
('viewer','manage_oases',0,NOW(),NULL),
('viewer','view_aflaj',1,NOW(),NULL),
('viewer','manage_aflaj',0,NOW(),NULL),
('viewer','view_farms',1,NOW(),NULL),
('viewer','manage_farms',0,NOW(),NULL),
('viewer','view_assets',1,NOW(),NULL),
('viewer','manage_assets',0,NOW(),NULL),
('viewer','view_maintenance',1,NOW(),NULL),
('viewer','manage_maintenance',0,NOW(),NULL),
('viewer','export_maintenance_pdf',0,NOW(),NULL),
('viewer','view_reports',1,NOW(),NULL),
('viewer','export_reports_pdf',0,NOW(),NULL),
('viewer','manage_users',0,NOW(),NULL),
('viewer','view_audit',0,NOW(),NULL),
('viewer','view_contractors',1,NOW(),NULL),
('viewer','manage_contractors',0,NOW(),NULL),
('viewer','view_contracts',1,NOW(),NULL),
('viewer','manage_contracts',0,NOW(),NULL),
('viewer','manage_permissions',0,NOW(),NULL);

-- Ensure any missing matrix cells are created with 0 for non-admin roles
INSERT IGNORE INTO role_permissions (role, perm_key, allowed, created_at, updated_at)
SELECT r.role, p.perm_key, 0, NOW(), NULL
FROM (SELECT 'supervisor' role UNION ALL SELECT 'data_entry' UNION ALL SELECT 'viewer') r
CROSS JOIN permissions p;



-- Attachments (v0.3.1)
CREATE TABLE IF NOT EXISTS attachments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_type VARCHAR(30) NOT NULL,         -- e.g. 'asset','farm'
  owner_id INT UNSIGNED NOT NULL,
  stored_name VARCHAR(255) NOT NULL,       -- relative path under storage/uploads
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(120) NOT NULL,
  size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
  uploaded_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_attach_owner (owner_type, owner_id),
  CONSTRAINT fk_attach_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Contractors & Contracts (v0.3.2)
CREATE TABLE IF NOT EXISTS contractors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(200) NOT NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(120) NULL,
  address VARCHAR(255) NULL,
  license_no VARCHAR(120) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_contractors_code (code),
  INDEX idx_contractors_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contractor_id INT UNSIGNED NOT NULL,
  contract_no VARCHAR(80) NOT NULL,
  title VARCHAR(200) NOT NULL,
  start_date DATE NULL,
  end_date DATE NULL,
  status ENUM('active','expired','suspended','draft') NOT NULL DEFAULT 'active',
  terms TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_contracts_no (contract_no),
  INDEX idx_contracts_contractor (contractor_id),
  CONSTRAINT fk_contracts_contractor FOREIGN KEY (contractor_id) REFERENCES contractors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contract_services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_id INT UNSIGNED NOT NULL,
  service_name VARCHAR(200) NOT NULL,
  unit VARCHAR(50) NULL,
  unit_price DECIMAL(12,2) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_services_contract (contract_id),
  CONSTRAINT fk_services_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;