DO $$
DECLARE
    _path character varying(256) := 'patch-2025_10_31-EmailMessages_FromColumns_And_Indexes.';
BEGIN
    IF EXISTS (SELECT 1 FROM public."Versions" WHERE "Path" = _path) THEN
        RETURN;
    END IF;

    IF EXISTS (SELECT "Id" FROM public."Versions" WHERE "Build" >= 15567) THEN
        RETURN;
    END IF;

    BEGIN
        PERFORM 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'EmailMessages'
          AND column_name = 'FromAddress';
        IF NOT FOUND THEN
            EXECUTE 'ALTER TABLE public."EmailMessages" ADD COLUMN "FromAddress" character varying(512) NULL';
        END IF;
    
        PERFORM 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'EmailMessages'
          AND column_name = 'FromDisplayName';
        IF NOT FOUND THEN
            EXECUTE 'ALTER TABLE public."EmailMessages" ADD COLUMN "FromDisplayName" character varying(512) NULL';
        END IF;
    
        EXECUTE 'CREATE INDEX IF NOT EXISTS idx_emailmessages_fromaddress ON public."EmailMessages" ("FromAddress")';
    
        EXECUTE 'CREATE INDEX IF NOT EXISTS idx_emailmessages_ownerid_date ON public."EmailMessages" ("OwnerId", "Date" DESC)';
    
        EXECUTE 'CREATE INDEX IF NOT EXISTS idx_emailmessages_folderid_date ON public."EmailMessages" ("FolderId", "Date" DESC)';
    
        EXECUTE 'CREATE INDEX IF NOT EXISTS idx_emailmessages_folderid_uid ON public."EmailMessages" ("FolderId", "Uid")';
    
        EXECUTE '' ||
                'CREATE INDEX IF NOT EXISTS idx_emailmessages_ownerid_threadid_date ' ||
                'ON public."EmailMessages" ("OwnerId", "ThreadId", "Date" DESC)';
    
        EXECUTE 'CREATE INDEX IF NOT EXISTS idx_contacts_lower_email ON public."Contacts" (lower("Email"))';
    
        INSERT INTO public."Versions" ("Path","Build") VALUES (_path, 0);

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE '[ERR] %: %', _path, SQLERRM;
    END;
END;
$$;