

-- Enable extensions for better functionality
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      -- For UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";       -- For encryption functions
CREATE EXTENSION IF NOT EXISTS "pg_trgm";        -- For fuzzy text searching
CREATE EXTENSION IF NOT EXISTS "btree_gin";

-- ============================================
-- SESSIONS TABLE with partitioning support
-- ============================================
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token TEXT NOT NULL,
    ip_address INET,  -- Store IP address
    user_agent TEXT,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    -- Add constraint to ensure sessions expire in the future
    CONSTRAINT valid_expiry CHECK (expires_at > created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions for sessions (monthly partitions)
CREATE TABLE sessions_2025_08 PARTITION OF sessions
    FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE sessions_2025_09 PARTITION OF sessions
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
-- Add more partitions as needed

-- ============================================
-- PROJECTS TABLE with enhanced features
-- ============================================
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    client_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    
    -- Use ENUM type for better data integrity
    package VARCHAR(50),
    status VARCHAR(50) DEFAULT 'planning' 
        CHECK (status IN ('planning', 'in_progress', 'review', 'completed', 'cancelled')),
    
    -- Project metrics
    videos_total INTEGER DEFAULT 0 CHECK (videos_total >= 0),
    videos_completed INTEGER DEFAULT 0 CHECK (videos_completed >= 0),
    
    -- Dates
    deadline DATE,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    
    -- JSONB fields for flexible data
    requirements JSONB DEFAULT '{}'::jsonb,
    deliverables JSONB DEFAULT '[]'::jsonb,
    metadata JSONB DEFAULT '{}'::jsonb,
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    -- Computed progress percentage (PG 12+ generated column)
    progress_percentage INTEGER GENERATED ALWAYS AS (
        CASE 
            WHEN videos_total > 0 
            THEN (videos_completed * 100 / videos_total)
            ELSE 0
        END
    ) STORED,
    
    -- Constraints
    CONSTRAINT videos_check CHECK (videos_completed <= videos_total),
    CONSTRAINT dates_check CHECK (completed_at IS NULL OR completed_at >= started_at)
);

-- ============================================
-- MESSAGES TABLE with full-text search
-- ============================================
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sender_id UUID REFERENCES users(id) ON DELETE SET NULL,
    sender_name VARCHAR(255),
    recipient_id UUID REFERENCES users(id) ON DELETE CASCADE,
    subject VARCHAR(255),
    body TEXT,
    
    -- Message status
    read BOOLEAN DEFAULT FALSE,
    archived BOOLEAN DEFAULT FALSE,
    starred BOOLEAN DEFAULT FALSE,
    
    -- Thread support
    thread_id UUID,
    reply_to_id UUID REFERENCES messages(id) ON DELETE SET NULL,
    
    -- Attachments as JSONB array
    attachments JSONB DEFAULT '[]'::jsonb,
    
    -- Full-text search vector (automatically updated)
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', 
            coalesce(subject, '') || ' ' || 
            coalesce(body, '') || ' ' || 
            coalesce(sender_name, '')
        )
    ) STORED,
    
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- FILES TABLE with enhanced metadata
-- ============================================
CREATE TABLE files (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    
    -- File details
    name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255),
    size BIGINT CHECK (size > 0),
    mime_type VARCHAR(100),
    extension VARCHAR(20) GENERATED ALWAYS AS (
        lower(substring(name from '\.([^.]+)$'))
    ) STORED,
    
    -- Storage information
    storage_provider VARCHAR(50) DEFAULT 'local', -- 'local', 's3', 'cloudinary'
    storage_key VARCHAR(500) UNIQUE,
    url TEXT,
    thumbnail_url TEXT,
    
    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,
    exif_data JSONB,
    
    -- Versioning support
    version INTEGER DEFAULT 1,
    parent_file_id UUID REFERENCES files(id) ON DELETE SET NULL,
    
    -- Timestamps
    uploaded_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at TIMESTAMPTZ,
    
    -- Soft delete support
    deleted_at TIMESTAMPTZ,
    
    -- Ensure logical consistency
    CONSTRAINT file_version_check CHECK (
        (parent_file_id IS NULL AND version = 1) OR 
        (parent_file_id IS NOT NULL AND version > 1)
    )
);

