fileflash_schema_clean.sql

-- FileFlash Complete Database Schema
-- This file sets up the entire database structure for the FileFlash application

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    storage_quota BIGINT DEFAULT 10737418240, -- 10GB default
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create files table for logical storage
CREATE TABLE IF NOT EXISTS files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    path TEXT NOT NULL,
    parent_id UUID REFERENCES files(id) ON DELETE CASCADE,
    owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    is_folder BOOLEAN DEFAULT false,
    file_size BIGINT DEFAULT 0,
    mime_type VARCHAR(100),
    checksum VARCHAR(64),
    physical_file_id UUID,
    used_storage BIGINT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(path, owner_id)
);

-- Create physical_storage table for tracking actual file locations
CREATE TABLE IF NOT EXISTS physical_storage (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    storage_path TEXT NOT NULL,
    disk_size BIGINT NOT NULL,
    storage_backend VARCHAR(20) DEFAULT 'local',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Physical files table (for deduplication)
CREATE TABLE IF NOT EXISTS physical_files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hash VARCHAR(64) UNIQUE NOT NULL, -- SHA-256
    file_size BIGINT NOT NULL,
    chunk_count INTEGER NOT NULL,
    ref_count INTEGER DEFAULT 1, -- Reference count for deduplication
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Chunks storage table
CREATE TABLE IF NOT EXISTS chunks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    physical_file_id UUID REFERENCES physical_files(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    chunk_size BIGINT NOT NULL,
    chunk_hash VARCHAR(64) NOT NULL,
    storage_path TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Upload sessions table (for resumable uploads)
CREATE TABLE IF NOT EXISTS upload_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    file_name VARCHAR(255) NOT NULL,
    file_size BIGINT NOT NULL,
    file_hash VARCHAR(64),
    chunk_size INTEGER NOT NULL,
    uploaded_chunks INTEGER DEFAULT 0,
    parent_id UUID REFERENCES files(id) ON DELETE SET NULL,
    status VARCHAR(20) DEFAULT 'pending', -- pending, uploading, completed, failed
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '24 hours')
);

-- Permissions table
CREATE TABLE IF NOT EXISTS permissions (
    id SERIAL PRIMARY KEY,
    resource_id UUID NOT NULL, -- file/folder ID
    resource_type VARCHAR(10) NOT NULL, -- 'file' or 'folder'
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    permission_type VARCHAR(20) NOT NULL, -- 'read', 'write', 'share', 'admin'
    granted_by INTEGER REFERENCES users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE
);

