-- Migração: Sistema de Assinaturas
-- Data: 2026-04-07

USE saas_barbearia;

-- Tabela de planos de assinatura
CREATE TABLE membership_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'BRL',
    billing_cycle ENUM('daily', 'weekly', 'monthly', 'quarterly', 'semiannual', 'annual') NOT NULL,
    trial_days INT DEFAULT 0,
    setup_fee DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('active', 'inactive', 'archived') DEFAULT 'active',
    max_members INT DEFAULT NULL, -- NULL = ilimitado
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_status (company_id, status)
);

-- Tabela de regras dos planos
CREATE TABLE membership_plan_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    rule_type ENUM('usage_limit', 'time_limit', 'feature_access') NOT NULL,
    rule_key VARCHAR(100) NOT NULL, -- Ex: 'appointments_per_month', 'days_valid', 'premium_features'
    rule_value VARCHAR(255) NOT NULL, -- Valor da regra (pode ser número, JSON, etc)
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES membership_plans(id) ON DELETE CASCADE,
    UNIQUE KEY unique_plan_rule (plan_id, rule_key)
);

-- Tabela de assinaturas (contratos ativos)
CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    plan_id INT NOT NULL,
    client_id INT NOT NULL, -- Referência ao usuário cliente
    subscription_id VARCHAR(255) UNIQUE, -- ID do gateway de pagamento
    status ENUM('active', 'trial', 'past_due', 'canceled', 'expired', 'suspended') DEFAULT 'trial',
    current_period_start TIMESTAMP NOT NULL,
    current_period_end TIMESTAMP NOT NULL,
    trial_end TIMESTAMP NULL,
    canceled_at TIMESTAMP NULL,
    cancel_reason TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES membership_plans(id) ON DELETE RESTRICT,
    FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_company_client (company_id, client_id),
    INDEX idx_status_period (status, current_period_end)
);

-- Tabela de ciclos de cobrança
CREATE TABLE membership_cycles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    membership_id INT NOT NULL,
    cycle_number INT NOT NULL, -- Número sequencial do ciclo
    period_start TIMESTAMP NOT NULL,
    period_end TIMESTAMP NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'BRL',
    status ENUM('pending', 'paid', 'failed', 'refunded', 'canceled') DEFAULT 'pending',
    payment_date TIMESTAMP NULL,
    payment_method VARCHAR(50),
    transaction_id VARCHAR(255),
    invoice_url VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE CASCADE,
    UNIQUE KEY unique_membership_cycle (membership_id, cycle_number),
    INDEX idx_status_payment (status, payment_date)
);

-- Tabela de controle de uso/consumo
CREATE TABLE membership_usages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    membership_id INT NOT NULL,
    usage_type VARCHAR(100) NOT NULL, -- Ex: 'appointment', 'service_visit', 'feature_access'
    usage_key VARCHAR(255) NOT NULL, -- Identificador específico (ID do agendamento, etc)
    usage_value DECIMAL(10,2) DEFAULT 1.00, -- Quantidade usada (1 para contagem simples)
    usage_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE CASCADE,
    INDEX idx_membership_type_date (membership_id, usage_type, usage_date),
    INDEX idx_usage_key (usage_key)
);

-- Tabela de logs de status das assinaturas
CREATE TABLE membership_status_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    membership_id INT NOT NULL,
    old_status ENUM('active', 'trial', 'past_due', 'canceled', 'expired', 'suspended'),
    new_status ENUM('active', 'trial', 'past_due', 'canceled', 'expired', 'suspended') NOT NULL,
    changed_by INT NULL, -- Usuário que fez a mudança
    change_reason TEXT,
    notes TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_membership_changed (membership_id, changed_at)
);

-- Tabela de comissões (para profissionais)
CREATE TABLE membership_commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    membership_id INT NOT NULL,
    professional_id INT NOT NULL,
    commission_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    commission_value DECIMAL(5,2) NOT NULL, -- 5.00 para 5% ou valor fixo
    commission_amount DECIMAL(10,2) NOT NULL, -- Valor calculado da comissão
    reference_type VARCHAR(50) NOT NULL, -- 'appointment', 'service', etc
    reference_id INT NOT NULL, -- ID do agendamento, serviço, etc
    status ENUM('pending', 'paid', 'canceled') DEFAULT 'pending',
    paid_at TIMESTAMP NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE CASCADE,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    INDEX idx_professional_status (professional_id, status),
    INDEX idx_reference (reference_type, reference_id)
);

-- Inserir dados de exemplo para planos
INSERT INTO membership_plans (company_id, name, description, price, billing_cycle, trial_days, max_members) VALUES
(1, 'Plano Básico', 'Acesso básico aos serviços', 29.90, 'monthly', 7, NULL),
(1, 'Plano Premium', 'Acesso completo com benefícios exclusivos', 59.90, 'monthly', 15, NULL),
(1, 'Plano Empresarial', 'Solução completa para empresas', 149.90, 'monthly', 30, 50);

-- Inserir regras de exemplo
INSERT INTO membership_plan_rules (plan_id, rule_type, rule_key, rule_value, description) VALUES
(1, 'usage_limit', 'appointments_per_month', '4', 'Máximo de 4 agendamentos por mês'),
(1, 'time_limit', 'days_valid', '30', 'Validade de 30 dias por ciclo'),
(2, 'usage_limit', 'appointments_per_month', '10', 'Máximo de 10 agendamentos por mês'),
(2, 'feature_access', 'premium_services', 'true', 'Acesso a serviços premium'),
(3, 'usage_limit', 'appointments_per_month', 'unlimited', 'Agendamentos ilimitados'),
(3, 'feature_access', 'all_services', 'true', 'Acesso a todos os serviços'),
(3, 'feature_access', 'priority_scheduling', 'true', 'Agendamento prioritário');