ALTER TABLE shows ADD COLUMN completed_at_utc TEXT;

ALTER TABLE show_sections ADD COLUMN category_name_snapshot TEXT;
ALTER TABLE show_sections ADD COLUMN category_color_snapshot TEXT;

CREATE TABLE show_finalizations (
	show_id TEXT PRIMARY KEY REFERENCES shows(id) ON DELETE CASCADE,
	command_id TEXT NOT NULL,
	source_revision INTEGER NOT NULL CHECK (source_revision >= 1),
	finalized_by_user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
	finalized_at_utc TEXT NOT NULL,
	UNIQUE (show_id, command_id)
);

CREATE TABLE show_finalization_archived_pieces (
	show_id TEXT NOT NULL REFERENCES show_finalizations(show_id) ON DELETE CASCADE,
	piece_id TEXT NOT NULL REFERENCES pieces(id) ON DELETE RESTRICT,
	PRIMARY KEY (show_id, piece_id)
);

CREATE TABLE piece_usage_history (
	usage_id TEXT PRIMARY KEY REFERENCES piece_usages(id) ON DELETE RESTRICT,
	show_id TEXT NOT NULL REFERENCES shows(id) ON DELETE RESTRICT,
	original_piece_id TEXT NOT NULL REFERENCES pieces(id) ON DELETE RESTRICT,
	show_title_snapshot TEXT NOT NULL,
	usage_title_snapshot TEXT NOT NULL,
	used_at_utc TEXT NOT NULL,
	actual_duration_seconds INTEGER NOT NULL CHECK (actual_duration_seconds >= 0)
);

CREATE INDEX idx_piece_usage_history_original
	ON piece_usage_history(original_piece_id, used_at_utc DESC);

CREATE TABLE show_duplication_commands (
	source_show_id TEXT NOT NULL REFERENCES show_finalizations(show_id) ON DELETE CASCADE,
	command_id TEXT NOT NULL,
	created_show_id TEXT NOT NULL REFERENCES shows(id) ON DELETE CASCADE,
	created_at_utc TEXT NOT NULL,
	PRIMARY KEY (source_show_id, command_id)
);

CREATE TABLE show_shares (
	id TEXT PRIMARY KEY,
	show_id TEXT NOT NULL REFERENCES show_finalizations(show_id) ON DELETE CASCADE,
	token_hash TEXT NOT NULL UNIQUE,
	scope TEXT NOT NULL CHECK (scope IN ('rundown', 'moderation', 'research')),
	password_hash TEXT,
	expires_at_utc TEXT,
	revoked_at_utc TEXT,
	created_by_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
	created_at_utc TEXT NOT NULL
);

CREATE INDEX idx_show_shares_show ON show_shares(show_id, created_at_utc DESC);

CREATE TABLE show_share_sessions (
	token_hash TEXT PRIMARY KEY,
	share_id TEXT NOT NULL REFERENCES show_shares(id) ON DELETE CASCADE,
	created_at_utc TEXT NOT NULL,
	expires_at_utc TEXT NOT NULL
);

CREATE INDEX idx_show_share_sessions_share ON show_share_sessions(share_id, expires_at_utc);

CREATE TABLE share_password_attempts (
	id TEXT PRIMARY KEY,
	share_key TEXT NOT NULL,
	address_key TEXT NOT NULL,
	attempted_at_utc TEXT NOT NULL
);

CREATE INDEX idx_share_password_attempts_share
	ON share_password_attempts(share_key, attempted_at_utc);
CREATE INDEX idx_share_password_attempts_address
	ON share_password_attempts(address_key, attempted_at_utc);

UPDATE show_sections
SET category_name_snapshot = (
		SELECT name FROM section_categories WHERE id = show_sections.category_id
	),
	category_color_snapshot = (
		SELECT color FROM section_categories WHERE id = show_sections.category_id
	)
WHERE show_id IN (SELECT id FROM shows WHERE status = 'completed');

UPDATE shows
SET completed_at_utc = COALESCE(
	(SELECT ended_at_utc FROM show_live_states WHERE show_id = shows.id),
	updated_at_utc
)
WHERE status = 'completed';

INSERT INTO show_finalizations (
	show_id, command_id, source_revision, finalized_by_user_id, finalized_at_utc
)
SELECT id, 'migration-' || id, revision, NULL, completed_at_utc
FROM shows
WHERE status = 'completed';

INSERT INTO piece_usage_history (
	usage_id, show_id, original_piece_id, show_title_snapshot, usage_title_snapshot,
	used_at_utc, actual_duration_seconds
)
SELECT u.id, sh.id, u.original_piece_id, sh.title, u.title_snapshot,
	COALESCE(sh.starts_at_utc, sh.completed_at_utc),
	COALESCE((
		SELECT sum(max(0, CAST((julianday(COALESCE(r.ended_at_utc, sh.completed_at_utc)) -
			julianday(r.started_at_utc)) * 86400 AS INTEGER)))
		FROM live_usage_runs r WHERE r.usage_id = u.id
	), 0)
FROM piece_usages u
JOIN show_sections s ON s.id = u.show_section_id
JOIN shows sh ON sh.id = s.show_id
WHERE sh.status = 'completed' AND s.is_reserve_bank = 0
	AND u.status = 'done' AND u.original_piece_id IS NOT NULL;