-- Migração 002: Tabelas do módulo de agendamentos
USE saas_barbearia;

-- Tabela de profissionais (funcionários que prestam serviços)
CREATE TABLE professionals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NOT NULL, -- Link com usuário do sistema
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    bio TEXT,
    avatar VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    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 (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_company_user (company_id, user_id)
);

-- Tabela de horários de trabalho dos profissionais
CREATE TABLE schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    professional_id INT NOT NULL,
    day_of_week TINYINT NOT NULL, -- 0=Domingo, 1=Segunda, ..., 6=Sábado
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_professional_day (professional_id, day_of_week)
);

-- Tabela de horários bloqueados (feriados, férias, etc.)
CREATE TABLE blocked_times (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    professional_id INT NULL, -- NULL = bloqueio para todos os profissionais
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_type ENUM('daily', 'weekly', 'monthly', 'yearly') NULL,
    recurrence_end_date DATE NULL,
    created_by INT NOT 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 (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabela de relacionamento profissional-serviço (muitos-para-muitos)
CREATE TABLE professional_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    professional_id INT NOT NULL,
    service_id INT NOT NULL,
    custom_price DECIMAL(10,2) NULL, -- Preço customizado para este profissional
    custom_duration INT NULL, -- Duração customizada em minutos
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    UNIQUE KEY unique_professional_service (professional_id, service_id)
);

-- Tabela de agendamentos (atualizada)
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    client_id INT NOT NULL, -- Cliente que fez o agendamento
    professional_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    total_duration INT NOT NULL, -- Duração total em minutos
    total_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show') DEFAULT 'pending',
    notes TEXT,
    client_notes TEXT, -- Observações do cliente
    created_by INT NOT NULL, -- Quem criou o agendamento
    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 (client_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_company_date (company_id, appointment_date),
    INDEX idx_professional_date (professional_id, appointment_date),
    INDEX idx_client_date (client_id, appointment_date),
    INDEX idx_status (status)
);

-- Tabela de serviços do agendamento (muitos-para-muitos)
CREATE TABLE appointment_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_id INT NOT NULL,
    service_id INT NOT NULL,
    professional_id INT NOT NULL, -- Profissional específico para este serviço
    price DECIMAL(10,2) NOT NULL, -- Preço no momento do agendamento
    duration INT NOT NULL, -- Duração no momento do agendamento
    order_position INT NOT NULL, -- Ordem dos serviços
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    FOREIGN KEY (professional_id) REFERENCES professionals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_appointment_service (appointment_id, service_id, order_position)
);

-- Tabela de logs de status dos agendamentos
CREATE TABLE appointment_status_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_id INT NOT NULL,
    old_status ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show'),
    new_status ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show') NOT NULL,
    changed_by INT NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_appointment_status (appointment_id, new_status)
);

-- Inserir permissões para o módulo de agendamentos
INSERT INTO permissions (name, display_name, description, module) VALUES
('appointments.view', 'Visualizar Agendamentos', 'Permite visualizar agendamentos', 'appointments'),
('appointments.create', 'Criar Agendamentos', 'Permite criar novos agendamentos', 'appointments'),
('appointments.edit', 'Editar Agendamentos', 'Permite editar agendamentos existentes', 'appointments'),
('appointments.cancel', 'Cancelar Agendamentos', 'Permite cancelar agendamentos', 'appointments'),
('appointments.complete', 'Completar Agendamentos', 'Permite marcar agendamentos como concluídos', 'appointments'),
('professionals.view', 'Visualizar Profissionais', 'Permite visualizar profissionais', 'professionals'),
('professionals.manage', 'Gerenciar Profissionais', 'Permite criar/editar profissionais', 'professionals'),
('services.view', 'Visualizar Serviços', 'Permite visualizar serviços', 'services'),
('services.manage', 'Gerenciar Serviços', 'Permite criar/editar serviços', 'services'),
('schedules.view', 'Visualizar Horários', 'Permite visualizar horários de trabalho', 'schedules'),
('schedules.manage', 'Gerenciar Horários', 'Permite gerenciar horários de trabalho', 'schedules'),
('blocked_times.view', 'Visualizar Bloqueios', 'Permite visualizar horários bloqueados', 'blocked_times'),
('blocked_times.manage', 'Gerenciar Bloqueios', 'Permite criar/editar bloqueios de horário', 'blocked_times');