-- Migration 002: Add Repository File Storage Tables
-- This migration adds comprehensive file system storage in PostgreSQL

-- Create table for repository local storage tracking
CREATE TABLE IF NOT EXISTS repository_storage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  repository_id UUID REFERENCES github_repositories(id) ON DELETE CASCADE,
  local_path TEXT NOT NULL,
  storage_status VARCHAR(50) DEFAULT 'pending', -- pending, downloading, completed, error
  total_files_count INTEGER DEFAULT 0,
  total_directories_count INTEGER DEFAULT 0,
  total_size_bytes BIGINT DEFAULT 0,
  download_started_at TIMESTAMP,
  download_completed_at TIMESTAMP,
  last_verified_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(repository_id)
);

-- Create table for directory structure
CREATE TABLE IF NOT EXISTS repository_directories (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  repository_id UUID REFERENCES github_repositories(id) ON DELETE CASCADE,
  storage_id UUID REFERENCES repository_storage(id) ON DELETE CASCADE,
  parent_directory_id UUID REFERENCES repository_directories(id) ON DELETE CASCADE,
  directory_name VARCHAR(255) NOT NULL,
  relative_path TEXT NOT NULL, -- path from repository root
  absolute_path TEXT NOT NULL, -- full local filesystem path
  level INTEGER DEFAULT 0, -- depth in hierarchy (0 = root)
  files_count INTEGER DEFAULT 0,
  subdirectories_count INTEGER DEFAULT 0,
  total_size_bytes BIGINT DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create table for individual files
CREATE TABLE IF NOT EXISTS repository_files (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  repository_id UUID REFERENCES github_repositories(id) ON DELETE CASCADE,
  storage_id UUID REFERENCES repository_storage(id) ON DELETE CASCADE,
  directory_id UUID REFERENCES repository_directories(id) ON DELETE SET NULL,
  filename VARCHAR(255) NOT NULL,
  file_extension VARCHAR(50),
  relative_path TEXT NOT NULL, -- path from repository root
  absolute_path TEXT NOT NULL, -- full local filesystem path
  file_size_bytes BIGINT DEFAULT 0,
  file_hash VARCHAR(64), -- SHA-256 hash for integrity
  mime_type VARCHAR(100),
  is_binary BOOLEAN DEFAULT false,
  encoding VARCHAR(50) DEFAULT 'utf-8',
  github_sha VARCHAR(40), -- GitHub blob SHA for tracking changes
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create table for file contents (for text files and searchability)
CREATE TABLE IF NOT EXISTS repository_file_contents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  file_id UUID REFERENCES repository_files(id) ON DELETE CASCADE,
  content_text TEXT, -- full content for text files
  content_preview TEXT, -- first 1000 characters for quick preview
  language_detected VARCHAR(50), -- programming language detected
  line_count INTEGER DEFAULT 0,
  char_count INTEGER DEFAULT 0,
  is_indexed BOOLEAN DEFAULT false, -- for search indexing status
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(file_id)
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_repository_storage_repo_id ON repository_storage(repository_id);
CREATE INDEX IF NOT EXISTS idx_repository_storage_status ON repository_storage(storage_status);

CREATE INDEX IF NOT EXISTS idx_repo_directories_repo_id ON repository_directories(repository_id);
CREATE INDEX IF NOT EXISTS idx_repo_directories_parent_id ON repository_directories(parent_directory_id);
CREATE INDEX IF NOT EXISTS idx_repo_directories_storage_id ON repository_directories(storage_id);
CREATE INDEX IF NOT EXISTS idx_repo_directories_level ON repository_directories(level);
CREATE INDEX IF NOT EXISTS idx_repo_directories_relative_path ON repository_directories(relative_path);

CREATE INDEX IF NOT EXISTS idx_repo_files_repo_id ON repository_files(repository_id);
CREATE INDEX IF NOT EXISTS idx_repo_files_directory_id ON repository_files(directory_id);
CREATE INDEX IF NOT EXISTS idx_repo_files_storage_id ON repository_files(storage_id);
CREATE INDEX IF NOT EXISTS idx_repo_files_extension ON repository_files(file_extension);
CREATE INDEX IF NOT EXISTS idx_repo_files_filename ON repository_files(filename);
CREATE INDEX IF NOT EXISTS idx_repo_files_relative_path ON repository_files(relative_path);
CREATE INDEX IF NOT EXISTS idx_repo_files_is_binary ON repository_files(is_binary);

CREATE INDEX IF NOT EXISTS idx_file_contents_file_id ON repository_file_contents(file_id);
CREATE INDEX IF NOT EXISTS idx_file_contents_language ON repository_file_contents(language_detected);
CREATE INDEX IF NOT EXISTS idx_file_contents_is_indexed ON repository_file_contents(is_indexed);

-- Full text search index for file contents
CREATE INDEX IF NOT EXISTS idx_file_contents_text_search ON repository_file_contents USING gin(to_tsvector('english', content_text));

-- Add update triggers
CREATE TRIGGER update_repository_storage_updated_at BEFORE UPDATE ON repository_storage
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_repository_directories_updated_at BEFORE UPDATE ON repository_directories
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_repository_files_updated_at BEFORE UPDATE ON repository_files
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_repository_file_contents_updated_at BEFORE UPDATE ON repository_file_contents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
