

-- Tabela de Franquias
CREATE TABLE IF NOT EXISTS franquias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cnpj VARCHAR(18) UNIQUE,
    endereco TEXT,
    telefone VARCHAR(20),
    email VARCHAR(100),
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de Relação Usuário-Franquia
CREATE TABLE IF NOT EXISTS usuario_franquia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    franquia_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (franquia_id) REFERENCES franquias(id) ON DELETE CASCADE,
    UNIQUE KEY (usuario_id, franquia_id)
);

-- Tabela de Arquivos
CREATE TABLE IF NOT EXISTS arquivos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    categoria ENUM('documento', 'imagem', 'video') NOT NULL,
    caminho VARCHAR(255) NOT NULL,
    tamanho INT NOT NULL,
    visibilidade ENUM('publico', 'privado') DEFAULT 'privado',
    usuario_id INT NOT NULL,
    franquia_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (franquia_id) REFERENCES franquias(id) ON DELETE SET NULL
);

-- Tabela de Pastas
CREATE TABLE IF NOT EXISTS pastas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    pasta_pai_id INT,
    usuario_id INT NOT NULL,
    franquia_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pasta_pai_id) REFERENCES pastas(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (franquia_id) REFERENCES franquias(id) ON DELETE CASCADE
);

-- Tabela de Relação Arquivo-Pasta
CREATE TABLE IF NOT EXISTS arquivo_pasta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    arquivo_id INT NOT NULL,
    pasta_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (arquivo_id) REFERENCES arquivos(id) ON DELETE CASCADE,
    FOREIGN KEY (pasta_id) REFERENCES pastas(id) ON DELETE CASCADE,
    UNIQUE KEY (arquivo_id, pasta_id)
);

-- Tabela de Notificações
CREATE TABLE IF NOT EXISTS notificacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(100) NOT NULL,
    mensagem TEXT NOT NULL,
    tipo ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    lida TINYINT(1) DEFAULT 0,
    para_todos TINYINT(1) DEFAULT 0,
    usuario_id INT,
    franquia_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (franquia_id) REFERENCES franquias(id) ON DELETE CASCADE
);

-- Tabela de Eventos
CREATE TABLE IF NOT EXISTS eventos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(100) NOT NULL,
    descricao TEXT,
    data_inicio DATETIME NOT NULL,
    data_fim DATETIME NOT NULL,
    local VARCHAR(255),
    para_todos TINYINT(1) DEFAULT 0,
    usuario_id INT NOT NULL,
    franquia_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (franquia_id) REFERENCES franquias(id) ON DELETE CASCADE
);
-- Senha: admin123 