-- ============================================
-- ACTIVITIES TABLE with better performance
-- ============================================
CREATE TABLE activities (
    id BIGSERIAL PRIMARY KEY,  -- BIGSERIAL for large-scale data
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    -- Activity details
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),  -- 'project', 'file', 'invoice', etc.
    entity_id UUID,
    
    -- Store details as JSONB for flexibility
    details JSONB DEFAULT '{}'::jsonb,
    
    -- Request metadata
    ip_address INET,
    user_agent TEXT,
    
    -- Timestamp with index
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (timestamp);

-- Create monthly partitions for activities
CREATE TABLE activities_2025_08 PARTITION OF activities
    FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE activities_2025_09 PARTITION OF activities
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

-- ============================================
-- INVOICES TABLE (new for FreshBooks integration)
-- ============================================
CREATE TABLE invoices_cache (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    freshbooks_invoice_id VARCHAR(255) UNIQUE NOT NULL,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    
    -- Invoice data cached from FreshBooks
    invoice_data JSONB NOT NULL,
    
    -- Quick access fields
    invoice_number VARCHAR(50),
    amount DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(50),
    due_date DATE,
    
    -- Timestamps
    cached_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_projects_requirements_gin ON projects USING GIN (requirements);
CREATE INDEX idx_messages_attachments_gin ON messages USING GIN (attachments);

-- Full-text search index
CREATE INDEX idx_messages_search ON messages USING GIN (search_vector);

-- Partial indexes for common queries
CREATE INDEX idx_sessions_active ON sessions(user_id) WHERE expires_at > CURRENT_TIMESTAMP;
CREATE INDEX idx_projects_active ON projects(client_id) WHERE status != 'completed' AND status != 'cancelled';
CREATE INDEX idx_messages_unread ON messages(recipient_id) WHERE read = FALSE;
CREATE INDEX idx_files_not_deleted ON files(user_id, project_id) WHERE deleted_at IS NULL;

-- Covering indexes (INCLUDE clause - PG 11+)
CREATE INDEX idx_projects_client_cover ON projects(client_id) 
    INCLUDE (name, status, progress_percentage);
CREATE INDEX idx_activities_user_cover ON activities(user_id) 
    INCLUDE (action, timestamp);

-- ============================================
-- TRIGGERS for automatic updates
-- ============================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_projects_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to clean expired sessions automatically
CREATE OR REPLACE FUNCTION clean_expired_sessions()
RETURNS void AS $$
BEGIN
    DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE files ENABLE ROW LEVEL SECURITY;

-- Create policies (example for users seeing their own data)
CREATE POLICY users_own_data ON users
    FOR ALL
    USING (id = current_setting('app.current_user_id', true)::uuid);

CREATE POLICY projects_own_data ON projects
    FOR ALL
    USING (client_id = current_setting('app.current_user_id', true)::uuid);

-- ============================================
-- MATERIALIZED VIEWS for reporting (refreshed periodically)
-- ============================================

CREATE MATERIALIZED VIEW project_statistics AS
SELECT 
    client_id,
    COUNT(*) as total_projects,
    COUNT(*) FILTER (WHERE status = 'completed') as completed_projects,
    COUNT(*) FILTER (WHERE status = 'in_progress') as active_projects,
    AVG(progress_percentage) as avg_progress,
    SUM(videos_total) as total_videos,
    SUM(videos_completed) as completed_videos
FROM projects
GROUP BY client_id
WITH DATA;

-- Create index on materialized view
CREATE UNIQUE INDEX idx_project_stats_client ON project_statistics(client_id);

-- ============================================
-- MAINTENANCE COMMANDS
-- ============================================

-- Grant permissions to application user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO acstrat1_dbuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO acstrat1_dbuser;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO acstrat1_dbuser;

-- Analyze tables for query optimization
ANALYZE;
