76 lines
3.7 KiB
MySQL
76 lines
3.7 KiB
MySQL
|
-- Migration 048: P-06 Upload-Einwilligungsdialog
|
||
|
|
-- Append-only Deklarations-Log + Schnellfelder in media_assets
|
||
|
|
-- Alle bestehenden Medien erhalten rights_status = 'legacy_unreviewed'
|
||
|
|
|
||
|
|
-- Deklarations-Log (append-only, wird nie geaendert oder geloescht)
|
||
|
|
CREATE TABLE IF NOT EXISTS media_asset_rights_declarations (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
media_asset_id INT NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
|
||
|
|
declared_by_profile_id INT REFERENCES profiles(id) ON DELETE SET NULL,
|
||
|
|
declared_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
|
||
|
|
-- Kontext der Erklaerung
|
||
|
|
action_type VARCHAR(50) NOT NULL
|
||
|
|
CHECK (action_type IN (
|
||
|
|
'upload', -- Erstupload
|
||
|
|
'promote_club', -- Promotion zu club
|
||
|
|
'promote_official', -- Promotion zu official
|
||
|
|
're_declaration', -- Freiwillige Nacherklaerung
|
||
|
|
'legacy_re_declaration' -- Altmedium: erste Erklaerung nachgereicht
|
||
|
|
)),
|
||
|
|
target_visibility VARCHAR(32) NOT NULL
|
||
|
|
CHECK (target_visibility IN ('private', 'club', 'official')),
|
||
|
|
-- Textversion der Erklaerung; 'p06-v1-conservative' = konservative Erstannahmen
|
||
|
|
-- VORLAEUTIG: Texte noch nicht juristisch geprueft
|
||
|
|
declaration_version VARCHAR(40) NOT NULL DEFAULT 'p06-v1-conservative',
|
||
|
|
|
||
|
|
-- Pflichtfeld (alle Sichtbarkeiten, alle Aktionen)
|
||
|
|
rights_holder_confirmed BOOLEAN NOT NULL,
|
||
|
|
|
||
|
|
-- Personen (konservative Annahme: immer abgefragt, auch bei 'private')
|
||
|
|
contains_identifiable_persons BOOLEAN,
|
||
|
|
person_consent_confirmed BOOLEAN, -- Pflicht wenn contains_identifiable_persons = true
|
||
|
|
|
||
|
|
-- Minderjaehrige
|
||
|
|
contains_minors BOOLEAN,
|
||
|
|
parental_consent_confirmed BOOLEAN, -- Pflicht wenn contains_minors = true
|
||
|
|
|
||
|
|
-- Drittmaterial
|
||
|
|
contains_music BOOLEAN,
|
||
|
|
music_rights_confirmed BOOLEAN, -- Pflicht wenn contains_music = true
|
||
|
|
contains_third_party_content BOOLEAN,
|
||
|
|
third_party_rights_confirmed BOOLEAN, -- Pflicht wenn contains_third_party_content = true
|
||
|
|
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mard_asset
|
||
|
|
ON media_asset_rights_declarations (media_asset_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mard_profile
|
||
|
|
ON media_asset_rights_declarations (declared_by_profile_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_mard_action_type
|
||
|
|
ON media_asset_rights_declarations (action_type);
|
||
|
|
|
||
|
|
-- Schnellfelder in media_assets (kein Ersatz fuer den Log, nur fuer effiziente Abfragen)
|
||
|
|
ALTER TABLE media_assets
|
||
|
|
ADD COLUMN IF NOT EXISTS rights_status VARCHAR(32)
|
||
|
|
NOT NULL DEFAULT 'legacy_unreviewed'
|
||
|
|
CHECK (rights_status IN ('legacy_unreviewed', 'declared', 'blocked')),
|
||
|
|
ADD COLUMN IF NOT EXISTS rights_declared_for_visibility VARCHAR(32)
|
||
|
|
CHECK (rights_declared_for_visibility IN ('private', 'club', 'official')),
|
||
|
|
ADD COLUMN IF NOT EXISTS rights_declared_at TIMESTAMPTZ;
|
||
|
|
|
||
|
|
-- Bestehende Medien: explicit legacy_unreviewed setzen (redundant zum DEFAULT, zur Klarheit)
|
||
|
|
UPDATE media_assets
|
||
|
|
SET rights_status = 'legacy_unreviewed'
|
||
|
|
WHERE rights_status = 'legacy_unreviewed'; -- no-op, setzt Default explizit
|
||
|
|
|
||
|
|
COMMENT ON TABLE media_asset_rights_declarations IS
|
||
|
|
'P-06: Append-only Erklaerungslog fuer Upload-Einwilligungen. '
|
||
|
|
'Eintraege werden nie geaendert. Juristische Validierung der Felder und Texte steht aus.';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN media_assets.rights_status IS
|
||
|
|
'P-06: legacy_unreviewed = Altbestand ohne P-06-Erklaerung; '
|
||
|
|
'declared = gueltige Erklaerung fuer rights_declared_for_visibility; '
|
||
|
|
'blocked = durch Admin gesperrt (P-11-Schnittstelle).';
|