-- ⛔ لا تستورد هذا الملف في MySQL / phpMyAdmin — سيُعطي الخطأ #1064.
-- ⛔ NE PAS importer dans MySQL / phpMyAdmin — provoque l'erreur #1064.
-- ⛔ DO NOT import into MySQL / phpMyAdmin — causes error #1064.
--
-- هذا الملف بصيغة SQLite (AUTOINCREMENT) وهو مخصّص للاختبار المحلي فقط.
-- للإنتاج على MySQL استعمل: database/schema.sql
-- =====================================================================
-- aqariverify — SQLite schema (اختبار محلي فقط). الإنتاج يستعمل database/schema.sql (MySQL).
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL, full_name TEXT NOT NULL, role TEXT NOT NULL, entity_scope TEXT, custom_permissions TEXT,
  lang TEXT NOT NULL DEFAULT 'ar', theme TEXT NOT NULL DEFAULT 'light', is_active INTEGER NOT NULL DEFAULT 1,
  last_login_at TEXT, created_at TEXT NOT NULL);
CREATE TABLE login_attempts (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, ip TEXT NOT NULL, success INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL);
CREATE TABLE password_resets (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, token TEXT NOT NULL, expires_at TEXT NOT NULL, used INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL);
CREATE TABLE wilayas (id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT NOT NULL UNIQUE, name_ar TEXT NOT NULL, name_fr TEXT NOT NULL, name_en TEXT NOT NULL);
CREATE TABLE communes (id INTEGER PRIMARY KEY AUTOINCREMENT, wilaya_id INTEGER NOT NULL, name_ar TEXT NOT NULL, name_fr TEXT NOT NULL, name_en TEXT NOT NULL);
CREATE TABLE property_types (id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT NOT NULL UNIQUE, name_ar TEXT NOT NULL, name_fr TEXT NOT NULL, name_en TEXT NOT NULL, requires_named_list INTEGER NOT NULL DEFAULT 0);
CREATE TABLE persons (id INTEGER PRIMARY KEY AUTOINCREMENT, nin TEXT NOT NULL UNIQUE, first_name TEXT NOT NULL, last_name TEXT NOT NULL, birth_date TEXT, gender TEXT DEFAULT 'm', civil_status TEXT DEFAULT 'single', created_at TEXT NOT NULL);
CREATE TABLE properties (id INTEGER PRIMARY KEY AUTOINCREMENT, ref TEXT NOT NULL UNIQUE, type_id INTEGER NOT NULL, address TEXT NOT NULL, wilaya_id INTEGER, commune_id INTEGER, owner_nin TEXT, area_m2 REAL, latitude REAL, longitude REAL, housing_program TEXT NOT NULL DEFAULT 'none', location_note TEXT, created_at TEXT NOT NULL);
CREATE TABLE legal_relations (id INTEGER PRIMARY KEY AUTOINCREMENT, person_id INTEGER NOT NULL, property_id INTEGER NOT NULL, relation_type TEXT NOT NULL, source TEXT DEFAULT 'real_estate_registry', status TEXT NOT NULL DEFAULT 'active', start_date TEXT, end_date TEXT, created_by INTEGER, created_at TEXT NOT NULL);
CREATE TABLE named_lists (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, nin TEXT NOT NULL, full_name TEXT NOT NULL, program TEXT, added_at TEXT NOT NULL);
CREATE TABLE legal_constraints (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, type TEXT NOT NULL, type_ar TEXT, type_fr TEXT, type_en TEXT, description TEXT, is_blocking INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'active', created_at TEXT NOT NULL);
CREATE TABLE verification_requests (id INTEGER PRIMARY KEY AUTOINCREMENT, ref TEXT NOT NULL UNIQUE, requester_user_id INTEGER, government_entity TEXT, service_type TEXT, property_id INTEGER, nin TEXT NOT NULL, claimed_relation TEXT, result TEXT NOT NULL, score INTEGER NOT NULL DEFAULT 0, details_json TEXT, created_at TEXT NOT NULL);
CREATE TABLE review_cases (id INTEGER PRIMARY KEY AUTOINCREMENT, ref TEXT NOT NULL UNIQUE, verification_request_id INTEGER, property_id INTEGER, nin TEXT NOT NULL, reason_codes TEXT, status TEXT NOT NULL DEFAULT 'open', assigned_to INTEGER, resolution_note TEXT, created_at TEXT NOT NULL, resolved_at TEXT);
CREATE TABLE events (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER, person_nin TEXT, event_type TEXT NOT NULL, description TEXT NOT NULL, user_id INTEGER, created_at TEXT NOT NULL);
CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, action TEXT NOT NULL, entity TEXT, entity_id INTEGER, ip TEXT, meta_json TEXT, created_at TEXT NOT NULL);
CREATE TABLE notifications (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, target_role TEXT, title TEXT NOT NULL, body TEXT, type TEXT DEFAULT 'info', link TEXT, is_read INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL);
CREATE TABLE trust_index (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL UNIQUE, score INTEGER NOT NULL DEFAULT 100, factors_json TEXT, computed_at TEXT NOT NULL);
CREATE TABLE family_books (id INTEGER PRIMARY KEY AUTOINCREMENT, book_no TEXT NOT NULL UNIQUE, head_nin TEXT NOT NULL, wilaya_id INTEGER, commune_id INTEGER, created_by INTEGER, created_at TEXT NOT NULL);
CREATE TABLE property_contracts (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER NOT NULL, contract_type TEXT NOT NULL, party_nin TEXT NOT NULL, owner_nin TEXT, start_date TEXT, end_date TEXT, status TEXT NOT NULL DEFAULT 'active', notary_user_id INTEGER, note TEXT, created_at TEXT NOT NULL);
CREATE TABLE residence_audit (id INTEGER PRIMARY KEY AUTOINCREMENT, property_id INTEGER, property_ref TEXT, applicant_nin TEXT, applicant_name TEXT, result TEXT NOT NULL, relation TEXT, commune_user_id INTEGER, created_at TEXT NOT NULL);
CREATE TABLE family_members (id INTEGER PRIMARY KEY AUTOINCREMENT, family_id INTEGER NOT NULL, nin TEXT NOT NULL, relation TEXT NOT NULL DEFAULT 'other', added_at TEXT NOT NULL);


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 État','State property',0);
INSERT INTO persons (nin,first_name,last_name,birth_date,gender,civil_status,created_at) VALUES
('101300012019845001','أحمد','بن يوسف','1985-04-12','m','married',datetime('now')),
('101600022019845002','فاطمة','مرابط','1990-09-03','f','married',datetime('now')),
('103100032019845003','كريم','حمداني','1978-01-25','m','single',datetime('now')),
('102500042019845004','سمية','بلعباس','1995-06-18','f','single',datetime('now')),
('101300052019845005','يوسف','زيتوني','1982-11-30','m','divorced',datetime('now')),
('101600062019845006','نادية','شريف','1988-03-14','f','married',datetime('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',datetime('now')),
('PRP-16-000002',2,'حي 500 مسكن، باب الوادي، الجزائر',2,3,NULL,datetime('now')),
('PRP-31-000003',4,'تجزئة الأمل، وهران',3,4,'103100032019845003',datetime('now')),
('PRP-25-000004',6,'قطعة أرض رقم 22، المنطقة الصناعية، قسنطينة',4,NULL,'101300052019845005',datetime('now')),
('PRP-13-000005',3,'إقامة الرياض، المنصورة، تلمسان',1,2,'101600062019845006',datetime('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',datetime('now')),
(2,1,'spouse','civil_registry','active','2015-05-01',datetime('now')),
(3,3,'beneficiary','housing_db','active','2020-02-10',datetime('now')),
(5,4,'owner','real_estate_registry','active','2012-08-15',datetime('now')),
(6,5,'owner','real_estate_registry','active','2018-03-20',datetime('now')),
(4,5,'partner','real_estate_registry','active','2018-03-20',datetime('now'));
INSERT INTO named_lists (property_id,nin,full_name,program,added_at) VALUES
(3,'103100032019845003','كريم حمداني','AADL 2',datetime('now')),
(2,'102500042019845004','سمية بلعباس','LSP',datetime('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',datetime('now')),
(1,'mortgage','رهن عقاري','Hypothèque','Mortgage','رهن لصالح بنك',0,'active',datetime('now'));

INSERT INTO family_books (book_no,head_nin,wilaya_id,commune_id,created_at) VALUES
('FB-13-000001','101300012019845001',1,1,datetime('now'));
INSERT INTO family_members (family_id,nin,relation,added_at) VALUES
(1,'101300012019845001','head',datetime('now')),
(1,'101600022019845002','spouse',datetime('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';

INSERT INTO property_contracts (property_id,contract_type,party_nin,owner_nin,start_date,end_date,status,created_at) VALUES
(1,'rental','101300052019845005','101300012019845001','2023-01-01','2024-01-01','ended',datetime('now')),
(2,'sale','101600022019845002',NULL,'2022-06-15',NULL,'active',datetime('now'));

-- ==== Migration v3 (SQLite test mirror): الهرمية السكنية ====
CREATE TABLE housing_projects (id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL, housing_program TEXT NOT NULL DEFAULT 'aadl', wilaya_id INTEGER, commune_id INTEGER, total_units_planned INTEGER, status TEXT NOT NULL DEFAULT 'planned', created_by INTEGER, created_at TEXT NOT NULL, updated_at TEXT);
CREATE TABLE housing_blocks (id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, block_code TEXT NOT NULL, floors_count INTEGER, created_at TEXT NOT NULL, UNIQUE(project_id, block_code));
CREATE TABLE housing_units (id INTEGER PRIMARY KEY AUTOINCREMENT, block_id INTEGER NOT NULL, floor_number INTEGER NOT NULL, unit_number TEXT NOT NULL, ref TEXT NOT NULL UNIQUE, surface_m2 REAL, property_id INTEGER, status TEXT NOT NULL DEFAULT 'vacant', created_at TEXT NOT NULL, updated_at TEXT, UNIQUE(block_id, floor_number, unit_number));
CREATE TABLE unit_beneficiary_assignments (id INTEGER PRIMARY KEY AUTOINCREMENT, unit_id INTEGER NOT NULL, beneficiary_id INTEGER NOT NULL, family_id INTEGER, status TEXT NOT NULL DEFAULT 'active', assigned_at TEXT NOT NULL, ended_at TEXT, end_reason TEXT, created_by INTEGER, created_at TEXT NOT NULL);
-- ملاحظة: القيد الفريد الجزئي (إسناد نشط واحد فقط) غير مُطبَّق هنا على مستوى القاعدة؛
-- SQLite للاختبار المحلي فقط، وHousingHierarchyService يفرض القاعدة تطبيقياً في كل الحالات.