-- Criação das tabelas para o sistema de RH

-- Tabela de colaboradores
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    cpf VARCHAR(14) UNIQUE NOT NULL,
    rg VARCHAR(20) NOT NULL,
    birth_date DATE NOT NULL,
    address TEXT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    job_title VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    contract_type ENUM('CLT', 'PJ', 'Estagiário', 'Terceirizado') NOT NULL DEFAULT 'CLT',
    cost_center VARCHAR(100) NULL,
    bank_name VARCHAR(100) NOT NULL,
    agency_number VARCHAR(20) NOT NULL,
    account_number VARCHAR(30) NOT NULL,
    status ENUM('ativo', 'inativo', 'demitido') NOT NULL DEFAULT 'ativo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de registros de ponto
CREATE TABLE time_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    work_date DATE NOT NULL,
    entry_time TIME NOT NULL,
    exit_time TIME NULL,
    total_hours DECIMAL(4,2) NULL,
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_employee_date (employee_id, work_date)
);

-- Tabela de ausências
CREATE TABLE absences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    absence_type ENUM('férias', 'licença médica', 'atestado', 'falta justificada', 'falta injustificada') NOT NULL,
    reason TEXT NULL,
    days_count INT NOT NULL,
    approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Tabela de folhas de pagamento
CREATE TABLE payrolls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    reference_month INT NOT NULL,
    reference_year INT NOT NULL,
    base_salary DECIMAL(10,2) NOT NULL,
    overtime_amount DECIMAL(10,2) DEFAULT 0,
    total_earnings DECIMAL(10,2) NOT NULL,
    inss_discount DECIMAL(10,2) DEFAULT 0,
    irrf_discount DECIMAL(10,2) DEFAULT 0,
    transport_discount DECIMAL(10,2) DEFAULT 0,
    meal_discount DECIMAL(10,2) DEFAULT 0,
    absence_discount DECIMAL(10,2) DEFAULT 0,
    total_discounts DECIMAL(10,2) NOT NULL,
    net_salary DECIMAL(10,2) NOT NULL,
    worked_days INT NOT NULL,
    absence_days INT DEFAULT 0,
    overtime_hours DECIMAL(4,2) DEFAULT 0,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY unique_employee_period (employee_id, reference_month, reference_year)
);

-- Tabela de documentos dos colaboradores
CREATE TABLE employee_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Índices para melhor performance
CREATE INDEX idx_employees_status ON employees(status);
CREATE INDEX idx_employees_cpf ON employees(cpf);
CREATE INDEX idx_time_records_date ON time_records(work_date);
CREATE INDEX idx_absences_dates ON absences(start_date, end_date);
CREATE INDEX idx_payrolls_period ON payrolls(reference_year, reference_month);