-- Criação do banco de dados
CREATE DATABASE gerador_stack;

\c gerador_stack;

-- Tabela de usuários
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    senha VARCHAR(255) NOT NULL,
    tipo VARCHAR(10) NOT NULL CHECK (tipo IN ('admin', 'aluno')),
    data_validade DATE,
    status BOOLEAN DEFAULT true,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ultimo_acesso TIMESTAMP,
    configuracoes_json JSONB DEFAULT '{}'::jsonb
);

-- Tabela de cursos
CREATE TABLE cursos (
    id SERIAL PRIMARY KEY,
    titulo VARCHAR(100) NOT NULL,
    descricao TEXT,
    imagem_url VARCHAR(255),
    status BOOLEAN DEFAULT true,
    nivel_dificuldade VARCHAR(20) CHECK (nivel_dificuldade IN ('iniciante', 'intermediario', 'avancado')),
    ordem_exibicao INTEGER,
    tags TEXT[],
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de stacks
CREATE TABLE stacks (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    descricao TEXT,
    compose_yml TEXT NOT NULL,
    variaveis JSONB DEFAULT '{}'::jsonb,
    servicos JSONB DEFAULT '{}'::jsonb,
    volumes JSONB DEFAULT '{}'::jsonb,
    networks JSONB DEFAULT '{}'::jsonb,
    status BOOLEAN DEFAULT true,
    tags JSONB DEFAULT '{}'::jsonb,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de relacionamento cursos_stacks
CREATE TABLE cursos_stacks (
    id SERIAL PRIMARY KEY,
    curso_id INTEGER REFERENCES cursos(id) ON DELETE CASCADE,
    stack_id INTEGER REFERENCES stacks(id) ON DELETE CASCADE,
    data_associacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de relacionamento alunos_cursos
CREATE TABLE alunos_cursos (
    aluno_id INTEGER REFERENCES usuarios(id) ON DELETE CASCADE,
    curso_id INTEGER REFERENCES cursos(id) ON DELETE CASCADE,
    data_acesso TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status BOOLEAN DEFAULT true,
    PRIMARY KEY (aluno_id, curso_id)
);

-- Tabela de stacks geradas
CREATE TABLE IF NOT EXISTS stacks_geradas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    stack_id INT NOT NULL,
    nome VARCHAR(255) NOT NULL,
    compose_yml TEXT NOT NULL,
    variaveis JSON,
    status ENUM('pendente', 'processando', 'ativo', 'erro', 'expirado') DEFAULT 'pendente',
    favorito BOOLEAN DEFAULT FALSE,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (stack_id) REFERENCES stacks(id) ON DELETE CASCADE
);

-- Tabela de notificações
CREATE TABLE notificacoes (
    id SERIAL PRIMARY KEY,
    aluno_id INTEGER NOT NULL REFERENCES usuarios(id),
    tipo VARCHAR(50) NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    mensagem TEXT NOT NULL,
    link VARCHAR(255),
    lida BOOLEAN DEFAULT false,
    data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data_leitura TIMESTAMP
);

-- Tabela de logs do sistema
CREATE TABLE logs_sistema (
    id SERIAL PRIMARY KEY,
    usuario_id INTEGER REFERENCES usuarios(id) ON DELETE SET NULL,
    acao VARCHAR(50) NOT NULL,
    detalhes JSONB NOT NULL DEFAULT '{}'::jsonb,
    data_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de guias de instalação
CREATE TABLE IF NOT EXISTS guias_instalacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT,
    ordem INT NOT NULL DEFAULT 0,
    status BOOLEAN DEFAULT TRUE,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de passos do guia
CREATE TABLE IF NOT EXISTS passos_instalacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    guia_id INT NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT,
    comando TEXT,
    validacao TEXT,
    ordem INT NOT NULL DEFAULT 0,
    status BOOLEAN DEFAULT TRUE,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (guia_id) REFERENCES guias_instalacao(id) ON DELETE CASCADE
);

-- Tabela de progresso do aluno
CREATE TABLE IF NOT EXISTS progresso_instalacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    passo_id INT NOT NULL,
    status ENUM('pendente', 'concluido', 'erro') DEFAULT 'pendente',
    log TEXT,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (passo_id) REFERENCES passos_instalacao(id) ON DELETE CASCADE,
    UNIQUE KEY unique_progresso (usuario_id, passo_id)
);

-- Tabela de versões do instalador
CREATE TABLE IF NOT EXISTS instalador_versoes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    versao VARCHAR(50) NOT NULL,
    url_download VARCHAR(255) NOT NULL,
    changelog TEXT,
    hash_arquivo VARCHAR(64) NOT NULL,
    obrigatorio BOOLEAN DEFAULT FALSE,
    data_lancamento TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_versao (versao)
);

-- Tabela de instalações
CREATE TABLE IF NOT EXISTS instalacoes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    versao_id INT NOT NULL,
    status ENUM('pendente', 'baixando', 'instalando', 'concluido', 'erro') DEFAULT 'pendente',
    log TEXT,
    data_inicio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_conclusao TIMESTAMP NULL,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (versao_id) REFERENCES instalador_versoes(id) ON DELETE CASCADE
);

-- Índices para otimização
CREATE INDEX idx_usuarios_email ON usuarios(email);
CREATE INDEX idx_usuarios_tipo ON usuarios(tipo);
CREATE INDEX idx_cursos_status ON cursos(status);
CREATE INDEX idx_stacks_status ON stacks(status);
CREATE INDEX idx_stacks_geradas_aluno ON stacks_geradas(usuario_id);
CREATE INDEX idx_logs_usuario ON logs_sistema(usuario_id);
CREATE INDEX idx_logs_data ON logs_sistema(data_hora);
CREATE INDEX idx_guias_status ON guias_instalacao(status);
CREATE INDEX idx_passos_guia ON passos_instalacao(guia_id);
CREATE INDEX idx_passos_ordem ON passos_instalacao(ordem);
CREATE INDEX idx_progresso_usuario ON progresso_instalacao(usuario_id);
CREATE INDEX idx_progresso_status ON progresso_instalacao(status);
CREATE INDEX idx_versoes_data ON instalador_versoes(data_lancamento);
CREATE INDEX idx_instalacoes_usuario ON instalacoes(usuario_id);
CREATE INDEX idx_instalacoes_status ON instalacoes(status);

-- Trigger para atualização automática de data_atualizacao
CREATE OR REPLACE FUNCTION update_data_atualizacao()
RETURNS TRIGGER AS $$
BEGIN
    NEW.data_atualizacao = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_cursos_data_atualizacao
    BEFORE UPDATE ON cursos
    FOR EACH ROW
    EXECUTE FUNCTION update_data_atualizacao();

CREATE TRIGGER update_stacks_data_atualizacao
    BEFORE UPDATE ON stacks
    FOR EACH ROW
    EXECUTE FUNCTION update_data_atualizacao();
