-- Merges two lines of work that both ran on production:
--   * 0001-0005 came from the Sep 18-23 checkout and were applied to prod then.
--   * The cancellation requests table, ticket_types.admits_count and their
--     constraints below were applied to prod by hand on 2026-09-29 (via
--     phpMyAdmin, from a migration then numbered 0001_cancellations_and_group_tickets;
--     event_id's FK is RESTRICT because of the generated pending_marker column).
-- So on production, the ONLY statement still to run from this file is:
--   ALTER TABLE `events` MODIFY COLUMN `fee_bps` int NOT NULL DEFAULT 400;
-- (4% commission; the 16% tax on it is now added by db/queries/settlement.ts).
-- A fresh database runs the whole file.
CREATE TABLE `event_cancellation_requests` (
	`id` varchar(36) NOT NULL,
	`event_id` varchar(36) NOT NULL,
	`organizer_id` varchar(36) NOT NULL,
	`requested_by` varchar(36),
	`reason` text NOT NULL,
	`status` enum('pending','approved','rejected','withdrawn') NOT NULL DEFAULT 'pending',
	`resolution_note` text,
	`resolved_by` varchar(36),
	`resolved_at` timestamp,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`pending_marker` varchar(36) GENERATED ALWAYS AS ((case when `status` = 'pending' then `event_id` else null end)) STORED,
	CONSTRAINT `event_cancellation_requests_id` PRIMARY KEY(`id`),
	CONSTRAINT `uniq_event_cancellation_pending_per_event` UNIQUE(`pending_marker`)
);
--> statement-breakpoint
ALTER TABLE `events` MODIFY COLUMN `fee_bps` int NOT NULL DEFAULT 400;--> statement-breakpoint
ALTER TABLE `ticket_types` ADD `admits_count` int DEFAULT 1 NOT NULL;--> statement-breakpoint
ALTER TABLE `event_cancellation_requests` ADD CONSTRAINT `event_cancellation_requests_event_id_events_id_fk` FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `event_cancellation_requests` ADD CONSTRAINT `event_cancellation_requests_organizer_id_users_id_fk` FOREIGN KEY (`organizer_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `event_cancellation_requests` ADD CONSTRAINT `event_cancellation_requests_requested_by_users_id_fk` FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `event_cancellation_requests` ADD CONSTRAINT `event_cancellation_requests_resolved_by_users_id_fk` FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `idx_event_cancellation_requests_status` ON `event_cancellation_requests` (`status`);--> statement-breakpoint
CREATE INDEX `idx_event_cancellation_requests_event` ON `event_cancellation_requests` (`event_id`);--> statement-breakpoint
ALTER TABLE `ticket_types` ADD CONSTRAINT `admits_count_positive` CHECK (`ticket_types`.`admits_count` >= 1);