USE recepcao;

CREATE TABLE IF NOT EXISTS perfis_acesso (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    horarios_permitidos TEXT DEFAULT NULL,
    descricao VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    tipo ENUM('catraca', 'porta', 'cancela') NOT NULL,
    ip_address VARCHAR(50) DEFAULT NULL,
    status ENUM('online', 'offline', 'manutencao') DEFAULT 'online',
    localizacao VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS profile_device_permissions (
    perfil_acesso_id INT NOT NULL,
    device_id INT NOT NULL,
    horario_inicio TIME NOT NULL DEFAULT '00:00:00',
    horario_fim TIME NOT NULL DEFAULT '23:59:59',
    PRIMARY KEY (perfil_acesso_id, device_id),
    FOREIGN KEY (perfil_acesso_id) REFERENCES perfis_acesso(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;