-- Migration 004: Add opened_at column for one-time view tracking
ALTER TABLE customers ADD COLUMN IF NOT EXISTS opened_at TIMESTAMPTZ;
CREATE INDEX IF NOT EXISTS idx_customers_opened_at ON customers(opened_at) WHERE opened_at IS NOT NULL;

-- Allow NULL on encrypted columns after postcard is opened (one-time view)
ALTER TABLE customers ALTER COLUMN encrypted_image    DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN encryption_iv      DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN encryption_tag     DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN encrypted_message  DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN message_iv         DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN message_tag        DROP NOT NULL;
ALTER TABLE customers ALTER COLUMN decryption_key     DROP NOT NULL;

