CREATE TABLE `tickets` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`anonymous_id` text NOT NULL,
	`status` text NOT NULL,
	`channel_id` text NOT NULL,
	`close_reason` text,
	`private_notes` text,
	`opened_at` integer NOT NULL,
	`closed_at` integer,
	`opened_by` integer NOT NULL,
	`open_reason` text,
	`subject` integer NOT NULL,
	`claimed_by` integer,
	`claimed_at` integer,
	`thanked_at` integer,
	`warned_at` integer,
	`last_message_at` integer,
	`has_moderator_message` integer,
	`has_author_message` integer,
	CONSTRAINT `fk_tickets_opened_by_actors_id_fk` FOREIGN KEY (`opened_by`) REFERENCES `actors`(`id`),
	CONSTRAINT `fk_tickets_subject_actors_id_fk` FOREIGN KEY (`subject`) REFERENCES `actors`(`id`),
	CONSTRAINT `fk_tickets_claimed_by_actors_id_fk` FOREIGN KEY (`claimed_by`) REFERENCES `actors`(`id`)
);
--> statement-breakpoint
CREATE TABLE `ticket_attachments` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`public_id` text NOT NULL,
	`message_id` integer,
	`ticket_id` integer NOT NULL,
	`actor_id` integer NOT NULL,
	`file_name` text NOT NULL,
	`file_type` text NOT NULL,
	`file_size` integer NOT NULL,
	CONSTRAINT `fk_ticket_attachments_message_id_ticket_messages_id_fk` FOREIGN KEY (`message_id`) REFERENCES `ticket_messages`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_attachments_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_attachments_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `ticket_messages` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`public_id` text NOT NULL,
	`message_id` text,
	`ticket_id` integer NOT NULL,
	`actor_id` integer NOT NULL,
	`content` text NOT NULL,
	`actor_role` text NOT NULL,
	`created_at` integer NOT NULL,
	`edited_at` integer,
	`deleted_at` integer,
	CONSTRAINT `fk_ticket_messages_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_messages_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);
--> statement-breakpoint
CREATE TABLE `ticket_participants` (
	`id` integer PRIMARY KEY AUTOINCREMENT,
	`ticket_id` integer NOT NULL,
	`actor_id` integer NOT NULL,
	`role` text NOT NULL,
	`reason` text,
	`created_at` integer NOT NULL,
	CONSTRAINT `fk_ticket_participants_ticket_id_tickets_id_fk` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
	CONSTRAINT `fk_ticket_participants_actor_id_actors_id_fk` FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`) ON DELETE CASCADE
);