-- Migração 006: Normalização do Schema e Índices de Performance
-- Consolidação de correções, índices e melhorias de segurança

USE saas_barbearia;

-- ============================================================================
-- ÍNDICES DE PERFORMANCE (faltantes)
-- ============================================================================

-- Índices para queries de tenant
CREATE INDEX IF NOT EXISTS idx_users_company_id ON users(company_id);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_email_company ON users(email, company_id);

-- Índices para agendamentos
CREATE INDEX IF NOT EXISTS idx_appointments_company_date ON appointments(company_id, appointment_date);
CREATE INDEX IF NOT EXISTS idx_appointments_professional_date ON appointments(professional_id, appointment_date);
CREATE INDEX IF NOT EXISTS idx_appointments_client_date ON appointments(client_id, appointment_date);
CREATE INDEX IF NOT EXISTS idx_appointments_status ON appointments(status);

-- Índices para transações financeiras
CREATE INDEX IF NOT EXISTS idx_financial_transactions_company_id ON financial_transactions(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_transactions_type ON financial_transactions(transaction_type);
CREATE INDEX IF NOT EXISTS idx_financial_transactions_date ON financial_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_financial_transactions_company_type_date ON financial_transactions(company_id, transaction_type, transaction_date);

-- Índices para pedidos
CREATE INDEX IF NOT EXISTS idx_orders_company_id ON orders(company_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

-- Índices para memberships
CREATE INDEX IF NOT EXISTS idx_memberships_company_client ON memberships(company_id, client_id);
CREATE INDEX IF NOT EXISTS idx_memberships_status ON memberships(status);
CREATE INDEX IF NOT EXISTS idx_memberships_current_period ON memberships(status, current_period_end);

-- Índices para blocos de tempo
CREATE INDEX IF NOT EXISTS idx_blocked_times_company_id ON blocked_times(company_id);
CREATE INDEX IF NOT EXISTS idx_blocked_times_professional_id ON blocked_times(professional_id);
CREATE INDEX IF NOT EXISTS idx_blocked_times_period ON blocked_times(start_datetime, end_datetime);

-- Índices para Celcoin
CREATE INDEX IF NOT EXISTS idx_celcoin_charges_company_id ON celcoin_charges(company_id);
CREATE INDEX IF NOT EXISTS idx_celcoin_subscriptions_company_id ON celcoin_subscriptions(company_id);
CREATE INDEX IF NOT EXISTS idx_celcoin_webhook_logs_company_id ON celcoin_webhook_logs(company_id);

-- Índices para contas
CREATE INDEX IF NOT EXISTS idx_accounts_receivable_company_id ON accounts_receivable(company_id);
CREATE INDEX IF NOT EXISTS idx_accounts_payable_company_id ON accounts_payable(company_id);

-- ============================================================================
-- ADIÇÃO DE CAMPOS FALTANTES
-- ============================================================================

-- Adicionar plan_type e trial_expires_at em companies (para SaaS)
ALTER TABLE companies ADD COLUMN IF NOT EXISTS plan_type ENUM('trial', 'basic', 'professional', 'enterprise') DEFAULT 'trial' AFTER status;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS trial_expires_at TIMESTAMP NULL AFTER plan_type;

-- Adicionar company_id em cash_register se não existir (redundante mas para segurança)
ALTER TABLE cash_registers ADD COLUMN IF NOT EXISTS company_id INT AFTER id;

-- ============================================================================
-- CHECKS DE INTEGRIDADE DE DADOS (apenas para auditoria)
-- ============================================================================

-- Verificar se há usuarios sem empresa
SELECT COUNT(*) as usuarios_sem_empresa FROM users WHERE company_id IS NULL;

-- Verificar se há agendamentos sem empresa
SELECT COUNT(*) as agendamentos_sem_empresa FROM appointments WHERE company_id IS NULL;

-- Verificar se há memberships sem empresa
SELECT COUNT(*) as memberships_sem_empresa FROM memberships WHERE company_id IS NULL;