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

-- جدول الخطوط
CREATE TABLE fonts (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    category ENUM('TIFINAGH', 'LATIN', 'ARABIC', 'MY_FONTS') NOT NULL,
    download_url VARCHAR(500) NOT NULL,
    preview_url VARCHAR(500),
    file_size BIGINT DEFAULT 0,
    is_premium BOOLEAN DEFAULT FALSE,
    downloads INT DEFAULT 0,
    rating DECIMAL(2,1) DEFAULT 0.0,
    version VARCHAR(20) DEFAULT '1.0',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (category),
    INDEX idx_premium (is_premium),
    INDEX idx_downloads (downloads DESC),
    INDEX idx_rating (rating DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول التحميلات (Analytics)
CREATE TABLE font_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    font_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(100),
    device_info TEXT,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (font_id) REFERENCES fonts(id) ON DELETE CASCADE,
    INDEX idx_font_id (font_id),
    INDEX idx_downloaded_at (downloaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول التقييمات
CREATE TABLE font_ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    font_id VARCHAR(50) NOT NULL,
    user_id VARCHAR(100) NOT NULL,
    rating TINYINT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (font_id) REFERENCES fonts(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_rating (font_id, user_id),
    INDEX idx_font_id (font_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المستخدمين Premium (اختياري)
CREATE TABLE premium_users (
    user_id VARCHAR(100) PRIMARY KEY,
    subscription_type ENUM('monthly', 'yearly', 'lifetime') NOT NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- إدخال بيانات تجريبية
INSERT INTO fonts (id, name, file_name, category, download_url, preview_url, file_size, is_premium, downloads, rating) VALUES
('font_001', 'Cairo Bold', 'cairo_bold.ttf', 'ARABIC', 'https://yourserver.com/fonts/cairo_bold.ttf', 'https://yourserver.com/previews/cairo_bold.png', 245760, FALSE, 1250, 4.5),
('font_002', 'Amiri Regular', 'amiri_regular.ttf', 'ARABIC', 'https://yourserver.com/fonts/amiri_regular.ttf', 'https://yourserver.com/previews/amiri_regular.png', 312480, FALSE, 890, 4.8),
('font_003', 'Roboto Black', 'roboto_black.ttf', 'LATIN', 'https://yourserver.com/fonts/roboto_black.ttf', NULL, 168920, TRUE, 2340, 4.7),
('font_004', 'Tifinagh Modern', 'tifinagh_modern.otf', 'TIFINAGH', 'https://yourserver.com/fonts/tifinagh_modern.otf', NULL, 198400, FALSE, 567, 4.3),
('font_005', 'Noto Naskh Arabic', 'noto_naskh.ttf', 'ARABIC', 'https://yourserver.com/fonts/noto_naskh.ttf', NULL, 423680, TRUE, 1789, 4.9),
('font_006', 'Open Sans Bold', 'opensans_bold.ttf', 'LATIN', 'https://yourserver.com/fonts/opensans_bold.ttf', NULL, 156800, FALSE, 3456, 4.6);

-- Stored Procedures مفيدة

-- زيادة عدد التحميلات
DELIMITER $$
CREATE PROCEDURE increment_download_count(IN font_id_param VARCHAR(50))
BEGIN
    UPDATE fonts 
    SET downloads = downloads + 1 
    WHERE id = font_id_param;
END$$
DELIMITER ;

-- حساب متوسط التقييم
DELIMITER $$
CREATE PROCEDURE update_font_rating(IN font_id_param VARCHAR(50))
BEGIN
    UPDATE fonts f
    SET rating = (
        SELECT COALESCE(AVG(rating), 0)
        FROM font_ratings
        WHERE font_id = font_id_param
    )
    WHERE f.id = font_id_param;
END$$
DELIMITER ;

-- التحقق من Premium
DELIMITER $$
CREATE FUNCTION is_premium_user(user_id_param VARCHAR(100))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE is_premium BOOLEAN DEFAULT FALSE;
    
    SELECT COUNT(*) > 0 INTO is_premium
    FROM premium_users
    WHERE user_id = user_id_param
    AND (expires_at IS NULL OR expires_at > NOW());
    
    RETURN is_premium;
END$$
DELIMITER ;

-- Views مفيدة

-- أكثر الخطوط تحميلاً
CREATE VIEW most_downloaded_fonts AS
SELECT id, name, category, downloads, rating
FROM fonts
ORDER BY downloads DESC
LIMIT 10;

-- أعلى تقييم
CREATE VIEW top_rated_fonts AS
SELECT id, name, category, downloads, rating
FROM fonts
WHERE rating >= 4.5
ORDER BY rating DESC, downloads DESC
LIMIT 10;

-- الخطوط المجانية
CREATE VIEW free_fonts AS
SELECT id, name, category, downloads, rating
FROM fonts
WHERE is_premium = FALSE
ORDER BY downloads DESC;