-- Script para criar tabelas de empresas (company_students e company_sales)
-- Execute este script no phpMyAdmin

-- Tabela company_students (estagiários)
CREATE TABLE IF NOT EXISTS company_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    student_id INT NULL COMMENT 'ID do aluno (opcional - pode ser NULL para estagiários independentes)',
    student_name VARCHAR(255) NULL COMMENT 'Nome do estagiário (para estagiários independentes)',
    funcao VARCHAR(100) DEFAULT NULL,
    periodo VARCHAR(50) DEFAULT NULL,
    inicio DATE DEFAULT NULL,
    fim DATE DEFAULT NULL,
    observacoes TEXT DEFAULT NULL,
    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 (student_id) REFERENCES students(id) ON DELETE SET NULL,
    INDEX idx_company (company_id),
    INDEX idx_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Estagiários vinculados a empresas';

-- Tabela company_sales (vendas de empresas)
CREATE TABLE IF NOT EXISTS company_sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    service_id INT NOT NULL,
    payment_plan_id INT NOT NULL,
    valor_total DECIMAL(15, 2) NOT NULL,
    data_venda DATE NOT NULL,
    observacoes TEXT DEFAULT NULL,
    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 (service_id) REFERENCES services(id) ON DELETE RESTRICT,
    FOREIGN KEY (payment_plan_id) REFERENCES payment_plans(id) ON DELETE RESTRICT,
    INDEX idx_company (company_id),
    INDEX idx_service (service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Vendas realizadas para empresas';

-- Adiciona coluna company_sale_id na tabela receivables (se não existir)
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM (SHOW COLUMNS FROM receivables LIKE 'company_sale_id') AS temp;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE receivables ADD COLUMN company_sale_id INT NULL AFTER student_sale_id, ADD INDEX idx_company_sale (company_sale_id), ADD FOREIGN KEY (company_sale_id) REFERENCES company_sales(id) ON DELETE CASCADE',
    'SELECT ''Coluna company_sale_id já existe'' AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verifica se as tabelas foram criadas
SHOW TABLES LIKE 'company_%';