50 lines
1.7 KiB
SQL
50 lines
1.7 KiB
SQL
CREATE TABLE IF NOT EXISTS plugin_reviewers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id TEXT UNIQUE,
|
|
username TEXT,
|
|
source TEXT,
|
|
profile_url TEXT,
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plugin_reviewers_source_username ON plugin_reviewers (source, username);
|
|
|
|
INSERT INTO plugin_reviewers (user_id, source, created_at, updated_at)
|
|
SELECT DISTINCT user_id, 'system', NOW(), NOW()
|
|
FROM plugin_reviews
|
|
WHERE user_id IS NOT NULL
|
|
ON CONFLICT (user_id) DO NOTHING;
|
|
|
|
|
|
ALTER TABLE plugin_reviews
|
|
ALTER COLUMN user_id DROP NOT NULL,
|
|
ADD COLUMN IF NOT EXISTS reviewer_id BIGINT;
|
|
|
|
|
|
UPDATE plugin_reviews r SET reviewer_id = p.id FROM plugin_reviewers p WHERE p.user_id = r.user_id;
|
|
|
|
-- Critical: Verify all reviews have a reviewer_id before setting NOT NULL constraint
|
|
-- This prevents migration failure if there are orphaned reviews
|
|
DO $$
|
|
DECLARE
|
|
orphaned_count INTEGER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO orphaned_count FROM plugin_reviews WHERE reviewer_id IS NULL;
|
|
|
|
IF orphaned_count > 0 THEN
|
|
RAISE NOTICE 'Found % orphaned reviews without reviewer_id. Deleting them to proceed with migration.', orphaned_count;
|
|
DELETE FROM plugin_reviews WHERE reviewer_id IS NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE plugin_reviews ALTER COLUMN reviewer_id SET NOT NULL;
|
|
|
|
ALTER TABLE plugin_reviews
|
|
DROP CONSTRAINT IF EXISTS plugin_reviews_plugin_slug_user_id_key,
|
|
ADD CONSTRAINT plugin_reviews_plugin_slug_reviewer_id_key UNIQUE (plugin_slug, reviewer_id),
|
|
ADD CONSTRAINT fk_plugin_reviews_reviewer
|
|
FOREIGN KEY (reviewer_id) REFERENCES plugin_reviewers(id)
|
|
ON DELETE CASCADE;
|