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

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

-- العملاء / المشاريع
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 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)
);
