-- =====================================================================
--  aqariverify — منصة التحقق العقاري الوطنية | STORISMO
--  Database schema (MySQL 8 / MariaDB 10.4+) — utf8mb4
--  كلمة مرور جميع الحسابات التجريبية: Passw0rd!
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- المستخدمون ----------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60)  NOT NULL UNIQUE,
  email         VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name     VARCHAR(150) NOT NULL,
  role               VARCHAR(40)  NOT NULL,
  entity_scope       VARCHAR(120) DEFAULT NULL,
  custom_permissions TEXT         DEFAULT NULL,
  lang          VARCHAR(5)   NOT NULL DEFAULT 'ar',
  theme         VARCHAR(10)  NOT NULL DEFAULT 'light',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  last_login_at DATETIME     DEFAULT NULL,
  created_at    DATETIME     NOT NULL,
  INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- محاولات الدخول ----------
DROP TABLE IF EXISTS login_attempts;
CREATE TABLE login_attempts (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(100) NOT NULL,
  ip         VARCHAR(45)  NOT NULL,
  success    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL,
  INDEX idx_la_user (username, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- استعادة كلمة المرور ----------
DROP TABLE IF EXISTS password_resets;
CREATE TABLE password_resets (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  token      VARCHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used       TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX idx_pr_token (token),
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- الولايات ----------
DROP TABLE IF EXISTS wilayas;
CREATE TABLE wilayas (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  code    VARCHAR(4) NOT NULL UNIQUE,
  name_ar VARCHAR(80) NOT NULL,
  name_fr VARCHAR(80) NOT NULL,
  name_en VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- البلديات ----------
DROP TABLE IF EXISTS communes;
CREATE TABLE communes (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  wilaya_id INT NOT NULL,
  name_ar   VARCHAR(80) NOT NULL,
  name_fr   VARCHAR(80) NOT NULL,
  name_en   VARCHAR(80) NOT NULL,
  CONSTRAINT fk_com_wilaya FOREIGN KEY (wilaya_id) REFERENCES wilayas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- أنواع العقارات ----------
DROP TABLE IF EXISTS property_types;
CREATE TABLE property_types (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  code                VARCHAR(30) NOT NULL UNIQUE,
  name_ar             VARCHAR(80) NOT NULL,
  name_fr             VARCHAR(80) NOT NULL,
  name_en             VARCHAR(80) NOT NULL,
  requires_named_list TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- الأشخاص (السجل المدني) ----------
DROP TABLE IF EXISTS persons;
CREATE TABLE persons (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  nin          VARCHAR(18) NOT NULL UNIQUE,
  first_name   VARCHAR(80) NOT NULL,
  last_name    VARCHAR(80) NOT NULL,
  birth_date   DATE DEFAULT NULL,
  gender       ENUM('m','f') DEFAULT 'm',
  civil_status ENUM('single','married','divorced','widowed') DEFAULT 'single',
  created_at   DATETIME NOT NULL,
  INDEX idx_persons_name (last_name, first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- العقارات (السجل العقاري) ----------
DROP TABLE IF EXISTS properties;
CREATE TABLE properties (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  ref        VARCHAR(30) NOT NULL UNIQUE,
  type_id    INT NOT NULL,
  address    VARCHAR(200) NOT NULL,
  wilaya_id  INT DEFAULT NULL,
  commune_id INT DEFAULT NULL,
  owner_nin  VARCHAR(18) DEFAULT NULL,
  area_m2         DECIMAL(10,2) DEFAULT NULL,   -- المساحة بالمتر المربع
  latitude        DECIMAL(10,7) DEFAULT NULL,   -- خط العرض
  longitude       DECIMAL(10,7) DEFAULT NULL,   -- خط الطول
  housing_program ENUM('none','aadl','opgi') NOT NULL DEFAULT 'none', -- صيغة السكن
  location_note   VARCHAR(255) DEFAULT NULL,     -- وصف الموقع
  created_at DATETIME NOT NULL,
  INDEX idx_prop_type (type_id),
  INDEX idx_prop_wilaya (wilaya_id),
  INDEX idx_prop_owner (owner_nin),
  CONSTRAINT fk_prop_type   FOREIGN KEY (type_id)    REFERENCES property_types(id),
  CONSTRAINT fk_prop_wilaya FOREIGN KEY (wilaya_id)  REFERENCES wilayas(id)  ON DELETE SET NULL,
  CONSTRAINT fk_prop_commune FOREIGN KEY (commune_id) REFERENCES communes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- العلاقات القانونية ----------
DROP TABLE IF EXISTS legal_relations;
CREATE TABLE legal_relations (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  person_id     INT NOT NULL,
  property_id   INT NOT NULL,
  relation_type ENUM('owner','beneficiary','partner','spouse','child','agent','guardian','usufructuary','legal_rep') NOT NULL,
  source        VARCHAR(40) DEFAULT 'real_estate_registry',
  status        ENUM('active','ended') NOT NULL DEFAULT 'active',
  start_date    DATE DEFAULT NULL,
  end_date      DATE DEFAULT NULL,
  created_by    INT DEFAULT NULL,
  created_at    DATETIME NOT NULL,
  INDEX idx_lr_person (person_id),
  INDEX idx_lr_property (property_id),
  CONSTRAINT fk_lr_person   FOREIGN KEY (person_id)   REFERENCES persons(id)    ON DELETE CASCADE,
  CONSTRAINT fk_lr_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- القائمة الاسمية للمستفيدين ----------
DROP TABLE IF EXISTS named_lists;
CREATE TABLE named_lists (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  nin         VARCHAR(18) NOT NULL,
  full_name   VARCHAR(150) NOT NULL,
  program     VARCHAR(80) DEFAULT NULL,
  added_at    DATETIME NOT NULL,
  INDEX idx_nl_property (property_id),
  INDEX idx_nl_nin (nin),
  CONSTRAINT fk_nl_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- القيود القانونية ----------
DROP TABLE IF EXISTS legal_constraints;
CREATE TABLE legal_constraints (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT NOT NULL,
  type        VARCHAR(40) NOT NULL,       -- mortgage / seizure / litigation / opposition
  type_ar     VARCHAR(80) DEFAULT NULL,
  type_fr     VARCHAR(80) DEFAULT NULL,
  type_en     VARCHAR(80) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  is_blocking TINYINT(1) NOT NULL DEFAULT 0,
  status      ENUM('active','lifted') NOT NULL DEFAULT 'active',
  created_at  DATETIME NOT NULL,
  INDEX idx_lc_property (property_id),
  CONSTRAINT fk_lc_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- طلبات التحقق ----------
DROP TABLE IF EXISTS verification_requests;
CREATE TABLE verification_requests (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  ref               VARCHAR(30) NOT NULL UNIQUE,
  requester_user_id INT DEFAULT NULL,
  government_entity VARCHAR(120) DEFAULT NULL,
  service_type      VARCHAR(60)  DEFAULT NULL,
  property_id       INT DEFAULT NULL,
  nin               VARCHAR(18)  NOT NULL,
  claimed_relation  VARCHAR(30)  DEFAULT NULL,
  result            ENUM('match','review','mismatch') NOT NULL,
  score             INT NOT NULL DEFAULT 0,
  details_json      TEXT DEFAULT NULL,
  created_at        DATETIME NOT NULL,
  INDEX idx_vr_result (result),
  INDEX idx_vr_property (property_id),
  INDEX idx_vr_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- حالات المراجعة ----------
DROP TABLE IF EXISTS review_cases;
CREATE TABLE review_cases (
  id                       INT AUTO_INCREMENT PRIMARY KEY,
  ref                      VARCHAR(30) NOT NULL UNIQUE,
  verification_request_id  INT DEFAULT NULL,
  property_id              INT DEFAULT NULL,
  nin                      VARCHAR(18) NOT NULL,
  reason_codes             TEXT DEFAULT NULL,
  status                   ENUM('open','in_review','resolved','closed') NOT NULL DEFAULT 'open',
  assigned_to              INT DEFAULT NULL,
  resolution_note          TEXT DEFAULT NULL,
  created_at               DATETIME NOT NULL,
  resolved_at              DATETIME DEFAULT NULL,
  INDEX idx_rc_status (status),
  CONSTRAINT fk_rc_vr FOREIGN KEY (verification_request_id) REFERENCES verification_requests(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- سجل الأحداث (business) ----------
DROP TABLE IF EXISTS events;
CREATE TABLE events (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  property_id INT DEFAULT NULL,
  person_nin  VARCHAR(18) DEFAULT NULL,
  event_type  VARCHAR(40) NOT NULL,
  description VARCHAR(255) NOT NULL,
  user_id     INT DEFAULT NULL,
  created_at  DATETIME NOT NULL,
  INDEX idx_ev_property (property_id),
  INDEX idx_ev_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- سجل التدقيق الأمني ----------
DROP TABLE IF EXISTS audit_log;
CREATE TABLE audit_log (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT DEFAULT NULL,
  action     VARCHAR(60) NOT NULL,
  entity     VARCHAR(60) DEFAULT NULL,
  entity_id  INT DEFAULT NULL,
  ip         VARCHAR(45) DEFAULT NULL,
  meta_json  TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_al_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- الإشعارات ----------
DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT DEFAULT NULL,
  target_role VARCHAR(40) DEFAULT NULL,
  title       VARCHAR(150) NOT NULL,
  body        VARCHAR(255) DEFAULT NULL,
  type        VARCHAR(20) DEFAULT 'info',
  link        VARCHAR(200) DEFAULT NULL,
  is_read     TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL,
  INDEX idx_nt_user (user_id),
  INDEX idx_nt_role (target_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- مؤشر الثقة العقارية ----------
DROP TABLE IF EXISTS trust_index;
CREATE TABLE trust_index (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  property_id  INT NOT NULL UNIQUE,
  score        INT NOT NULL DEFAULT 100,
  factors_json TEXT DEFAULT NULL,
  computed_at  DATETIME NOT NULL,
  CONSTRAINT fk_ti_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- الدفتر العائلي الرقمي ----------
DROP TABLE IF EXISTS family_books;
CREATE TABLE family_books (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  book_no       VARCHAR(30) NOT NULL UNIQUE,
  head_nin      VARCHAR(18) NOT NULL,                 -- رب الأسرة
  wilaya_id     INT DEFAULT NULL,
  commune_id    INT DEFAULT NULL,
  created_by    INT DEFAULT NULL,
  created_at    DATETIME NOT NULL,
  INDEX idx_fb_head (head_nin),
  CONSTRAINT fk_fb_wilaya  FOREIGN KEY (wilaya_id)  REFERENCES wilayas(id)  ON DELETE SET NULL,
  CONSTRAINT fk_fb_commune FOREIGN KEY (commune_id) REFERENCES communes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS family_members;
CREATE TABLE family_members (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  family_id     INT NOT NULL,
  nin           VARCHAR(18) NOT NULL,
  relation      ENUM('head','spouse','son','daughter','father','mother','other') NOT NULL DEFAULT 'other',
  added_at      DATETIME NOT NULL,
  INDEX idx_fm_family (family_id),
  INDEX idx_fm_nin (nin),
  CONSTRAINT fk_fm_family FOREIGN KEY (family_id) REFERENCES family_books(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- سجلّ عقود العقار (بيع/تأجير) ----------
DROP TABLE IF EXISTS property_contracts;
CREATE TABLE property_contracts (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  property_id   INT NOT NULL,
  contract_type ENUM('sale','rental') NOT NULL,
  party_nin     VARCHAR(18) NOT NULL,              -- المشتري أو المستأجر
  owner_nin     VARCHAR(18) DEFAULT NULL,          -- البائع/المؤجّر وقت العقد
  start_date    DATE DEFAULT NULL,
  end_date      DATE DEFAULT NULL,                 -- للإيجار: تاريخ الانتهاء
  status        ENUM('active','ended','cancelled') NOT NULL DEFAULT 'active',
  notary_user_id INT DEFAULT NULL,                 -- الموثّق الذي أبرم العقد
  note          VARCHAR(255) DEFAULT NULL,
  created_at    DATETIME NOT NULL,
  INDEX idx_pc_property (property_id),
  INDEX idx_pc_party (party_nin),
  INDEX idx_pc_type (contract_type),
  CONSTRAINT fk_pc_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- تدقيق محاولات شهادة الإقامة ----------
DROP TABLE IF EXISTS residence_audit;
CREATE TABLE residence_audit (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  property_id  INT DEFAULT NULL,
  property_ref VARCHAR(30) DEFAULT NULL,
  applicant_nin VARCHAR(18) DEFAULT NULL,
  applicant_name VARCHAR(120) DEFAULT NULL,   -- الاسم المخزَّن للتدقيق عند الرفض
  result       ENUM('match','review','mismatch') NOT NULL,
  relation     VARCHAR(40) DEFAULT NULL,
  commune_user_id INT DEFAULT NULL,
  created_at   DATETIME NOT NULL,
  INDEX idx_ra_property (property_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  البيانات الأولية (Seed)
-- =====================================================================

INSERT INTO wilayas (code, name_ar, name_fr, name_en) VALUES
('13','تلمسان','Tlemcen','Tlemcen'),
('16','الجزائر','Alger','Algiers'),
('31','وهران','Oran','Oran'),
('25','قسنطينة','Constantine','Constantine');

INSERT INTO communes (wilaya_id, name_ar, name_fr, name_en) VALUES
(1,'تلمسان','Tlemcen','Tlemcen'),
(1,'المنصورة','Mansourah','Mansourah'),
(2,'باب الوادي','Bab El Oued','Bab El Oued'),
(3,'وهران','Oran','Oran');

INSERT INTO property_types (code, name_ar, name_fr, name_en, requires_named_list) VALUES
('private','عقار خاص','Bien privé','Private property',0),
('social','سكن اجتماعي','Logement social','Social housing',1),
('aadl','سكن عدل (AADL)','Logement AADL','AADL housing',1),
('opgi','سكن ديوان الترقية (OPGI)','Logement OPGI','OPGI housing',1),
('promotional','سكن ترقوي','Logement promotionnel','Promotional housing',0),
('subsidized','سكن مدعوم','Logement aidé','Subsidized housing',1),
('commercial','محل تجاري','Local commercial','Commercial premises',0),
('land','أرض','Terrain','Land',0),
('state','عقار تابع للدولة','Bien de l''État','State property',0);

-- الأشخاص (السجل المدني) — NIN مكوّن من 18 رقمًا
INSERT INTO persons (nin, first_name, last_name, birth_date, gender, civil_status, created_at) VALUES
('101300012019845001','أحمد','بن يوسف','1985-04-12','m','married',NOW()),
('101600022019845002','فاطمة','مرابط','1990-09-03','f','married',NOW()),
('103100032019845003','كريم','حمداني','1978-01-25','m','single',NOW()),
('102500042019845004','سمية','بلعباس','1995-06-18','f','single',NOW()),
('101300052019845005','يوسف','زيتوني','1982-11-30','m','divorced',NOW()),
('101600062019845006','نادية','شريف','1988-03-14','f','married',NOW());

-- العقارات (السجل العقاري)
INSERT INTO properties (ref, type_id, address, wilaya_id, commune_id, owner_nin, created_at) VALUES
('PRP-13-000001',1,'حي الإخوة بلحاج، عمارة 4، تلمسان',1,1,'101300012019845001',NOW()),  -- خاص، مالك أحمد
('PRP-16-000002',2,'حي 500 مسكن، باب الوادي، الجزائر',2,3,NULL,NOW()),                    -- اجتماعي، لا مالك مسجّل
('PRP-31-000003',4,'تجزئة الأمل، وهران',3,4,'103100032019845003',NOW()),                   -- مدعوم
('PRP-25-000004',6,'قطعة أرض رقم 22، المنطقة الصناعية، قسنطينة',4,NULL,'101300052019845005',NOW()), -- أرض
('PRP-13-000005',3,'إقامة الرياض، المنصورة، تلمسان',1,2,'101600062019845006',NOW());        -- ترقوي

-- العلاقات القانونية
INSERT INTO legal_relations (person_id, property_id, relation_type, source, status, start_date, created_at) VALUES
(1,1,'owner','real_estate_registry','active','2015-05-01',NOW()),
(2,1,'spouse','civil_registry','active','2015-05-01',NOW()),
(3,3,'beneficiary','housing_db','active','2020-02-10',NOW()),
(5,4,'owner','real_estate_registry','active','2012-08-15',NOW()),
(6,5,'owner','real_estate_registry','active','2018-03-20',NOW()),
(4,5,'partner','real_estate_registry','active','2018-03-20',NOW());

-- القائمة الاسمية للمستفيدين (لسكن مدعوم/اجتماعي)
INSERT INTO named_lists (property_id, nin, full_name, program, added_at) VALUES
(3,'103100032019845003','كريم حمداني','AADL 2',NOW()),
(2,'102500042019845004','سمية بلعباس','LSP',NOW());

-- القيود القانونية
INSERT INTO legal_constraints (property_id, type, type_ar, type_fr, type_en, description, is_blocking, status, created_at) VALUES
(4,'seizure','حجز قضائي','Saisie','Seizure','حجز تنفيذي بموجب حكم قضائي',1,'active',NOW()),
(1,'mortgage','رهن عقاري','Hypothèque','Mortgage','رهن لصالح بنك',0,'active',NOW());

SET FOREIGN_KEY_CHECKS = 1;

-- الدفتر العائلي (بذرة)
INSERT INTO family_books (book_no, head_nin, wilaya_id, commune_id, created_at) VALUES
('FB-13-000001','101300012019845001',1,1,NOW());
INSERT INTO family_members (family_id, nin, relation, added_at) VALUES
(1,'101300012019845001','head',NOW()),
(1,'101600022019845002','spouse',NOW());

-- إثراء أول عقارين ببيانات الموقع والمساحة والصيغة
UPDATE properties SET area_m2=95.5, latitude=34.8825, longitude=-1.3145, housing_program='aadl', location_note='حي المنصورة، عمارة B3' WHERE ref='PRP-13-000001';
UPDATE properties SET area_m2=120.0, latitude=34.8790, longitude=-1.3200, housing_program='opgi' WHERE ref='PRP-16-000002';