-- SaaS Multi-Tenant Architecture
-- عزل كامل: مختبر A لا يرى بيانات مختبر B

-- 1. خطط الاشتراك
CREATE TABLE IF NOT EXISTS subscription_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    max_branches INT NOT NULL DEFAULT 1,
    max_users INT NOT NULL DEFAULT 5,
    max_patients_per_month INT NULL,
    storage_gb DECIMAL(10,2) NULL,
    api_access TINYINT(1) NOT NULL DEFAULT 0,
    price_monthly DECIMAL(10,2) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO subscription_plans (code, name, max_branches, max_users, max_patients_per_month, storage_gb, api_access, price_monthly)
VALUES
('basic', 'Basic', 1, 5, 500, 1, 0, 29.99),
('pro', 'Pro', 3, 15, 2000, 5, 1, 79.99),
('enterprise', 'Enterprise', 999, 999, NULL, 50, 1, 199.99)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 2. جدول المختبرات (Tenants)
CREATE TABLE IF NOT EXISTS tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    plan_id INT NOT NULL DEFAULT 1,
    logo_url VARCHAR(500) NULL,
    primary_color VARCHAR(20) NULL DEFAULT '#0d9488',
    status ENUM('active','suspended','trial') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- 3. اشتراك المختبر (للتجديد والتتبع)
CREATE TABLE IF NOT EXISTS tenant_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    plan_id INT NOT NULL,
    started_at DATE NOT NULL,
    expires_at DATE NULL,
    status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- 4. إدراج المختبر الافتراضي (للنشرات الحالية)
INSERT INTO tenants (id, slug, name, plan_id, status) 
VALUES (1, 'main', 'المختبر الرئيسي', 1, 'active')
ON DUPLICATE KEY UPDATE slug = VALUES(slug);
