-- Custom Content RAG Feature Migration
-- Enables users to upload PDFs and create text entries for AI knowledge base

-- Create custom_content table
CREATE TABLE IF NOT EXISTS public.custom_content (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    store_id UUID NOT NULL REFERENCES public.stores(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- Content metadata
    content_type TEXT NOT NULL CHECK (content_type IN ('text_entry', 'pdf_upload')),
    title TEXT NOT NULL,
    original_filename TEXT, -- For PDFs, original uploaded filename
    file_checksum TEXT, -- SHA-256 hash for deduplication

    -- Content data
    content_text TEXT, -- Extracted text (from PDF or user input)

    -- Storage paths
    storage_path_pdf TEXT, -- e.g., 'store_id/uuid.pdf'
    storage_path_txt TEXT, -- e.g., 'store_id/uuid.txt'

    -- File metadata
    file_size_bytes BIGINT,
    page_count INTEGER, -- For PDFs
    chunk_count INTEGER DEFAULT 0, -- Number of chunks in Qdrant

    -- Sync status tracking
    sync_status TEXT NOT NULL DEFAULT 'pending' CHECK (sync_status IN ('pending', 'processing', 'completed', 'failed')),
    sync_started_at TIMESTAMPTZ,
    sync_completed_at TIMESTAMPTZ,
    sync_error TEXT,

    -- Qdrant integration
    qdrant_point_ids TEXT[], -- Array of point IDs in Qdrant for cleanup

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Prevent duplicate files per store
    CONSTRAINT unique_store_checksum UNIQUE (store_id, file_checksum)
);

-- Create indexes for performance
CREATE INDEX idx_custom_content_store_id ON public.custom_content(store_id);
CREATE INDEX idx_custom_content_user_id ON public.custom_content(user_id);
CREATE INDEX idx_custom_content_content_type ON public.custom_content(content_type);
CREATE INDEX idx_custom_content_sync_status ON public.custom_content(sync_status);
CREATE INDEX idx_custom_content_checksum ON public.custom_content(file_checksum) WHERE file_checksum IS NOT NULL;
CREATE INDEX idx_custom_content_created_at ON public.custom_content(created_at DESC);

-- Enable Row Level Security
ALTER TABLE public.custom_content ENABLE ROW LEVEL SECURITY;

-- RLS Policies

-- Users can view custom content for stores they own
CREATE POLICY "Users can view their store's custom content"
    ON public.custom_content
    FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM public.stores
            WHERE stores.id = custom_content.store_id
            AND stores.user_id = auth.uid()
        )
    );

-- Users can insert custom content for their stores
CREATE POLICY "Users can create custom content for their stores"
    ON public.custom_content
    FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM public.stores
            WHERE stores.id = custom_content.store_id
            AND stores.user_id = auth.uid()
        )
        AND user_id = auth.uid()
    );

-- Users can update custom content for their stores
CREATE POLICY "Users can update their store's custom content"
    ON public.custom_content
    FOR UPDATE
    USING (
        EXISTS (
            SELECT 1 FROM public.stores
            WHERE stores.id = custom_content.store_id
            AND stores.user_id = auth.uid()
        )
    )
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM public.stores
            WHERE stores.id = custom_content.store_id
            AND stores.user_id = auth.uid()
        )
    );

-- Users can delete custom content from their stores
CREATE POLICY "Users can delete their store's custom content"
    ON public.custom_content
    FOR DELETE
    USING (
        EXISTS (
            SELECT 1 FROM public.stores
            WHERE stores.id = custom_content.store_id
            AND stores.user_id = auth.uid()
        )
    );

-- Service role can do everything (for Edge Functions)
CREATE POLICY "Service role has full access"
    ON public.custom_content
    FOR ALL
    USING (auth.jwt() ->> 'role' = 'service_role')
    WITH CHECK (auth.jwt() ->> 'role' = 'service_role');

-- Create updated_at trigger
CREATE OR REPLACE FUNCTION update_custom_content_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER custom_content_updated_at
    BEFORE UPDATE ON public.custom_content
    FOR EACH ROW
    EXECUTE FUNCTION update_custom_content_updated_at();

-- Create Supabase Storage bucket for custom content
-- Note: This is declarative - if bucket exists, it will be updated
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
    'custom-content',
    'custom-content',
    false, -- Private bucket
    10485760, -- 10MB limit
    ARRAY['application/pdf', 'text/plain']
)
ON CONFLICT (id) DO UPDATE SET
    file_size_limit = 10485760,
    allowed_mime_types = ARRAY['application/pdf', 'text/plain'];

-- Storage bucket RLS policies
-- Users can upload files to their store's folder
CREATE POLICY "Users can upload to their store folder"
    ON storage.objects
    FOR INSERT
    WITH CHECK (
        bucket_id = 'custom-content'
        AND (storage.foldername(name))[1] IN (
            SELECT id::text FROM public.stores WHERE user_id = auth.uid()
        )
    );

-- Users can read files from their store's folder
CREATE POLICY "Users can read from their store folder"
    ON storage.objects
    FOR SELECT
    USING (
        bucket_id = 'custom-content'
        AND (storage.foldername(name))[1] IN (
            SELECT id::text FROM public.stores WHERE user_id = auth.uid()
        )
    );

-- Users can delete files from their store's folder
CREATE POLICY "Users can delete from their store folder"
    ON storage.objects
    FOR DELETE
    USING (
        bucket_id = 'custom-content'
        AND (storage.foldername(name))[1] IN (
            SELECT id::text FROM public.stores WHERE user_id = auth.uid()
        )
    );

-- Service role has full access to storage
CREATE POLICY "Service role has full storage access"
    ON storage.objects
    FOR ALL
    USING (
        bucket_id = 'custom-content'
        AND auth.jwt() ->> 'role' = 'service_role'
    )
    WITH CHECK (
        bucket_id = 'custom-content'
        AND auth.jwt() ->> 'role' = 'service_role'
    );

-- Create helper function to get custom content statistics
CREATE OR REPLACE FUNCTION get_custom_content_stats(p_store_id UUID)
RETURNS TABLE (
    total_entries BIGINT,
    text_entries BIGINT,
    pdf_uploads BIGINT,
    total_chunks BIGINT,
    pending_sync BIGINT,
    failed_sync BIGINT,
    total_storage_bytes BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::BIGINT as total_entries,
        COUNT(*) FILTER (WHERE content_type = 'text_entry')::BIGINT as text_entries,
        COUNT(*) FILTER (WHERE content_type = 'pdf_upload')::BIGINT as pdf_uploads,
        COALESCE(SUM(chunk_count), 0)::BIGINT as total_chunks,
        COUNT(*) FILTER (WHERE sync_status IN ('pending', 'processing'))::BIGINT as pending_sync,
        COUNT(*) FILTER (WHERE sync_status = 'failed')::BIGINT as failed_sync,
        COALESCE(SUM(file_size_bytes), 0)::BIGINT as total_storage_bytes
    FROM public.custom_content
    WHERE store_id = p_store_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_custom_content_stats(UUID) TO authenticated;

-- Add comment for documentation
COMMENT ON TABLE public.custom_content IS 'Stores custom RAG content (text entries and uploaded PDFs) for AI knowledge base';
COMMENT ON COLUMN public.custom_content.file_checksum IS 'SHA-256 hash of file content for deduplication (PDFs only)';
COMMENT ON COLUMN public.custom_content.qdrant_point_ids IS 'Array of Qdrant point IDs for deletion when content is removed';
COMMENT ON COLUMN public.custom_content.chunk_count IS 'Number of text chunks stored in Qdrant (1 for small docs, N for chunked docs)';
