-- =============================================
-- SocialPro — قاعدة البيانات
-- =============================================

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

-- المستخدمون
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','editor') DEFAULT 'editor',
    created_at DATETIME DEFAULT NOW()
);

-- العملاء / المشاريع
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    logo_url VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT NOW()
);

-- الحسابات المربوطة
CREATE TABLE IF NOT EXISTS accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    platform ENUM('instagram','tiktok','twitter','facebook','linkedin','youtube') NOT NULL,
    username VARCHAR(150) NOT NULL,
    display_name VARCHAR(150),
    platform_user_id VARCHAR(100),
    is_connected TINYINT(1) DEFAULT 1,
    connected_at DATETIME DEFAULT NOW(),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    UNIQUE KEY unique_account (client_id, platform, username)
);

-- التوكنات (مشفرة)
CREATE TABLE IF NOT EXISTS tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL UNIQUE,
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    expires_at DATETIME,
    updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- المنشورات
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    posted_by INT NOT NULL,
    caption TEXT,
    media_url VARCHAR(1000),
    cover_url VARCHAR(1000),
    post_type ENUM('reel','post','story','tweet','tiktok') DEFAULT 'post',
    status ENUM('draft','scheduled','published','failed') DEFAULT 'draft',
    scheduled_at DATETIME,
    published_at DATETIME,
    platform_post_id VARCHAR(200),
    error_msg TEXT,
    created_at DATETIME DEFAULT NOW(),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (posted_by) REFERENCES users(id)
);

-- مستخدم افتراضي (admin / admin123)
INSERT IGNORE INTO users (name, email, password, role)
VALUES ('Admin', 'admin@socialpro.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');