-- Sharing links table
CREATE TABLE IF NOT EXISTS sharing_links (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    resource_id UUID NOT NULL,
    token VARCHAR(32) UNIQUE NOT NULL,
    created_by INTEGER REFERENCES users(id) ON DELETE CASCADE,
    permission_type VARCHAR(20) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE,
    is_password_protected BOOLEAN DEFAULT false,
    password_hash VARCHAR(255),
    access_count INTEGER DEFAULT 0,
    max_accesses INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Collaboration spaces (workspaces)
CREATE TABLE IF NOT EXISTS workspaces (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Workspace members
CREATE TABLE IF NOT EXISTS workspace_members (
    workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL, -- 'owner', 'admin', 'editor', 'viewer'
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (workspace_id, user_id)
);

-- Preview cache table
CREATE TABLE IF NOT EXISTS preview_cache (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    preview_type VARCHAR(20) NOT NULL, -- 'thumbnail', 'pdf', 'video', 'audio'
    preview_path TEXT NOT NULL,
    preview_size BIGINT,
    width INTEGER,
    height INTEGER,
    duration INTEGER, -- for video/audio
    quality VARCHAR(10), -- 'low', 'medium', 'high'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '30 days'),
    access_count INTEGER DEFAULT 0,
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Preview generation jobs
CREATE TABLE IF NOT EXISTS preview_jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    file_id UUID REFERENCES files(id) ON DELETE CASCADE,
    job_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending', -- pending, processing, completed, failed
    progress INTEGER DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    retry_count INTEGER DEFAULT 0
);

-- Extraction sessions
CREATE TABLE IF NOT EXISTS extraction_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    archive_id UUID REFERENCES files(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    session_token VARCHAR(32) UNIQUE NOT NULL,
    extracted_path TEXT,
    file_count INTEGER DEFAULT 0,
    total_size BIGINT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    password_hash VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '1 hour'),
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Extracted files registry
CREATE TABLE IF NOT EXISTS extracted_files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID REFERENCES extraction_sessions(id) ON DELETE CASCADE,
    original_path TEXT NOT NULL,
    extracted_path TEXT NOT NULL,
    file_size BIGINT,
    is_directory BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Deleted files table (recycle bin)
CREATE TABLE IF NOT EXISTS deleted_files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    original_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    path TEXT NOT NULL,
    parent_id UUID REFERENCES files(id) ON DELETE SET NULL,
    owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    is_folder BOOLEAN DEFAULT false,
    file_size BIGINT DEFAULT 0,
    mime_type VARCHAR(100),
    checksum VARCHAR(64),
    physical_file_id UUID REFERENCES physical_files(id) ON DELETE SET NULL,
    used_storage BIGINT DEFAULT 0,
    deleted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '30 days'),
    permanent_delete_at TIMESTAMP WITH TIME ZONE DEFAULT (CURRENT_TIMESTAMP + INTERVAL '37 days'),
    deleted_by INTEGER REFERENCES users(id),
    original_path TEXT NOT NULL,
    metadata JSONB
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_files_parent ON files(parent_id);
CREATE INDEX IF NOT EXISTS idx_files_owner ON files(owner_id);
CREATE INDEX IF NOT EXISTS idx_files_path ON files(path);
CREATE INDEX IF NOT EXISTS idx_physical_storage_file ON physical_storage(file_id);
CREATE INDEX IF NOT EXISTS idx_physical_files_hash ON physical_files(hash);
CREATE INDEX IF NOT EXISTS idx_chunks_physical_file ON chunks(physical_file_id);
CREATE INDEX IF NOT EXISTS idx_chunks_hash ON chunks(chunk_hash);
CREATE INDEX IF NOT EXISTS idx_permissions_resource ON permissions(resource_id);
CREATE INDEX IF NOT EXISTS idx_permissions_user ON permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_sharing_links_token ON sharing_links(token);
CREATE INDEX IF NOT EXISTS idx_sharing_links_resource ON sharing_links(resource_id);
CREATE INDEX IF NOT EXISTS idx_upload_sessions_user ON upload_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_upload_sessions_status ON upload_sessions(status);
CREATE INDEX IF NOT EXISTS idx_workspace_members_workspace ON workspace_members(workspace_id);
CREATE INDEX IF NOT EXISTS idx_workspace_members_user ON workspace_members(user_id);
CREATE INDEX IF NOT EXISTS idx_preview_cache_file ON preview_cache(file_id);
CREATE INDEX IF NOT EXISTS idx_preview_cache_type ON preview_cache(preview_type);
CREATE INDEX IF NOT EXISTS idx_preview_jobs_file ON preview_jobs(file_id);
CREATE INDEX IF NOT EXISTS idx_preview_jobs_status ON preview_jobs(status);
CREATE INDEX IF NOT EXISTS idx_extraction_sessions_token ON extraction_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_extraction_sessions_user ON extraction_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_extracted_files_session ON extracted_files(session_id);
CREATE INDEX IF NOT EXISTS idx_deleted_files_owner ON deleted_files(owner_id);
CREATE INDEX IF NOT EXISTS idx_deleted_files_expires_at ON deleted_files(expires_at);
CREATE INDEX IF NOT EXISTS idx_deleted_files_permanent_delete_at ON deleted_files(permanent_delete_at);
CREATE INDEX IF NOT EXISTS idx_deleted_files_original_id ON deleted_files(original_id);

-- Create unique indexes
CREATE UNIQUE INDEX IF NOT EXISTS idx_sharing_links_token_unique ON sharing_links(token);
CREATE UNIQUE INDEX IF NOT EXISTS idx_extraction_sessions_token_unique ON extraction_sessions(session_token);

-- Add foreign key constraint for files.physical_file_id
ALTER TABLE files 
ADD CONSTRAINT fk_files_physical_file 
FOREIGN KEY (physical_file_id) REFERENCES physical_files(id) ON DELETE SET NULL;

-- Add constraints
ALTER TABLE permissions 
ADD CONSTRAINT chk_resource_type CHECK (resource_type IN ('file', 'folder')),
ADD CONSTRAINT chk_permission_type CHECK (permission_type IN ('read', 'write', 'share', 'admin'));

ALTER TABLE sharing_links 
ADD CONSTRAINT chk_permission_type CHECK (permission_type IN ('read', 'write', 'share', 'admin'));

ALTER TABLE workspace_members 
ADD CONSTRAINT chk_role CHECK (role IN ('owner', 'admin', 'editor', 'viewer'));

ALTER TABLE preview_cache 
ADD CONSTRAINT chk_preview_type CHECK (preview_type IN ('thumbnail', 'pdf', 'video', 'audio')),
ADD CONSTRAINT chk_quality CHECK (quality IN ('low', 'medium', 'high'));

ALTER TABLE preview_jobs 
ADD CONSTRAINT chk_job_type CHECK (job_type IN ('thumbnail', 'pdf', 'video', 'audio')),
ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'completed', 'failed'));

ALTER TABLE extraction_sessions 
ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'extracting', 'completed', 'failed'));

ALTER TABLE upload_sessions
ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'uploading', 'completed', 'failed'));

ALTER TABLE deleted_files
ADD CONSTRAINT chk_deleted_files_expires_at CHECK (expires_at > deleted_at),
ADD CONSTRAINT chk_deleted_files_permanent_delete_at CHECK (permanent_delete_at >= expires_at);

-- Create trigger function for updating updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updating updated_at
CREATE TRIGGER update_users_updated_at 
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_files_updated_at 
BEFORE UPDATE ON files
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_workspaces_updated_at 
BEFORE UPDATE ON workspaces
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Display setup completion
SELECT 'FileFlash database schema setup completed successfully!' as message;