CREATE TABLE IF NOT EXISTS doc.doc_embedding_prefix_profile (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(128) NOT NULL UNIQUE,
    prefix_mode VARCHAR(32) NOT NULL,
    prefix_text TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE doc.doc_embedding
    ADD COLUMN IF NOT EXISTS prefix_profile_id UUID;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns c
        WHERE c.table_schema = 'doc'
          AND c.table_name = 'doc_embedding'
          AND c.column_name = 'prefix_mode'
    ) THEN
        EXECUTE $sql$
            INSERT INTO doc.doc_embedding_prefix_profile (code, prefix_mode, prefix_text)
            SELECT DISTINCT
                COALESCE(de.prefix_mode, 'OFF') || ':' || md5(COALESCE(de.applied_prefix, '')),
                COALESCE(de.prefix_mode, 'OFF'),
                COALESCE(de.applied_prefix, '')
            FROM doc.doc_embedding de
            ON CONFLICT (code) DO NOTHING
        $sql$;

        EXECUTE $sql$
            UPDATE doc.doc_embedding de
            SET prefix_profile_id = pp.id
            FROM doc.doc_embedding_prefix_profile pp
            WHERE de.prefix_profile_id IS NULL
              AND pp.code = COALESCE(de.prefix_mode, 'OFF') || ':' || md5(COALESCE(de.applied_prefix, ''))
        $sql$;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_doc_embedding_prefix_profile'
    ) THEN
        ALTER TABLE doc.doc_embedding
            ADD CONSTRAINT fk_doc_embedding_prefix_profile
            FOREIGN KEY (prefix_profile_id)
            REFERENCES doc.doc_embedding_prefix_profile(id);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_doc_embedding_prefix_profile
    ON doc.doc_embedding(prefix_profile_id);

DROP INDEX IF EXISTS doc.idx_doc_embedding_prefix_mode;

ALTER TABLE doc.doc_embedding
    DROP COLUMN IF EXISTS applied_prefix;

ALTER TABLE doc.doc_embedding
    DROP COLUMN IF EXISTS prefix_mode;
