-- Database for International Alternative Proteins Event
-- UFMG - October 2026

CREATE DATABASE IF NOT EXISTS wwagen_protein_event_db;
USE wwagen_protein_event_db;

-- User profiles table
CREATE TABLE user_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    bio TEXT,
    profile_picture VARCHAR(255),
    website VARCHAR(255),
    linkedin VARCHAR(255),
    twitter VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Abstract categories table
CREATE TABLE abstract_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Abstracts table
CREATE TABLE abstracts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(500) NOT NULL,
    authors TEXT NOT NULL,
    abstract_text TEXT NOT NULL,
    keywords VARCHAR(500),
    presentation_type ENUM('oral', 'poster') DEFAULT 'poster',
    status ENUM('pending', 'approved', 'approved_with_reservations', 'rejected') DEFAULT 'pending',
    committee_feedback TEXT,
    file_path VARCHAR(255),
    institution VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES abstract_categories(id) ON DELETE CASCADE
);

-- Registrations table
CREATE TABLE registrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    registration_type ENUM('early_bird', 'regular', 'student') DEFAULT 'regular',
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'BRL',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    stripe_payment_intent_id VARCHAR(255),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Certificates table
CREATE TABLE certificates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    registration_id BIGINT UNSIGNED NOT NULL,
    certificate_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    hours INT DEFAULT 20,
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    file_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE CASCADE
);

-- Event settings table
CREATE TABLE event_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Notifications table
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Activity logs table
CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Abstract categories
INSERT INTO abstract_categories (name, description) VALUES
('Proteínas Vegetais', 'Pesquisas sobre proteínas derivadas de plantas'),
('Proteínas de Insetos', 'Estudos sobre proteínas de insetos como fonte alimentar'),
('Proteínas de Algas', 'Pesquisas sobre proteínas de micro e macroalgas'),
('Proteínas Cultivadas', 'Estudos sobre carne cultivada e proteínas celulares'),
('Proteínas Fúngicas', 'Pesquisas sobre proteínas de fungos e leveduras'),
('Novas Fontes Proteicas', 'Descobertas de novas fontes de Proteínas Alternativas'),
('Tecnologia e Processamento', 'Tecnologias de processamento e extração de proteínas'),
('Nutrição e Saúde', 'Aspectos nutricionais e de saúde das Proteínas Alternativas'),
('Sustentabilidade', 'Impacto ambiental e sustentabilidade das Proteínas Alternativas'),
('Regulamentação e Segurança', 'Aspectos regulatórios e de segurança alimentar');

-- Event settings
INSERT INTO event_settings (setting_key, setting_value, description) VALUES
('event_name', 'Evento Internacional de Proteínas Alternativas', 'Nome do evento'),
('event_date', '2026-10-15', 'Data do evento'),
('event_location', 'UFMG - Belo Horizonte, Brasil', 'Local do evento'),
('registration_price', '50.00', 'Preço da inscrição em reais'),
('abstract_submission_deadline', '2026-08-31', 'Prazo para submissão de resumos'),
('early_bird_deadline', '2026-06-30', 'Prazo para inscrição com desconto'),
('max_abstracts_per_user', '3', 'Máximo de resumos por participante'),
('certificate_hours', '20', 'Horas do certificado de participação');
CREATE INDEX idx_abstracts_status ON abstracts(status);
CREATE INDEX idx_abstracts_category ON abstracts(category_id);
CREATE INDEX idx_registrations_payment_status ON registrations(payment_status);
CREATE INDEX idx_certificates_user ON certificates(user_id);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, read_at); 