-- Criar banco de dados
CREATE DATABASE IF NOT EXISTS sistema_reservas;
USE sistema_reservas;

-- Tabela de Usuários Administrativos
CREATE TABLE IF NOT EXISTS admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Clientes
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Serviços
CREATE TABLE IF NOT EXISTS services (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    duration INT NOT NULL COMMENT 'Duração em minutos',
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Reservas
CREATE TABLE IF NOT EXISTS reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    service_id INT NOT NULL,
    reservation_date DATE NOT NULL,
    reservation_time TIME NOT NULL,
    status ENUM('pendente', 'confirmada', 'cancelada', 'concluida') DEFAULT 'pendente',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_reservation (reservation_date, reservation_time, service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Disponibilidade
CREATE TABLE IF NOT EXISTS availability (
    id INT PRIMARY KEY AUTO_INCREMENT,
    day_of_week INT NOT NULL COMMENT '0=Domingo, 1=Segunda, ..., 6=Sábado',
    opening_time TIME NOT NULL,
    closing_time TIME NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_day (day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Dias Fechados (Feriados, Férias, etc)
CREATE TABLE IF NOT EXISTS closed_dates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    closed_date DATE NOT NULL,
    reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_closed_date (closed_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Confirmação de E-mail
CREATE TABLE IF NOT EXISTS email_confirmations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservation_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    is_confirmed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserir horários padrão de funcionamento
INSERT INTO availability (day_of_week, opening_time, closing_time, is_available) VALUES
(0, '08:00:00', '18:00:00', FALSE),  -- Domingo fechado
(1, '08:00:00', '18:00:00', TRUE),   -- Segunda
(2, '08:00:00', '18:00:00', TRUE),   -- Terça
(3, '08:00:00', '18:00:00', TRUE),   -- Quarta
(4, '08:00:00', '18:00:00', TRUE),   -- Quinta
(5, '08:00:00', '18:00:00', TRUE),   -- Sexta
(6, '08:00:00', '14:00:00', TRUE);   -- Sábado

-- Inserir serviços padrão
INSERT INTO services (name, description, duration, price, is_active) VALUES
('Consulta Geral', 'Consulta médica geral', 30, 150.00, TRUE),
('Consulta Especializada', 'Consulta com especialista', 45, 250.00, TRUE),
('Procedimento Simples', 'Procedimento médico simples', 60, 300.00, TRUE);

-- Inserir usuário administrativo padrão (senha: admin123)
INSERT INTO admin_users (username, email, password, name, is_active) VALUES
('admin', 'admin@sistemareservas.com', '$2y$12$abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 'Administrador', TRUE);

-- Criar índices para melhorar performance
CREATE INDEX idx_reservation_date ON reservations(reservation_date);
CREATE INDEX idx_reservation_status ON reservations(status);
CREATE INDEX idx_customer_email ON customers(email);
CREATE INDEX idx_closed_date ON closed_dates(closed_date);