uploader-bot/init_db.sql

-- MY Network v3.0 Database Initialization

-- Extension for UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create enum types
DO $$ BEGIN
    CREATE TYPE content_status AS ENUM ('pending', 'processing', 'completed', 'failed');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Create stored_content table (compatible with DEPRECATED-uploader-bot)
CREATE TABLE IF NOT EXISTS stored_content (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hash VARCHAR(255) UNIQUE NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    file_size BIGINT NOT NULL,
    content_type VARCHAR(255),
    storage_path TEXT NOT NULL,
    decrypted_path TEXT,
    encrypted_path TEXT NOT NULL,
    thumbnail_path TEXT,
    converted_formats JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    encryption_key TEXT NOT NULL,
    upload_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    access_count INTEGER DEFAULT 0,
    status content_status DEFAULT 'pending',
    uploader_id VARCHAR(255),
    tags TEXT[],
    description TEXT,
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_stored_content_hash ON stored_content(hash);
CREATE INDEX IF NOT EXISTS idx_stored_content_status ON stored_content(status);
CREATE INDEX IF NOT EXISTS idx_stored_content_upload_date ON stored_content(upload_date);
CREATE INDEX IF NOT EXISTS idx_stored_content_uploader_id ON stored_content(uploader_id);
CREATE INDEX IF NOT EXISTS idx_stored_content_file_type ON stored_content(file_type);

-- Create nodes table for network management
CREATE TABLE IF NOT EXISTS nodes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    node_id VARCHAR(255) UNIQUE NOT NULL,
    address INET NOT NULL,
    port INTEGER NOT NULL,
    public_key TEXT,
    node_type VARCHAR(50) NOT NULL,
    version VARCHAR(20) NOT NULL,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    trust_score DECIMAL(3,2) DEFAULT 1.0,
    is_active BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for nodes
CREATE INDEX IF NOT EXISTS idx_nodes_node_id ON nodes(node_id);
CREATE INDEX IF NOT EXISTS idx_nodes_address ON nodes(address);
CREATE INDEX IF NOT EXISTS idx_nodes_is_active ON nodes(is_active);
CREATE INDEX IF NOT EXISTS idx_nodes_last_seen ON nodes(last_seen);

-- Create content_sync table for decentralized synchronization
CREATE TABLE IF NOT EXISTS content_sync (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    content_hash VARCHAR(255) NOT NULL,
    node_id VARCHAR(255) NOT NULL,
    sync_status VARCHAR(50) DEFAULT 'pending',
    attempts INTEGER DEFAULT 0,
    last_attempt TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for content_sync
CREATE INDEX IF NOT EXISTS idx_content_sync_hash ON content_sync(content_hash);
CREATE INDEX IF NOT EXISTS idx_content_sync_node_id ON content_sync(node_id);
CREATE INDEX IF NOT EXISTS idx_content_sync_status ON content_sync(sync_status);

-- Create conversion_jobs table
CREATE TABLE IF NOT EXISTS conversion_jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    content_id UUID REFERENCES stored_content(id),
    target_format VARCHAR(50) NOT NULL,
    status content_status DEFAULT 'pending',
    priority INTEGER DEFAULT 5,
    attempts INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 3,
    error_message TEXT,
    conversion_params JSONB DEFAULT '{}',
    output_path TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE
);

-- Create indexes for conversion_jobs
CREATE INDEX IF NOT EXISTS idx_conversion_jobs_content_id ON conversion_jobs(content_id);
CREATE INDEX IF NOT EXISTS idx_conversion_jobs_status ON conversion_jobs(status);
CREATE INDEX IF NOT EXISTS idx_conversion_jobs_priority ON conversion_jobs(priority);

-- Update trigger for updated_at columns
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply update triggers
DROP TRIGGER IF EXISTS update_stored_content_updated_at ON stored_content;
CREATE TRIGGER update_stored_content_updated_at
    BEFORE UPDATE ON stored_content
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_nodes_updated_at ON nodes;
CREATE TRIGGER update_nodes_updated_at
    BEFORE UPDATE ON nodes
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_content_sync_updated_at ON content_sync;
CREATE TRIGGER update_content_sync_updated_at
    BEFORE UPDATE ON content_sync
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_conversion_jobs_updated_at ON conversion_jobs;
CREATE TRIGGER update_conversion_jobs_updated_at
    BEFORE UPDATE ON conversion_jobs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();