-- Migration script for WebAI verification worker
-- Run this in your PostgreSQL database to add verification tables

-- WebAI verification results table
CREATE TABLE IF NOT EXISTS public.webai_verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tender_id UUID NOT NULL REFERENCES public.tenders(id) ON DELETE CASCADE,
    analysis JSONB NOT NULL,
    comparison JSONB NOT NULL,
    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_webai_verifications_tender_id ON public.webai_verifications(tender_id);
CREATE INDEX IF NOT EXISTS idx_webai_verifications_created_at ON public.webai_verifications(created_at);

-- Add verification columns to tenders table
ALTER TABLE public.tenders 
ADD COLUMN IF NOT EXISTS verification_status TEXT DEFAULT 'PENDING' CHECK (verification_status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED')),
ADD COLUMN IF NOT EXISTS verification_score FLOAT DEFAULT 0.0 CHECK (verification_score >= 0.0 AND verification_score <= 1.0),
ADD COLUMN IF NOT EXISTS last_verified_at TIMESTAMP WITH TIME ZONE;

-- Add verification job type to processing_jobs
ALTER TABLE public.processing_jobs 
ADD CONSTRAINT IF NOT EXISTS check_job_type 
CHECK (job_type IN ('ANALYZE', 'DRAFT', 'VERIFY'));

-- Create trigger to update updated_at on webai_verifications
CREATE OR REPLACE FUNCTION update_webai_verifications_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language plpgsql;

CREATE TRIGGER webai_verifications_updated_at
    BEFORE UPDATE ON public.webai_verifications
    FOR EACH ROW
    EXECUTE FUNCTION update_webai_verifications_updated_at();

-- Add RLS policies if using Supabase
ALTER TABLE public.webai_verifications ENABLE ROW LEVEL SECURITY;

-- Policy for service role to manage verifications
CREATE POLICY "Service role can manage verifications" ON public.webai_verifications
    FOR ALL USING (auth.jwt() ->> 'role' = 'service_role')
    WITH CHECK (auth.jwt() ->> 'role' = 'service_role');

-- Policy for authenticated users to read verifications for their org
CREATE POLICY "Users can read own org verifications" ON public.webai_verifications
    FOR SELECT USING (
        auth.uid() IS NOT NULL AND
        EXISTS (
            SELECT 1 FROM public.tenders t
            WHERE t.id = public.webai_verifications.tender_id
            AND t.organization_id = (
                SELECT organization_id FROM public.users 
                WHERE id = auth.uid()
            )
        )
    );

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON public.webai_verifications TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.webai_verifications TO service_role;
GRANT USAGE ON SEQUENCE public.webai_verifications_id_seq TO authenticated;
GRANT USAGE ON SEQUENCE public.webai_verifications_id_seq TO service_role;

-- Create view for verification summary
CREATE OR REPLACE VIEW public.verification_summary AS
SELECT 
    t.id as tender_id,
    t.title as tender_title,
    t.organization_id,
    t.status as tender_status,
    t.verification_status,
    t.verification_score,
    t.last_verified_at,
    wv.analysis->>'tenderTitle' as webai_title,
    wv.analysis->>'procuringEntity' as webai_entity,
    wv.comparison->>'agreement_score' as agreement_score,
    wv.comparison->'recommendation_comparison' as bid_comparison,
    wv.created_at as verification_date,
    CASE 
        WHEN wv.comparison->>'agreement_score'::float >= 0.8 THEN 'HIGH_AGREEMENT'
        WHEN wv.comparison->>'agreement_score'::float >= 0.6 THEN 'MEDIUM_AGREEMENT'
        WHEN wv.comparison->>'agreement_score'::float >= 0.4 THEN 'LOW_AGREEMENT'
        ELSE 'DISAGREEMENT'
    END as agreement_level
FROM public.tenders t
LEFT JOIN public.webai_verifications wv ON t.id = wv.tender_id
WHERE t.status = 'ANALYSIS_READY' OR t.verification_status != 'PENDING';

-- Grant view permissions
GRANT SELECT ON public.verification_summary TO authenticated;
GRANT SELECT ON public.verification_summary TO service_role;