BEGIN;
-- Preserve references before removing the old screenshot catalogue.
INSERT INTO post_assets(post_id,asset_id,position,role)
SELECT ps.post_id,ps.asset_id,
 COALESCE((SELECT max(pa.position)+1 FROM post_assets pa WHERE pa.post_id=ps.post_id),0)
 +row_number() OVER(PARTITION BY ps.post_id ORDER BY ps.page_index)::integer-1,'content'
FROM page_snapshots ps WHERE NOT EXISTS(SELECT 1 FROM post_assets pa WHERE pa.post_id=ps.post_id AND pa.asset_id=ps.asset_id);
DROP TABLE render_jobs;
DROP TABLE page_snapshots;
ALTER TABLE posts DROP COLUMN page_key;

-- Every processed file is an ordinary asset, retaining its original storage key.
ALTER TABLE assets ADD COLUMN source_asset_id uuid REFERENCES assets(id) ON DELETE RESTRICT;
ALTER TABLE assets ADD COLUMN derivation text CHECK(derivation IN ('silent_video','audio_track','poster'));
ALTER TABLE assets ADD CONSTRAINT assets_derivation_source CHECK((source_asset_id IS NULL)=(derivation IS NULL));
CREATE UNIQUE INDEX assets_source_derivation ON assets(source_asset_id,derivation) WHERE source_asset_id IS NOT NULL;
INSERT INTO assets(id,kind,filename,storage_key,mime_type,byte_size,source_asset_id,derivation)
SELECT md5(v.storage_key)::uuid,CASE v.role WHEN 'poster' THEN 'image' WHEN 'audio_track' THEN 'audio' ELSE 'video' END,
 v.role,v.storage_key,v.mime_type,v.byte_size,v.asset_id,v.role FROM asset_variants v WHERE true
ON CONFLICT(storage_key) DO UPDATE SET source_asset_id=excluded.source_asset_id,derivation=excluded.derivation;
ALTER TABLE post_assets DROP CONSTRAINT post_assets_role_check;
ALTER TABLE post_assets ADD CONSTRAINT post_assets_role_check CHECK(role IN ('content','caption','silent_video','audio_track','poster'));
INSERT INTO post_assets(post_id,asset_id,position,role)
SELECT sources.post_id,a.id,
 COALESCE((SELECT max(pa.position)+1 FROM post_assets pa WHERE pa.post_id=sources.post_id),0)
 +row_number() OVER(PARTITION BY sources.post_id ORDER BY a.id)::integer-1,a.derivation
FROM (SELECT DISTINCT post_id,asset_id FROM post_assets WHERE role='content') sources
JOIN assets a ON a.source_asset_id=sources.asset_id
WHERE NOT EXISTS(SELECT 1 FROM post_assets pa WHERE pa.post_id=sources.post_id AND pa.asset_id=a.id);
DROP TABLE asset_variants;

-- Flatten tags, merging equal names without losing either kind of association.
DROP TRIGGER tags_no_cycles ON tags;
DROP FUNCTION prevent_tag_cycle();
CREATE TEMP TABLE tag_slugs ON COMMIT DROP AS SELECT id,slug,row_number() OVER(PARTITION BY slug ORDER BY id) AS rank FROM tags;
UPDATE tags SET slug='tag-'||replace(id::text,'-','');
UPDATE tags SET parent_id=NULL;
CREATE TEMP TABLE tag_merge ON COMMIT DROP AS
 SELECT id,first_value(id) OVER(PARTITION BY lower(btrim(name)) ORDER BY id) AS keep_id FROM tags;
INSERT INTO post_tags(post_id,tag_id) SELECT pt.post_id,m.keep_id FROM post_tags pt JOIN tag_merge m ON m.id=pt.tag_id WHERE true ON CONFLICT DO NOTHING;
INSERT INTO asset_tags(asset_id,tag_id) SELECT at.asset_id,m.keep_id FROM asset_tags at JOIN tag_merge m ON m.id=at.tag_id WHERE true ON CONFLICT DO NOTHING;
DELETE FROM tags t USING tag_merge m WHERE t.id=m.id AND m.id<>m.keep_id;
ALTER TABLE tags DROP COLUMN parent_id;
UPDATE tags t SET slug=CASE WHEN s.rank=1 THEN s.slug ELSE 'tag-'||replace(t.id::text,'-','') END FROM tag_slugs s WHERE s.id=t.id;
CREATE UNIQUE INDEX tags_slug_unique ON tags(slug);
CREATE UNIQUE INDEX tags_name_unique ON tags(lower(btrim(name)));
INSERT INTO schema_migrations(version) VALUES('004_flat_notes');
COMMIT;