-- =============================================
-- Daleel App - Full Database Schema
-- =============================================

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

-- =============================================
-- Table: users
-- =============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    role ENUM('user', 'business', 'admin') DEFAULT 'user',
    avatar VARCHAR(255),
    fcm_token VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- Table: cities
-- =============================================
CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100),
    country VARCHAR(100) DEFAULT 'Lebanon',
    is_active TINYINT(1) DEFAULT 1
);

-- =============================================
-- Table: categories
-- =============================================
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100),
    icon VARCHAR(255),
    color VARCHAR(20) DEFAULT '#4CAF50',
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- Table: businesses
-- =============================================
CREATE TABLE businesses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150),
    category_id INT NOT NULL,
    city_id INT,
    description TEXT,
    description_ar TEXT,
    phone VARCHAR(20),
    whatsapp VARCHAR(20),
    email VARCHAR(150),
    website VARCHAR(255),
    address TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    cover_photo VARCHAR(255),
    profile_photo VARCHAR(255),
    rating DECIMAL(3, 2) DEFAULT 0.00,
    rating_count INT DEFAULT 0,
    is_featured TINYINT(1) DEFAULT 0,
    is_verified TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

-- =============================================
-- Table: working_hours
-- =============================================
CREATE TABLE working_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    day_of_week ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
    open_time TIME,
    close_time TIME,
    is_closed TINYINT(1) DEFAULT 0,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

-- =============================================
-- Table: business_gallery
-- =============================================
CREATE TABLE business_gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    image VARCHAR(255) NOT NULL,
    caption VARCHAR(255),
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

-- =============================================
-- Table: offers
-- =============================================
CREATE TABLE offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    title_ar VARCHAR(200),
    description TEXT,
    description_ar TEXT,
    image VARCHAR(255),
    discount_percentage INT,
    original_price DECIMAL(10, 2),
    discounted_price DECIMAL(10, 2),
    start_date DATE,
    expiry_date DATE,
    is_featured TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

-- =============================================
-- Table: favorites
-- =============================================
CREATE TABLE favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    business_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_favorite (user_id, business_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

-- =============================================
-- Table: notifications
-- =============================================
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    title_ar VARCHAR(200),
    description TEXT,
    description_ar TEXT,
    image VARCHAR(255),
    type ENUM('offer', 'promotion', 'announcement', 'general') DEFAULT 'general',
    business_id INT,
    target_all TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE SET NULL
);

-- =============================================
-- Table: user_notifications (read status)
-- =============================================
CREATE TABLE user_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    notification_id INT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
);

-- =============================================
-- Table: pages (static content)
-- =============================================
CREATE TABLE pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    title_ar VARCHAR(200),
    content LONGTEXT,
    content_ar LONGTEXT,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- Table: reviews
-- =============================================
CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    business_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_review (user_id, business_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
);

-- =============================================
-- Seed Data: Cities
-- =============================================
INSERT INTO cities (name, name_ar) VALUES
('Beirut', 'بيروت'),
('Tripoli', 'طرابلس'),
('Sidon', 'صيدا'),
('Tyre', 'صور'),
('Zahle', 'زحلة'),
('Jounieh', 'جونية'),
('Baalbek', 'بعلبك'),
('Byblos', 'جبيل');

-- =============================================
-- Seed Data: Categories
-- =============================================
INSERT INTO categories (name, name_ar, icon, color) VALUES
('Restaurants', 'مطاعم', 'restaurant', '#FF5722'),
('Car Services', 'خدمات السيارات', 'car_repair', '#2196F3'),
('Car Wash', 'غسيل سيارات', 'local_car_wash', '#03A9F4'),
('Coffee Shops', 'مقاهي', 'local_cafe', '#795548'),
('Pharmacies', 'صيدليات', 'local_pharmacy', '#4CAF50'),
('Supermarkets', 'سوبرماركت', 'shopping_cart', '#FF9800'),
('Electronics', 'إلكترونيات', 'devices', '#9C27B0'),
('Beauty & Spa', 'تجميل وسبا', 'spa', '#E91E63'),
('Clinics', 'عيادات', 'medical_services', '#00BCD4'),
('Bakeries', 'مخابز', 'bakery_dining', '#FF8F00'),
('Gyms', 'صالات رياضية', 'fitness_center', '#F44336'),
('Hotels', 'فنادق', 'hotel', '#607D8B');

-- =============================================
-- Seed Data: Pages
-- =============================================
INSERT INTO pages (slug, title, title_ar, content, content_ar) VALUES
('privacy-policy', 'Privacy Policy', 'سياسة الخصوصية',
'<h2>Privacy Policy</h2><p>This privacy policy explains how Daleel App collects, uses, and protects your personal information.</p><h3>Information We Collect</h3><p>We collect information you provide when registering, such as your name, email, and phone number.</p><h3>How We Use Your Information</h3><p>We use your information to provide and improve our services, send notifications, and communicate with you.</p>',
'<h2>سياسة الخصوصية</h2><p>توضح سياسة الخصوصية هذه كيفية جمع تطبيق دليل لمعلوماتك الشخصية واستخدامها وحمايتها.</p>'),

('terms-conditions', 'Terms & Conditions', 'الشروط والأحكام',
'<h2>Terms & Conditions</h2><p>By using Daleel App, you agree to these terms and conditions. Please read them carefully.</p><h3>Use of Service</h3><p>You agree to use the service only for lawful purposes and in a manner that does not infringe on others rights.</p>',
'<h2>الشروط والأحكام</h2><p>باستخدام تطبيق دليل، فإنك توافق على هذه الشروط والأحكام. يرجى قراءتها بعناية.</p>'),

('about-us', 'About Us', 'من نحن',
'<h2>About Daleel</h2><p>Daleel is a comprehensive business directory app designed to help you discover local businesses in your city.</p><p>Our mission is to connect customers with the best local businesses.</p>',
'<h2>عن دليل</h2><p>دليل هو تطبيق دليل أعمال شامل مصمم لمساعدتك في اكتشاف الأعمال التجارية المحلية في مدينتك.</p>');

-- =============================================
-- Seed Data: Demo Admin User
-- =============================================
INSERT INTO users (name, email, password, phone, role) VALUES
('Admin', 'admin@daleel.app', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+961', 'admin');
-- Default password: password