database_setup_microservices.sql

-- Create separate databases for each microservice
CREATE DATABASE auth_db;
CREATE DATABASE document_db;
CREATE DATABASE genai_db;

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE auth_db TO postgres;
GRANT ALL PRIVILEGES ON DATABASE document_db TO postgres;
GRANT ALL PRIVILEGES ON DATABASE genai_db TO postgres;

-- Connect to auth_db and create users table
\c auth_db;

CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(255) PRIMARY KEY,
    username VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'USER',
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP
);

-- Connect to document_db and create documents table
\c document_db;

CREATE TABLE IF NOT EXISTS documents (
    id VARCHAR(255) PRIMARY KEY,
    original_name VARCHAR(255) NOT NULL,
    file_size BIGINT NOT NULL,
    file_type VARCHAR(100),
    file_content TEXT,
    summary TEXT,
    processed_content JSONB,
    quiz_data JSONB,
    flashcard_data JSONB,
    summary_status VARCHAR(20) NOT NULL DEFAULT 'UPLOADED',
    quiz_status VARCHAR(20) NOT NULL DEFAULT 'UPLOADED',
    flashcard_status VARCHAR(20) NOT NULL DEFAULT 'UPLOADED',
    status VARCHAR(20) NOT NULL DEFAULT 'UPLOADED',
    upload_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    page_count INTEGER,
    user_id VARCHAR(255) NOT NULL
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_documents_user_id ON documents(user_id);
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(status);
CREATE INDEX IF NOT EXISTS idx_documents_upload_date ON documents(upload_date);