-- ============================================================
-- Setup Super Admin Tables - socialar_socialar_commerce
-- ============================================================

-- Tabela de Super Admins (se não existir)
CREATE TABLE IF NOT EXISTS super_admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500) DEFAULT NULL,
    role VARCHAR(50) DEFAULT 'superadmin',
    is_active TINYINT(1) DEFAULT 1,
    last_login_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de sessões de impersonation
CREATE TABLE IF NOT EXISTS impersonation_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    super_admin_id BIGINT UNSIGNED NOT NULL,
    store_id BIGINT UNSIGNED NOT NULL,
    target_user_id BIGINT UNSIGNED DEFAULT NULL,
    token VARCHAR(128) NOT NULL UNIQUE,
    reason TEXT DEFAULT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME DEFAULT NULL,
    ended_at DATETIME DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_impersonation_token (token),
    INDEX idx_impersonation_admin (super_admin_id),
    INDEX idx_impersonation_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de métricas SaaS (para o dashboard do Super Admin)
CREATE TABLE IF NOT EXISTS saas_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    total_stores INT DEFAULT 0,
    active_stores INT DEFAULT 0,
    trial_stores INT DEFAULT 0,
    churned_stores INT DEFAULT 0,
    new_stores INT DEFAULT 0,
    mrr DECIMAL(12, 2) DEFAULT 0.00,
    arr DECIMAL(12, 2) DEFAULT 0.00,
    new_mrr DECIMAL(12, 2) DEFAULT 0.00,
    churned_mrr DECIMAL(12, 2) DEFAULT 0.00,
    total_orders INT DEFAULT 0,
    total_users INT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de planos (se não existir)
CREATE TABLE IF NOT EXISTS plans (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT DEFAULT NULL,
    price_monthly DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    price_yearly DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'BRL',
    max_products INT DEFAULT NULL,
    max_orders_per_month INT DEFAULT NULL,
    max_staff_users INT DEFAULT NULL,
    features JSON DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    is_featured TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de subscriptions (assinaturas)
CREATE TABLE IF NOT EXISTS subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    store_id BIGINT UNSIGNED NOT NULL,
    plan_id VARCHAR(64) NOT NULL,
    provider VARCHAR(64) DEFAULT 'manual',
    provider_subscription_id VARCHAR(191) DEFAULT NULL,
    status ENUM('trialing', 'active', 'past_due', 'canceled', 'incomplete', 'unpaid') DEFAULT 'trialing',
    trial_ends_at DATETIME DEFAULT NULL,
    current_period_start DATETIME DEFAULT NULL,
    current_period_end DATETIME DEFAULT NULL,
    cancel_at_period_end TINYINT(1) DEFAULT 0,
    canceled_at DATETIME DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscriptions_store (store_id),
    INDEX idx_subscriptions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserir Super Admin padrão (senha: admin123)
INSERT IGNORE INTO super_admins (email, password_hash, name, role) VALUES
('admin@barberflow.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', 'superadmin');

-- Inserir planos padrão
INSERT IGNORE INTO plans (id, name, description, price_monthly, price_yearly, max_products, max_orders_per_month, max_staff_users, features, is_active, is_featured, sort_order) VALUES
('free', 'Grátis', 'Para começar', 0.00, 0.00, 10, 50, 1, '["dashboard","basic_reports"]', 1, 0, 1),
('starter', 'Starter', 'Para pequenos negócios', 49.90, 479.00, 100, 500, 3, '["dashboard","reports","whatsapp_integration"]', 1, 0, 2),
('pro', 'Profissional', 'Para negócios em crescimento', 99.90, 959.00, NULL, NULL, 10, '["dashboard","advanced_reports","whatsapp_integration","api_access","custom_domain"]', 1, 1, 3),
('enterprise', 'Enterprise', 'Para grandes operações', 299.90, 2879.00, NULL, NULL, NULL, '["all_features","dedicated_support","white_label"]', 1, 0, 4);

-- Adicionar coluna status na tabela stores se não existir
-- ALTER TABLE stores ADD COLUMN IF NOT EXISTS status ENUM('trial', 'active', 'past_due', 'cancelled', 'suspended') DEFAULT 'active';
-- ALTER TABLE stores ADD COLUMN IF NOT EXISTS plan_id VARCHAR(64) DEFAULT 'free';

SELECT 'Setup concluído com sucesso!' as resultado;
