-- جدول المشاريع
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255),
    description TEXT,
    area FLOAT,
    units_count INT,
    status ENUM('مكتمل','تحت الانشاء','تم البيع') NOT NULL,
    finish_date DATE,
    price_from DECIMAL(12,2),
    price_to DECIMAL(12,2),
    brochure VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- جدول نماذج الوحدات
CREATE TABLE IF NOT EXISTS unit_models (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    name VARCHAR(255),
    area FLOAT,
    price DECIMAL(12,2),
    bedrooms INT,
    bathrooms INT,
    features TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- جدول الصور والفيديوهات
CREATE TABLE IF NOT EXISTS media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    type ENUM('image','video'),
    file_path VARCHAR(255),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- جدول الأماكن القريبة
CREATE TABLE IF NOT EXISTS nearby_places (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    place_type ENUM('مدرسة','حديقة','مسجد','مجمع تجاري','مستشفى'),
    name VARCHAR(255),
    map_url VARCHAR(255),
    distance_km FLOAT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- جدول المستخدمين
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) UNIQUE,
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    role ENUM('admin','editor','viewer') DEFAULT 'viewer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- جدول الرسائل والاستفسارات
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    project_id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    message TEXT,
    status ENUM('جديد','تم الرد','محذوف') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- جدول الإعدادات العامة
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(100) UNIQUE,
    value TEXT
);
