init.sql

-- Smart Data Processing Database Initialization
-- This script creates the initial database structure and sample data

USE smart_data;

-- Create users table if it doesn't exist
CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(120) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    role ENUM('USER', 'ADMIN', 'ANALYST') NOT NULL DEFAULT 'USER',
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create file_metadata table if it doesn't exist
CREATE TABLE IF NOT EXISTS file_metadata (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    original_file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NOT NULL,
    file_size BIGINT,
    mongo_id VARCHAR(255) NOT NULL,
    upload_date TIMESTAMP NOT NULL,
    user_id BIGINT,
    status VARCHAR(50) DEFAULT 'PENDING',
    error_message TEXT,
    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 SET NULL
);

-- Create processed_data table if it doesn't exist
CREATE TABLE IF NOT EXISTS processed_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    numeric_value DOUBLE,
    description TEXT,
    processed_date TIMESTAMP NOT NULL,
    file_id BIGINT,
    source_row TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (file_id) REFERENCES file_metadata(id) ON DELETE SET NULL
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_file_metadata_user_id ON file_metadata(user_id);
CREATE INDEX IF NOT EXISTS idx_file_metadata_status ON file_metadata(status);
CREATE INDEX IF NOT EXISTS idx_file_metadata_upload_date ON file_metadata(upload_date);
CREATE INDEX IF NOT EXISTS idx_processed_data_category ON processed_data(category);
CREATE INDEX IF NOT EXISTS idx_processed_data_value ON processed_data(value);
CREATE INDEX IF NOT EXISTS idx_processed_data_processed_date ON processed_data(processed_date);
CREATE INDEX IF NOT EXISTS idx_processed_data_file_id ON processed_data(file_id);

-- Insert sample admin user (password: admin123)
INSERT INTO users (username, password, email, role) VALUES 
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVEFDa', 'admin@smartdata.com', 'ADMIN')
ON DUPLICATE KEY UPDATE username=username;

-- Insert sample analyst user (password: analyst123)
INSERT INTO users (username, password, email, role) VALUES 
('analyst', '$2a$10$8K1p/a0dL1LXMIgoEDFrwOfgqwAG6JpL6yPm7zJx6x6x6x6x6x6x6', 'analyst@smartdata.com', 'ANALYST')
ON DUPLICATE KEY UPDATE username=username;

-- Insert sample regular user (password: user123)
INSERT INTO users (username, password, email, role) VALUES 
('user', '$2a$10$8K1p/a0dL1LXMIgoEDFrwOfgqwAG6JpL6yPm7zJx6x6x6x6x6x6x6', 'user@smartdata.com', 'USER')
ON DUPLICATE KEY UPDATE username=username;

-- Show created tables
SHOW TABLES;

-- Show sample users
SELECT id, username, email, role, enabled FROM users;