Skip to content

armgit / Cap

Public Imported from GitHub
0

Open source Loom alternative. Beautiful, shareable screen recordings.

61.9 KB
Raw Blame
import type {
	Agent,
	AiGenerationLanguage,
	Comment,
	Folder,
	ImageUpload,
	Organisation,
	PublicCollection,
	S3Bucket,
	Space,
	Storage,
	User,
	Video,
} from "@cap/web-domain";
import { sql } from "drizzle-orm";
import {
	bigint,
	boolean,
	customType,
	datetime,
	float,
	foreignKey,
	index,
	int,
	json,
	longtext,
	mysqlTable,
	primaryKey,
	text,
	timestamp,
	unique,
	uniqueIndex,
	varchar,
} from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm/relations";

import { nanoIdLength } from "./helpers.ts";
import type {
	VideoCallToAction,
	VideoEditSpec,
	VideoMetadata,
} from "./types/index.ts";

export type AuthApiKeySource = "desktop" | "extension" | "mobile" | "unknown";

type GoogleDriveStorageQuotaCache = {
	limit?: string | null;
	usage?: string | null;
	usageInDrive?: string | null;
	usageInDriveTrash?: string | null;
	fetchedAt: string;
};

const nanoId = customType<{ data: string; notNull: true }>({
	dataType() {
		return `varchar(${nanoIdLength})`;
	},
});
// TODO: This will replace `nanoId` in: https://github.com/CapSoftware/Cap/pull/1105
const nanoIdRequired = (name: string) =>
	varchar(name, { length: nanoIdLength }).notNull();

const nanoIdNullable = customType<{ data: string; notNull: false }>({
	dataType() {
		return `varchar(${nanoIdLength})`;
	},
});

// Add a custom type for encrypted strings
const encryptedText = customType<{ data: string; notNull: true }>({
	dataType() {
		return "text";
	},
});

const encryptedTextNullable = customType<{ data: string; notNull: false }>({
	dataType() {
		return "text";
	},
});

export const users = mysqlTable(
	"users",
	{
		id: nanoId("id").notNull().primaryKey().$type<User.UserId>(),
		name: varchar("name", { length: 255 }),
		lastName: varchar("lastName", { length: 255 }),
		email: varchar("email", { length: 255 }).notNull(),
		emailVerified: timestamp("emailVerified"),
		image: varchar("image", { length: 255 }).$type<ImageUpload.ImageUrlOrKey>(),
		stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
		stripeSubscriptionId: varchar("stripeSubscriptionId", {
			length: 255,
		}),
		thirdPartyStripeSubscriptionId: varchar("thirdPartyStripeSubscriptionId", {
			length: 255,
		}),
		stripeSubscriptionStatus: varchar("stripeSubscriptionStatus", {
			length: 255,
		}),
		stripeSubscriptionPriceId: varchar("stripeSubscriptionPriceId", {
			length: 255,
		}),
		preferences: json("preferences")
			.$type<{
				notifications: {
					pauseComments: boolean;
					pauseReplies: boolean;
					pauseViews: boolean;
					pauseReactions: boolean;
					pauseAnonViews?: boolean;
				};
				trackedEvents?: {
					user_signed_up?: boolean;
				};
			} | null>()
			.default(null),
		activeOrganizationId: nanoId(
			"activeOrganizationId",
		).$type<Organisation.OrganisationId>(),
		created_at: timestamp("created_at").notNull().defaultNow(),
		updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
		onboardingSteps: json("onboardingSteps").$type<{
			welcome?: boolean;
			organizationSetup?: boolean;
			customDomain?: boolean;
			inviteTeam?: boolean;
			download?: boolean;
		}>(),
		onboarding_completed_at: timestamp("onboarding_completed_at"),
		customBucket: nanoIdNullable("customBucket"),
		inviteQuota: int("inviteQuota").notNull().default(1),
		defaultOrgId:
			nanoIdNullable("defaultOrgId").$type<Organisation.OrganisationId>(),
		authSessionVersion: int("authSessionVersion").notNull().default(0),
		marketingOrigin: varchar("marketingOrigin", { length: 20 })
			.notNull()
			.default("unknown"),
	},
	(table) => ({
		emailIndex: uniqueIndex("email_idx").on(table.email),
	}),
);

export const loopsSyncJobs = mysqlTable(
	"loops_sync_jobs",
	{
		userId: nanoId("userId").notNull().primaryKey().$type<User.UserId>(),
		revision: int("revision").notNull().default(1),
		nextAttemptAt: datetime("nextAttemptAt", { mode: "date" }).notNull(),
		leaseToken: varchar("leaseToken", { length: 36 }),
		leaseUntil: datetime("leaseUntil", { mode: "date" }),
		failures: int("failures").notNull().default(0),
		lastError: varchar("lastError", { length: 64 }),
		profileHash: varchar("profileHash", { length: 64 }),
		teammateJoinedAt: datetime("teammateJoinedAt", { mode: "date" }),
		syncedEmail: varchar("syncedEmail", { length: 255 }),
		lastSyncedAt: datetime("lastSyncedAt", { mode: "date" }),
	},
	(table) => ({
		dueIndex: index("loops_sync_due_idx").on(table.nextAttemptAt),
	}),
);

export const accounts = mysqlTable(
	"accounts",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull(),
		type: varchar("type", { length: 255 }).notNull(),
		provider: varchar("provider", { length: 255 }).notNull(),
		providerAccountId: varchar("providerAccountId", { length: 255 }).notNull(),
		access_token: text("access_token"),
		expires_in: int("expires_in"),
		id_token: text("id_token"),
		refresh_token: text("refresh_token"),
		refresh_token_expires_in: int("refresh_token_expires_in"),
		scope: varchar("scope", { length: 255 }),
		token_type: varchar("token_type", { length: 255 }),
		createdAt: timestamp("createdAt").defaultNow().notNull(),
		updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
		tempColumn: text("tempColumn"),
	},
	(table) => ({
		userIdIndex: index("user_id_idx").on(table.userId),
		providerAccountIdIndex: index("provider_account_id_idx").on(
			table.providerAccountId,
		),
	}),
);

export const sessions = mysqlTable(
	"sessions",
	{
		id: nanoId("id").notNull().primaryKey(),
		sessionToken: varchar("sessionToken", { length: 255 }).notNull(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		expires: datetime("expires").notNull(),
		created_at: timestamp("created_at").notNull().defaultNow(),
		updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		sessionTokenIndex: uniqueIndex("session_token_idx").on(table.sessionToken),
		userIdIndex: index("user_id_idx").on(table.userId),
	}),
);

export const verificationTokens = mysqlTable("verification_tokens", {
	identifier: varchar("identifier", { length: 255 }).primaryKey().notNull(),
	token: varchar("token", { length: 255 }).unique().notNull(),
	expires: datetime("expires").notNull(),
	created_at: timestamp("created_at").notNull().defaultNow(),
	updated_at: timestamp("updated_at").notNull().defaultNow().onUpdateNow(),
});

export const organizations = mysqlTable(
	"organizations",
	{
		id: nanoId("id")
			.notNull()
			.primaryKey()
			.$type<Organisation.OrganisationId>(),
		name: varchar("name", { length: 255 }).notNull(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		metadata: json("metadata"),
		tombstoneAt: timestamp("tombstoneAt"),
		allowedEmailDomain: varchar("allowedEmailDomain", { length: 255 }),
		defaultVideoVisibility: varchar("defaultVideoVisibility", {
			length: 7,
		}).$type<"private">(),
		customDomain: varchar("customDomain", { length: 255 }),
		domainVerified: timestamp("domainVerified"),
		settings: json("settings").$type<{
			disableSummary?: boolean;
			disableCaptions?: boolean;
			disableChapters?: boolean;
			disableReactions?: boolean;
			disableTranscript?: boolean;
			disableComments?: boolean;
			hideShareableLinkCapLogo?: boolean;
			shareableLinkUseOrganizationIcon?: boolean;
			aiGenerationLanguage?: AiGenerationLanguage;
			defaultPlaybackSpeed?: number;
		}>(),
		iconUrl: varchar("iconUrl", {
			length: 1024,
		}).$type<ImageUpload.ImageUrlOrKey>(),
		shareableLinkIconUrl: varchar("shareableLinkIconUrl", {
			length: 1024,
		}).$type<ImageUpload.ImageUrlOrKey>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		workosOrganizationId: varchar("workosOrganizationId", { length: 255 }),
		workosConnectionId: varchar("workosConnectionId", { length: 255 }),
	},
	(table) => ({
		ownerIdTombstoneIndex: index("owner_id_tombstone_idx").on(
			table.ownerId,
			table.tombstoneAt,
		),
		customDomainIndex: index("custom_domain_idx").on(table.customDomain),
		workosOrganizationIdIndex: uniqueIndex("workos_organization_id_idx").on(
			table.workosOrganizationId,
		),
	}),
);

export const organizationSso = mysqlTable(
	"organization_sso",
	{
		organizationId: nanoId("organizationId")
			.notNull()
			.primaryKey()
			.$type<Organisation.OrganisationId>(),
		purchasedByUserId: nanoId("purchasedByUserId")
			.notNull()
			.$type<User.UserId>(),
		stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
		stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 255 }),
		stripePriceId: varchar("stripePriceId", { length: 255 }),
		status: varchar("status", { length: 32 }).notNull().default("unpaid"),
		paidThrough: datetime("paidThrough"),
		currentPeriodEnd: datetime("currentPeriodEnd"),
		cancelAtPeriodEnd: boolean("cancelAtPeriodEnd").notNull().default(false),
		checkoutAttemptId: varchar("checkoutAttemptId", { length: 36 }),
		checkoutCurrency: varchar("checkoutCurrency", { length: 3 }),
		checkoutPriceId: varchar("checkoutPriceId", { length: 255 }),
		checkoutSessionId: varchar("checkoutSessionId", { length: 255 }),
		checkoutStartedAt: datetime("checkoutStartedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		stripeSubscriptionIdIndex: uniqueIndex("sso_stripe_subscription_id_idx").on(
			table.stripeSubscriptionId,
		),
	}),
);

export type OrganisationMemberRole = "owner" | "admin" | "member";
export const organizationMembers = mysqlTable(
	"organization_members",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		role: varchar("role", { length: 255 })
			.notNull()
			.$type<OrganisationMemberRole>(),
		hasProSeat: boolean("hasProSeat").default(false).notNull(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		userIdOrganizationIdIndex: index("user_id_organization_id_idx").on(
			table.userId,
			table.organizationId,
		),
	}),
);

export const integrationInstallations = mysqlTable(
	"integration_installations",
	{
		id: nanoId("id").notNull().primaryKey(),
		provider: varchar("provider", { length: 64 }).notNull(),
		externalId: varchar("externalId", { length: 255 }).notNull(),
		displayName: varchar("displayName", { length: 255 }).notNull(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		installedByUserId: nanoId("installedByUserId")
			.notNull()
			.$type<User.UserId>(),
		encryptedCredentials: encryptedText("encryptedCredentials").notNull(),
		metadata: json("metadata").notNull().$type<Record<string, unknown>>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		providerExternalIdIndex: uniqueIndex("provider_external_id_idx").on(
			table.provider,
			table.externalId,
		),
		organizationProviderDisplayNameIndex: index(
			"organization_provider_display_name_idx",
		).on(table.organizationId, table.provider, table.displayName),
	}),
);

export const organizationInvites = mysqlTable(
	"organization_invites",
	{
		id: nanoId("id").notNull().primaryKey(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		invitedEmail: varchar("invitedEmail", { length: 255 }).notNull(),
		invitedByUserId: nanoId("invitedByUserId").notNull().$type<User.UserId>(),
		role: varchar("role", { length: 255 })
			.notNull()
			.$type<OrganisationMemberRole>(),
		status: varchar("status", { length: 255 }).notNull().default("pending"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		expiresAt: timestamp("expiresAt"),
	},
	(table) => ({
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		invitedEmailIndex: index("invited_email_idx").on(table.invitedEmail),
		invitedByUserIdIndex: index("invited_by_user_id_idx").on(
			table.invitedByUserId,
		),
		statusIndex: index("status_idx").on(table.status),
	}),
);

export const folders = mysqlTable(
	"folders",
	{
		id: nanoId("id").notNull().primaryKey().$type<Folder.FolderId>(),
		name: varchar("name", { length: 255 }).notNull(),
		color: varchar("color", {
			length: 16,
			enum: ["normal", "blue", "red", "yellow"],
		})
			.notNull()
			.default("normal"),
		// Internet-facing public collection link (/c/[id]).
		public: boolean("public").notNull().default(false),
		settings: json("settings").$type<{
			publicPage?: PublicCollection.PublicPageSettings;
		}>(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		createdById: nanoId("createdById").notNull().$type<User.UserId>(),
		parentId: nanoIdNullable("parentId").$type<Folder.FolderId>(),
		spaceId: nanoIdNullable("spaceId").$type<Space.SpaceIdOrOrganisationId>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		createdByIdIndex: index("created_by_id_idx").on(table.createdById),
		parentIdIndex: index("parent_id_idx").on(table.parentId),
		spaceIdIndex: index("space_id_idx").on(table.spaceId),
		publicParentIdIndex: index("public_parent_id_idx").on(
			table.public,
			table.parentId,
		),
		publicSpaceParentIdIndex: index("public_space_parent_id_idx").on(
			table.public,
			table.spaceId,
			table.parentId,
		),
	}),
);

export const videos = mysqlTable(
	"videos",
	{
		id: nanoId("id").notNull().primaryKey().$type<Video.VideoId>(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		orgId: nanoIdRequired("orgId").$type<Organisation.OrganisationId>(),
		name: varchar("name", { length: 255 }).notNull().default("My Video"),
		bucket: nanoIdNullable("bucket").$type<S3Bucket.S3BucketId>(),
		storageIntegrationId: nanoIdNullable("storageIntegrationId")
			.references(() => storageIntegrations.id, { onDelete: "restrict" })
			.$type<Storage.StorageIntegrationId>(),
		// in seconds
		duration: float("duration"),
		width: int("width"),
		height: int("height"),
		fps: int("fps"),
		metadata: json("metadata").$type<VideoMetadata>(),
		public: boolean("public").notNull().default(true),
		settings: json("settings").$type<{
			disableSummary?: boolean;
			disableCaptions?: boolean;
			disableChapters?: boolean;
			disableReactions?: boolean;
			disableTranscript?: boolean;
			disableComments?: boolean;
			defaultPlaybackSpeed?: number;
			callToAction?: VideoCallToAction;
		}>(),
		transcriptionStatus: varchar("transcriptionStatus", { length: 255 }).$type<
			"PROCESSING" | "COMPLETE" | "ERROR" | "SKIPPED" | "NO_AUDIO"
		>(),
		source: json("source")
			.$type<
				| { type: "MediaConvert" }
				| { type: "local" }
				| {
						type: "desktopMP4";
						outputKey?: string;
						audioLevelOutputKey?: string;
						audioLevelSourceKey?: string;
						thumbnailKey?: string;
						previewKey?: string;
				  }
				| { type: "desktopSegments" }
				| {
						type: "webMP4";
						outputKey?: string;
						thumbnailKey?: string;
						previewKey?: string;
						audioLevelOutputKey?: string;
						audioLevelSourceKey?: string;
				  }
			>()
			.notNull()
			.default({ type: "MediaConvert" }),
		folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		effectiveCreatedAt: datetime("effectiveCreatedAt").generatedAlwaysAs(
			sql.raw(
				"COALESCE(\n          STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%s.%fZ'),\n          STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(`metadata`, '$.customCreatedAt')), '%Y-%m-%dT%H:%i:%sZ'),\n          `createdAt`\n        )",
			),
			{ mode: "stored" },
		),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		// PRIVATE
		password: encryptedTextNullable("password"),
		// LEGACY
		xStreamInfo: text("xStreamInfo"),
		firstViewEmailSentAt: timestamp("firstViewEmailSentAt"),
		isScreenshot: boolean("isScreenshot").notNull().default(false),
		// DEPRECATED
		awsRegion: varchar("awsRegion", { length: 255 }),
		awsBucket: varchar("awsBucket", { length: 255 }),
		videoStartTime: varchar("videoStartTime", { length: 255 }),
		audioStartTime: varchar("audioStartTime", { length: 255 }),
		jobId: varchar("jobId", { length: 255 }),
		jobStatus: varchar("jobStatus", { length: 255 }),
		skipProcessing: boolean("skipProcessing").notNull().default(false),
	},
	(table) => [
		index("owner_id_idx").on(table.ownerId),
		index("owner_updated_id_idx").on(table.ownerId, table.updatedAt, table.id),
		index("is_public_idx").on(table.public),
		index("folder_id_idx").on(table.folderId),
		index("storage_integration_id_idx").on(table.storageIntegrationId),
		index("org_owner_folder_idx").on(
			table.orgId,
			table.ownerId,
			table.folderId,
		),
		index("org_owner_folder_effective_idx").on(
			table.orgId,
			table.ownerId,
			table.folderId,
			table.effectiveCreatedAt,
		),
		index("org_effective_created_idx").on(
			table.orgId,
			table.effectiveCreatedAt,
		),
		index("screenshot_transcription_created_idx").on(
			table.isScreenshot,
			table.transcriptionStatus,
			table.createdAt,
		),
	],
);

export const videoEdits = mysqlTable("video_edits", {
	videoId: nanoId("videoId")
		.notNull()
		.primaryKey()
		.$type<Video.VideoId>()
		.references(() => videos.id, { onDelete: "cascade" }),
	sourceKey: varchar("sourceKey", { length: 512 }).notNull(),
	editSpec: json("editSpec").notNull().$type<VideoEditSpec>(),
	createdAt: timestamp("createdAt").notNull().defaultNow(),
	updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
});

export const sharedVideos = mysqlTable(
	"shared_videos",
	{
		id: nanoId("id").notNull().primaryKey(),
		videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
		folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		sharedByUserId: nanoId("sharedByUserId").notNull().$type<User.UserId>(),
		sharedAt: timestamp("sharedAt").notNull().defaultNow(),
	},
	(table) => ({
		folderIdIndex: index("folder_id_idx").on(table.folderId),
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		sharedByUserIdIndex: index("shared_by_user_id_idx").on(
			table.sharedByUserId,
		),
		videoIdOrganizationIdIndex: index("video_id_organization_id_idx").on(
			table.videoId,
			table.organizationId,
		),
		videoIdFolderIdIndex: index("video_id_folder_id_idx").on(
			table.videoId,
			table.folderId,
		),
	}),
);

export const videoViewerGrants = mysqlTable(
	"video_viewer_grants",
	{
		id: nanoId("id").notNull().primaryKey(),
		videoId: nanoId("videoId")
			.notNull()
			.$type<Video.VideoId>()
			.references(() => videos.id, { onDelete: "cascade" }),
		email: varchar("email", { length: 255 }).notNull(),
		invitedByUserId: nanoId("invitedByUserId").notNull().$type<User.UserId>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		revokedAt: timestamp("revokedAt"),
	},
	(table) => ({
		videoEmailUnique: uniqueIndex("video_email_idx").on(
			table.videoId,
			table.email,
		),
	}),
);

export const comments = mysqlTable(
	"comments",
	{
		id: nanoId("id").notNull().primaryKey().$type<Comment.CommentId>(),
		type: varchar("type", {
			length: 6,
			enum: ["emoji", "text", "video", "audio"],
		}).notNull(),
		content: text("content").notNull(),
		timestamp: float("timestamp"),
		authorId: nanoId("authorId").notNull().$type<User.UserId>(),
		videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		parentCommentId:
			nanoIdNullable("parentCommentId").$type<Comment.CommentId>(),
		// Media comments ("video"/"audio" type): object key under the parent
		// video's prefix (`${ownerId}/${videoId}/comments/${commentId}/...`),
		// served via /api/storage/object which asserts that prefix.
		mediaKey: varchar("mediaKey", { length: 512 }),
		mediaDuration: float("mediaDuration"),
		mediaMeta: json("mediaMeta").$type<Comment.MediaMeta>(),
	},
	(table) => ({
		videoTypeCreatedIndex: index("video_type_created_idx").on(
			table.videoId,
			table.type,
			table.createdAt,
			table.id,
		),
		authorIdIndex: index("author_id_idx").on(table.authorId),
		parentCommentIdIndex: index("parent_comment_id_idx").on(
			table.parentCommentId,
		),
	}),
);

export const notifications = mysqlTable(
	"notifications",
	{
		id: nanoId("id").notNull().primaryKey(),
		orgId: nanoId("orgId").notNull().$type<Organisation.OrganisationId>(),
		recipientId: nanoId("recipientId").notNull().$type<User.UserId>(),
		type: varchar("type", { length: 16 })
			.notNull()
			.$type<
				"view" | "comment" | "reply" | "reaction" | "anon_view" /*| "mention"*/
			>(),
		data: json("data")
			.$type<{
				videoId?: string;
				authorId?: string;
				comment?: {
					id: string;
					content: string;
				};
				anonName?: string;
				location?: string | null;
			}>()
			.notNull(),
		videoId: varchar("videoId", { length: 50 }),
		dedupKey: varchar("dedupKey", { length: 128 }),
		readAt: timestamp("readAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => ({
		orgIdIndex: index("org_id_idx").on(table.orgId),
		typeIndex: index("type_idx").on(table.type),
		readAtIndex: index("read_at_idx").on(table.readAt),
		createdAtIndex: index("created_at_idx").on(table.createdAt),
		recipientReadIndex: index("recipient_read_idx").on(
			table.recipientId,
			table.readAt,
		),
		recipientCreatedIndex: index("recipient_created_idx").on(
			table.recipientId,
			table.createdAt,
		),
		dedupKeyUnique: uniqueIndex("dedup_key_idx").on(table.dedupKey),
		typeRecipientCreatedIndex: index("type_recipient_created_idx").on(
			table.type,
			table.recipientId,
			table.createdAt,
		),
		typeRecipientVideoCreatedIndex: index(
			"type_recipient_video_created_idx",
		).on(table.type, table.recipientId, table.videoId, table.createdAt),
		videoIdIndex: index("video_id_idx").on(table.videoId),
	}),
);

export type MessengerAgent = "Millie";
export type MessengerConversationMode = "agent" | "human";
export type MessengerMessageRole = "user" | "agent" | "admin";

export const messengerConversations = mysqlTable(
	"messenger_conversations",
	{
		id: nanoId("id").notNull().primaryKey(),
		agent: varchar("agent", {
			length: 32,
			enum: ["Millie"],
		})
			.notNull()
			.$type<MessengerAgent>(),
		mode: varchar("mode", {
			length: 16,
			enum: ["agent", "human"],
		})
			.notNull()
			.default("agent")
			.$type<MessengerConversationMode>(),
		userId: nanoIdNullable("userId").$type<User.UserId>(),
		anonymousId: varchar("anonymousId", { length: 64 }),
		takeoverByUserId: nanoIdNullable("takeoverByUserId").$type<User.UserId>(),
		takeoverAt: timestamp("takeoverAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		lastMessageAt: timestamp("lastMessageAt").notNull().defaultNow(),
	},
	(table) => ({
		userLastMessageIndex: index("user_last_message_idx").on(
			table.userId,
			table.lastMessageAt,
		),
		anonymousLastMessageIndex: index("anonymous_last_message_idx").on(
			table.anonymousId,
			table.lastMessageAt,
		),
		modeLastMessageIndex: index("mode_last_message_idx").on(
			table.mode,
			table.lastMessageAt,
		),
		updatedAtIndex: index("updated_at_idx").on(table.updatedAt),
	}),
);

export const messengerMessages = mysqlTable(
	"messenger_messages",
	{
		id: nanoId("id").notNull().primaryKey(),
		conversationId: nanoId("conversationId")
			.notNull()
			.references(() => messengerConversations.id, { onDelete: "cascade" }),
		role: varchar("role", {
			length: 16,
			enum: ["user", "agent", "admin"],
		})
			.notNull()
			.$type<MessengerMessageRole>(),
		content: text("content").notNull(),
		userId: nanoIdNullable("userId").$type<User.UserId>(),
		anonymousId: varchar("anonymousId", { length: 64 }),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => ({
		conversationCreatedAtIndex: index("conversation_created_at_idx").on(
			table.conversationId,
			table.createdAt,
		),
		roleCreatedAtIndex: index("role_created_at_idx").on(
			table.role,
			table.createdAt,
		),
	}),
);

export const messengerSupportEmails = mysqlTable(
	"messenger_support_emails",
	{
		id: nanoId("id").notNull().primaryKey(),
		conversationId: nanoId("conversationId").notNull(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		userEmail: varchar("userEmail", { length: 255 }).notNull(),
		subject: varchar("subject", { length: 255 }).notNull(),
		message: text("message").notNull(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => ({
		conversationForeignKey: foreignKey({
			name: "support_email_conversation_fk",
			columns: [table.conversationId],
			foreignColumns: [messengerConversations.id],
		}).onDelete("cascade"),
		userCreatedAtIndex: index("support_email_user_created_at_idx").on(
			table.userId,
			table.createdAt,
		),
		conversationCreatedAtIndex: index(
			"support_email_conversation_created_at_idx",
		).on(table.conversationId, table.createdAt),
	}),
);

export const signedBaas = mysqlTable(
	"signed_baas",
	{
		id: nanoId("id").notNull().primaryKey(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		status: varchar("status", { length: 255 }).notNull().default("pending"),
		stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 255 }),
		entityName: varchar("entityName", { length: 255 }).notNull(),
		entityType: varchar("entityType", { length: 255 }).notNull(),
		entityAddress: varchar("entityAddress", { length: 500 }).notNull(),
		signerName: varchar("signerName", { length: 255 }).notNull(),
		signerTitle: varchar("signerTitle", { length: 255 }).notNull(),
		noticesEmail: varchar("noticesEmail", { length: 255 }).notNull(),
		signatureData: longtext("signatureData").notNull(),
		signedAt: timestamp("signedAt"),
		emailSentAt: timestamp("emailSentAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		organizationIdIndex: uniqueIndex("signed_baa_organization_id_idx").on(
			table.organizationId,
		),
		stripeSubscriptionIdIndex: index("signed_baa_stripe_subscription_idx").on(
			table.stripeSubscriptionId,
		),
	}),
);

export const s3Buckets = mysqlTable(
	"s3_buckets",
	{
		id: nanoId("id").notNull().primaryKey().$type<S3Bucket.S3BucketId>(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		organizationId:
			nanoIdNullable("organizationId").$type<Organisation.OrganisationId>(),
		region: encryptedText("region").notNull(),
		endpoint: encryptedTextNullable("endpoint"),
		bucketName: encryptedText("bucketName").notNull(),
		accessKeyId: encryptedText("accessKeyId").notNull(),
		secretAccessKey: encryptedText("secretAccessKey").notNull(),
		provider: text("provider").notNull().default("aws"),
		active: boolean("active").notNull().default(true),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		ownerOrganizationIndex: index("owner_organization_idx").on(
			table.ownerId,
			table.organizationId,
		),
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		organizationActiveIndex: index("organization_active_idx").on(
			table.organizationId,
			table.active,
			table.updatedAt,
		),
	}),
);

export const storageIntegrations = mysqlTable(
	"storage_integrations",
	{
		id: nanoId("id")
			.notNull()
			.primaryKey()
			.$type<Storage.StorageIntegrationId>(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		organizationId:
			nanoIdNullable("organizationId").$type<Organisation.OrganisationId>(),
		provider: varchar("provider", { length: 64 })
			.notNull()
			.$type<Storage.StorageProvider>(),
		displayName: varchar("displayName", { length: 255 }).notNull(),
		status: varchar("status", { length: 32 })
			.notNull()
			.default("active")
			.$type<Storage.StorageIntegrationStatus>(),
		active: boolean("active").notNull().default(false),
		encryptedConfig: encryptedText("encryptedConfig").notNull(),
		googleDriveAccessToken: encryptedTextNullable("googleDriveAccessToken"),
		googleDriveAccessTokenExpiresAt: timestamp(
			"googleDriveAccessTokenExpiresAt",
		),
		googleDriveTokenRefreshLeaseId: varchar("googleDriveTokenRefreshLeaseId", {
			length: 64,
		}),
		googleDriveTokenRefreshLeaseExpiresAt: timestamp(
			"googleDriveTokenRefreshLeaseExpiresAt",
		),
		googleDriveStorageQuotaCache: json(
			"googleDriveStorageQuotaCache",
		).$type<GoogleDriveStorageQuotaCache>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		ownerProviderIndex: index("owner_provider_idx").on(
			table.ownerId,
			table.provider,
		),
		ownerActiveIndex: index("owner_active_idx").on(table.ownerId, table.active),
		organizationProviderIndex: index("organization_provider_idx").on(
			table.organizationId,
			table.provider,
		),
		organizationActiveIndex: index("organization_active_idx").on(
			table.organizationId,
			table.active,
			table.status,
		),
	}),
);

export const storageObjects = mysqlTable(
	"storage_objects",
	{
		id: nanoId("id").notNull().primaryKey().$type<Storage.StorageObjectId>(),
		integrationId: nanoId("integrationId")
			.notNull()
			.references(() => storageIntegrations.id, { onDelete: "restrict" })
			.$type<Storage.StorageIntegrationId>(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		videoId: nanoIdNullable("videoId").$type<Video.VideoId>(),
		objectKey: text("objectKey").notNull(),
		objectKeyHash: varchar("objectKeyHash", { length: 64 }).notNull(),
		providerObjectId: varchar("providerObjectId", { length: 255 }).notNull(),
		uploadSessionUrl: encryptedTextNullable("uploadSessionUrl"),
		uploadStatus: varchar("uploadStatus", { length: 32 })
			.notNull()
			.default("pending")
			.$type<"pending" | "complete" | "error">(),
		contentType: varchar("contentType", { length: 255 }),
		contentLength: bigint("contentLength", { mode: "number", unsigned: true }),
		metadata: json("metadata").$type<Storage.StorageObjectMetadata>(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		integrationKeyHashIndex: uniqueIndex("integration_key_hash_idx").on(
			table.integrationId,
			table.objectKeyHash,
		),
		integrationStatusIndex: index("integration_status_idx").on(
			table.integrationId,
			table.uploadStatus,
		),
		integrationObjectKeyPrefixIndex: index(
			"integration_object_key_prefix_idx",
		).on(table.integrationId, sql`${table.objectKey}(191)`),
		videoIdIndex: index("video_id_idx").on(table.videoId),
		ownerIdIndex: index("owner_id_idx").on(table.ownerId),
	}),
);

export const notificationsRelations = relations(notifications, ({ one }) => ({
	org: one(organizations, {
		fields: [notifications.orgId],
		references: [organizations.id],
	}),
	recipient: one(users, {
		fields: [notifications.recipientId],
		references: [users.id],
	}),
}));

export const authApiKeys = mysqlTable(
	"auth_api_keys",
	{
		id: varchar("id", { length: 36 }).notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		source: varchar("source", { length: 32 })
			.notNull()
			.default("unknown")
			.$type<AuthApiKeySource>(),
		createdAt: timestamp("createdAt").defaultNow().notNull(),
	},
	(table) => ({
		userIdCreatedAtIndex: index("user_id_created_at_idx").on(
			table.userId,
			table.createdAt,
		),
	}),
);

export const agentApiKeys = mysqlTable(
	"agent_api_keys",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		tokenHash: varchar("tokenHash", { length: 64 }).notNull(),
		name: varchar("name", { length: 100 }).notNull().default("Cap CLI"),
		scopes: json("scopes").notNull().$type<Agent.AgentScope[]>(),
		expiresAt: timestamp("expiresAt").notNull(),
		revokedAt: timestamp("revokedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		lastUsedAt: timestamp("lastUsedAt"),
	},
	(table) => [
		uniqueIndex("token_hash_idx").on(table.tokenHash),
		index("user_created_at_idx").on(table.userId, table.createdAt),
		index("expires_at_idx").on(table.expiresAt),
	],
);

export const agentApiIdempotency = mysqlTable(
	"agent_api_idempotency",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		operation: varchar("operation", { length: 64 }).notNull(),
		keyHash: varchar("keyHash", { length: 64 }).notNull(),
		requestHash: varchar("requestHash", { length: 64 }).notNull(),
		state: varchar("state", {
			length: 16,
			enum: ["pending", "complete"],
		})
			.notNull()
			.default("pending"),
		statusCode: int("statusCode"),
		response: json("response").$type<unknown>(),
		expiresAt: timestamp("expiresAt").notNull(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => [
		uniqueIndex("user_operation_key_idx").on(
			table.userId,
			table.operation,
			table.keyHash,
		),
		index("expires_at_idx").on(table.expiresAt),
	],
);

export const agentApiAuthorizationCodes = mysqlTable(
	"agent_api_authorization_codes",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		codeHash: varchar("codeHash", { length: 64 }).notNull(),
		codeChallenge: varchar("codeChallenge", { length: 64 }).notNull(),
		redirectUri: varchar("redirectUri", { length: 512 }).notNull(),
		scopes: json("scopes").notNull().$type<Agent.AgentScope[]>(),
		expiresAt: timestamp("expiresAt").notNull(),
		consumedAt: timestamp("consumedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		uniqueIndex("code_hash_idx").on(table.codeHash),
		index("expires_at_idx").on(table.expiresAt),
		index("user_created_at_idx").on(table.userId, table.createdAt),
	],
);

export const mcpOAuthClients = mysqlTable(
	"mcp_oauth_clients",
	{
		id: nanoId("id").notNull().primaryKey(),
		clientId: varchar("clientId", { length: 128 }).notNull(),
		clientName: varchar("clientName", { length: 100 }).notNull(),
		redirectUris: json("redirectUris").notNull().$type<string[]>(),
		activatedAt: timestamp("activatedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		uniqueIndex("client_id_idx").on(table.clientId),
		index("inactive_created_at_idx").on(table.activatedAt, table.createdAt),
	],
);

export const mcpOAuthRegistrationQuotas = mysqlTable(
	"mcp_oauth_registration_quotas",
	{
		windowId: varchar("windowId", { length: 10 }).notNull().primaryKey(),
		registrations: int("registrations").notNull().default(0),
		expiresAt: timestamp("expiresAt").notNull(),
	},
	(table) => [index("expires_at_idx").on(table.expiresAt)],
);

export const mcpOAuthCodes = mysqlTable(
	"mcp_oauth_codes",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		clientId: varchar("clientId", { length: 128 }).notNull(),
		codeHash: varchar("codeHash", { length: 64 }).notNull(),
		codeChallenge: varchar("codeChallenge", { length: 64 }).notNull(),
		redirectUri: varchar("redirectUri", { length: 512 }).notNull(),
		resource: varchar("resource", { length: 512 }).notNull(),
		expiresAt: timestamp("expiresAt").notNull(),
		consumedAt: timestamp("consumedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		uniqueIndex("code_hash_idx").on(table.codeHash),
		index("expires_at_idx").on(table.expiresAt),
	],
);

export const mcpOAuthTokens = mysqlTable(
	"mcp_oauth_tokens",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		clientId: varchar("clientId", { length: 128 }).notNull(),
		familyId: nanoId("familyId").notNull(),
		resource: varchar("resource", { length: 512 }).notNull(),
		accessHash: varchar("accessHash", { length: 64 }).notNull(),
		refreshHash: varchar("refreshHash", { length: 64 }).notNull(),
		accessExpiresAt: timestamp("accessExpiresAt").notNull(),
		refreshExpiresAt: timestamp("refreshExpiresAt").notNull(),
		revokedAt: timestamp("revokedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		uniqueIndex("access_hash_idx").on(table.accessHash),
		uniqueIndex("refresh_hash_idx").on(table.refreshHash),
		index("family_id_idx").on(table.familyId),
		index("client_active_idx").on(
			table.clientId,
			table.revokedAt,
			table.refreshExpiresAt,
		),
		index("refresh_expires_at_idx").on(table.refreshExpiresAt),
	],
);

export const agentApiOperations = mysqlTable(
	"agent_api_operations",
	{
		id: nanoId("id").notNull().primaryKey(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		kind: varchar("kind", {
			length: 32,
			enum: [
				"duplicate_cap",
				"delete_cap",
				"import_loom",
				"delete_organization",
				"set_organization_domain",
				"remove_organization_domain",
				"verify_organization_domain",
				"transfer_org_content",
			],
		}).notNull(),
		resourceId: nanoId("resourceId").notNull(),
		resultResourceId: nanoIdNullable("resultResourceId"),
		state: varchar("state", {
			length: 16,
			enum: ["queued", "running", "succeeded", "failed"],
		})
			.notNull()
			.default("queued"),
		payload: json("payload").notNull().$type<unknown>(),
		result: json("result").$type<unknown>(),
		errorCode: varchar("errorCode", { length: 64 }),
		errorMessage: text("errorMessage"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		completedAt: timestamp("completedAt"),
	},
	(table) => [
		index("user_created_at_idx").on(table.userId, table.createdAt),
		index("state_updated_at_idx").on(table.state, table.updatedAt),
		index("resource_id_idx").on(table.resourceId),
	],
);

export const commentsRelations = relations(comments, ({ one }) => ({
	author: one(users, {
		fields: [comments.authorId],
		references: [users.id],
	}),
	video: one(videos, {
		fields: [comments.videoId],
		references: [videos.id],
	}),
	parentComment: one(comments, {
		fields: [comments.parentCommentId],
		references: [comments.id],
	}),
}));

export const messengerConversationsRelations = relations(
	messengerConversations,
	({ one, many }) => ({
		user: one(users, {
			fields: [messengerConversations.userId],
			references: [users.id],
		}),
		messages: many(messengerMessages),
		supportEmails: many(messengerSupportEmails),
	}),
);

export const messengerMessagesRelations = relations(
	messengerMessages,
	({ one }) => ({
		conversation: one(messengerConversations, {
			fields: [messengerMessages.conversationId],
			references: [messengerConversations.id],
		}),
		user: one(users, {
			fields: [messengerMessages.userId],
			references: [users.id],
		}),
	}),
);

export const messengerSupportEmailsRelations = relations(
	messengerSupportEmails,
	({ one }) => ({
		conversation: one(messengerConversations, {
			fields: [messengerSupportEmails.conversationId],
			references: [messengerConversations.id],
		}),
		user: one(users, {
			fields: [messengerSupportEmails.userId],
			references: [users.id],
		}),
	}),
);

// Define Relationships
export const usersRelations = relations(users, ({ many, one }) => ({
	accounts: many(accounts),
	sessions: many(sessions),
	organizationMembers: many(organizationMembers),
	videos: many(videos),
	sharedVideos: many(sharedVideos),
	customBucket: one(s3Buckets),
	storageIntegrations: many(storageIntegrations),
	spaces: many(spaces),
	spaceMembers: many(spaceMembers),
	messengerConversations: many(messengerConversations),
	messengerMessages: many(messengerMessages),
	messengerSupportEmails: many(messengerSupportEmails),
	integrationInstallations: many(integrationInstallations),
}));

export const accountsRelations = relations(accounts, ({ one }) => ({
	user: one(users, {
		fields: [accounts.userId],
		references: [users.id],
	}),
}));

export const s3BucketsRelations = relations(s3Buckets, ({ one }) => ({
	owner: one(users, {
		fields: [s3Buckets.ownerId],
		references: [users.id],
	}),
	organization: one(organizations, {
		fields: [s3Buckets.organizationId],
		references: [organizations.id],
	}),
}));

export const storageIntegrationsRelations = relations(
	storageIntegrations,
	({ one, many }) => ({
		owner: one(users, {
			fields: [storageIntegrations.ownerId],
			references: [users.id],
		}),
		organization: one(organizations, {
			fields: [storageIntegrations.organizationId],
			references: [organizations.id],
		}),
		objects: many(storageObjects),
	}),
);

export const storageObjectsRelations = relations(storageObjects, ({ one }) => ({
	integration: one(storageIntegrations, {
		fields: [storageObjects.integrationId],
		references: [storageIntegrations.id],
	}),
	video: one(videos, {
		fields: [storageObjects.videoId],
		references: [videos.id],
	}),
}));

export const organizationsRelations = relations(
	organizations,
	({ one, many }) => ({
		owner: one(users, {
			fields: [organizations.ownerId],
			references: [users.id],
		}),
		organizationMembers: many(organizationMembers),
		sharedVideos: many(sharedVideos),
		organizationInvites: many(organizationInvites),
		spaces: many(spaces),
		s3Buckets: many(s3Buckets),
		storageIntegrations: many(storageIntegrations),
		integrationInstallations: many(integrationInstallations),
	}),
);

export const sessionsRelations = relations(sessions, ({ one }) => ({
	user: one(users, {
		fields: [sessions.userId],
		references: [users.id],
	}),
}));

export const verificationTokensRelations = relations(
	verificationTokens,
	() => ({
		// No relations defined
	}),
);

export const organizationMembersRelations = relations(
	organizationMembers,
	({ one }) => ({
		user: one(users, {
			fields: [organizationMembers.userId],
			references: [users.id],
		}),
		organization: one(organizations, {
			fields: [organizationMembers.organizationId],
			references: [organizations.id],
		}),
	}),
);

export const integrationInstallationsRelations = relations(
	integrationInstallations,
	({ one }) => ({
		organization: one(organizations, {
			fields: [integrationInstallations.organizationId],
			references: [organizations.id],
		}),
		installedByUser: one(users, {
			fields: [integrationInstallations.installedByUserId],
			references: [users.id],
		}),
	}),
);

export const organizationInvitesRelations = relations(
	organizationInvites,
	({ one }) => ({
		organization: one(organizations, {
			fields: [organizationInvites.organizationId],
			references: [organizations.id],
		}),
		invitedByUser: one(users, {
			fields: [organizationInvites.invitedByUserId],
			references: [users.id],
		}),
	}),
);

export const videosRelations = relations(videos, ({ one, many }) => ({
	owner: one(users, {
		fields: [videos.ownerId],
		references: [users.id],
	}),
	sharedVideos: many(sharedVideos),
	spaceVideos: many(spaceVideos),
	edit: one(videoEdits, {
		fields: [videos.id],
		references: [videoEdits.videoId],
	}),
	folder: one(folders, {
		fields: [videos.folderId],
		references: [folders.id],
	}),
	storageIntegration: one(storageIntegrations, {
		fields: [videos.storageIntegrationId],
		references: [storageIntegrations.id],
	}),
}));

export const videoEditsRelations = relations(videoEdits, ({ one }) => ({
	video: one(videos, {
		fields: [videoEdits.videoId],
		references: [videos.id],
	}),
}));

export const sharedVideosRelations = relations(sharedVideos, ({ one }) => ({
	video: one(videos, {
		fields: [sharedVideos.videoId],
		references: [videos.id],
	}),
	organization: one(organizations, {
		fields: [sharedVideos.organizationId],
		references: [organizations.id],
	}),
	sharedByUser: one(users, {
		fields: [sharedVideos.sharedByUserId],
		references: [users.id],
	}),
}));

export const spaces = mysqlTable(
	"spaces",
	{
		id: nanoId("id")
			.notNull()
			.primaryKey()
			.$type<Space.SpaceIdOrOrganisationId>(),
		primary: boolean("primary").notNull().default(false),
		name: varchar("name", { length: 255 }).notNull(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		createdById: nanoId("createdById").notNull().$type<User.UserId>(),
		iconUrl: varchar("iconUrl", {
			length: 255,
		}).$type<ImageUpload.ImageUrlOrKey>(),
		description: varchar("description", { length: 1000 }),
		settings: json("settings").$type<{
			disableSummary?: boolean;
			disableCaptions?: boolean;
			disableChapters?: boolean;
			disableReactions?: boolean;
			disableTranscript?: boolean;
			disableComments?: boolean;
			publicPage?: PublicCollection.PublicPageSettings;
		}>(),
		password: encryptedTextNullable("password"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
		// Org-internal browsability: "Public" spaces are visible to all org
		// members. Unrelated to the internet-facing `public` flag below.
		privacy: varchar("privacy", { length: 255, enum: ["Public", "Private"] })
			.notNull()
			.default("Private"),
		// Internet-facing public collection link (/c/[id]).
		public: boolean("public").notNull().default(false),
	},
	(table) => ({
		organizationIdIndex: index("organization_id_idx").on(table.organizationId),
		createdByIdIndex: index("created_by_id_idx").on(table.createdById),
		publicOrganizationIdIndex: index("public_organization_id_idx").on(
			table.public,
			table.organizationId,
		),
	}),
);

export const spaceMembers = mysqlTable(
	"space_members",
	{
		id: nanoId("id").notNull().primaryKey(),
		spaceId: nanoId("spaceId").notNull().$type<Space.SpaceIdOrOrganisationId>(),
		userId: nanoId("userId").notNull().$type<User.UserId>(),
		role: varchar("role", { length: 255 })
			.notNull()
			.default("member")
			.$type<"admin" | "member">(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => ({
		userIdIndex: index("user_id_idx").on(table.userId),
		spaceIdUserIdUnique: unique("space_id_user_id_unique").on(
			table.spaceId,
			table.userId,
		),
	}),
);

export const spaceVideos = mysqlTable(
	"space_videos",
	{
		id: nanoId("id").notNull().primaryKey(),
		spaceId: nanoId("spaceId").notNull().$type<Space.SpaceIdOrOrganisationId>(),
		folderId: nanoIdNullable("folderId").$type<Folder.FolderId>(),
		videoId: nanoId("videoId").notNull().$type<Video.VideoId>(),
		addedById: nanoId("addedById").notNull().$type<User.UserId>(),
		addedAt: timestamp("addedAt").notNull().defaultNow(),
	},
	(table) => ({
		folderIdIndex: index("folder_id_idx").on(table.folderId),
		videoIdIndex: index("video_id_idx").on(table.videoId),
		addedByIdIndex: index("added_by_id_idx").on(table.addedById),
		spaceIdVideoIdIndex: index("space_id_video_id_idx").on(
			table.spaceId,
			table.videoId,
		),
		spaceIdFolderIdIndex: index("space_id_folder_id_idx").on(
			table.spaceId,
			table.folderId,
		),
	}),
);

export const spacesRelations = relations(spaces, ({ one, many }) => ({
	organization: one(organizations, {
		fields: [spaces.organizationId],
		references: [organizations.id],
	}),
	createdBy: one(users, {
		fields: [spaces.createdById],
		references: [users.id],
	}),
	spaceMembers: many(spaceMembers),
	spaceVideos: many(spaceVideos),
}));

export const spaceMembersRelations = relations(spaceMembers, ({ one }) => ({
	space: one(spaces, {
		fields: [spaceMembers.spaceId],
		references: [spaces.id],
	}),
	user: one(users, {
		fields: [spaceMembers.userId],
		references: [users.id],
	}),
}));

export const spaceVideosRelations = relations(spaceVideos, ({ one }) => ({
	space: one(spaces, {
		fields: [spaceVideos.spaceId],
		references: [spaces.id],
	}),
	video: one(videos, {
		fields: [spaceVideos.videoId],
		references: [videos.id],
	}),
	addedBy: one(users, {
		fields: [spaceVideos.addedById],
		references: [users.id],
	}),
}));

export const foldersRelations = relations(folders, ({ one, many }) => ({
	organization: one(organizations, {
		fields: [folders.organizationId],
		references: [organizations.id],
	}),
	createdBy: one(users, {
		fields: [folders.createdById],
		references: [users.id],
	}),
	parentFolder: one(folders, {
		fields: [folders.parentId],
		references: [folders.id],
		relationName: "parentChild",
	}),
	childFolders: many(folders, { relationName: "parentChild" }),
	videos: many(videos),
}));

export const videoUploads = mysqlTable(
	"video_uploads",
	{
		videoId: nanoId("video_id").primaryKey().notNull().$type<Video.VideoId>(),
		uploaded: bigint("uploaded", { mode: "number", unsigned: true })
			.notNull()
			.$defaultFn(() => 0),
		total: bigint("total", { mode: "number", unsigned: true })
			.notNull()
			.$defaultFn(() => 0),
		startedAt: timestamp("started_at").notNull().defaultNow(),
		updatedAt: timestamp("updated_at").notNull().defaultNow(),
		mode: varchar("mode", { length: 255, enum: ["singlepart", "multipart"] }),
		phase: varchar("phase", { length: 32 })
			.$type<
				| "uploading"
				| "processing"
				| "generating_thumbnail"
				| "complete"
				| "error"
			>()
			.notNull()
			.default("uploading"),
		processingProgress: int("processing_progress").notNull().default(0),
		processingMessage: varchar("processing_message", { length: 255 }),
		processingError: text("processing_error"),
		rawFileKey: varchar("raw_file_key", { length: 512 }),
	},
	(table) => [
		index("phase_updated_at_video_id_idx").on(
			table.phase,
			table.updatedAt,
			table.videoId,
		),
		index("started_at_idx").on(table.startedAt),
	],
);

export const videoProcessingJobs = mysqlTable(
	"video_processing_jobs",
	{
		videoId: nanoId("video_id").primaryKey().notNull().$type<Video.VideoId>(),
		ownerId: nanoId("owner_id").notNull().$type<User.UserId>(),
		generation: varchar("generation", { length: 64 }).notNull(),
		manifestSha256: varchar("manifest_sha256", { length: 64 }),
		state: varchar("state", { length: 32 })
			.$type<
				| "committing"
				| "queued"
				| "processing"
				| "retry"
				| "verified"
				| "source-blocked"
			>()
			.notNull()
			.default("committing"),
		attemptId: varchar("attempt_id", { length: 64 }),
		attemptCount: int("attempt_count").notNull().default(0),
		leaseExpiresAt: datetime("lease_expires_at", { fsp: 3 }),
		nextRetryAt: datetime("next_retry_at", { fsp: 3 }).notNull(),
		workflowRunId: varchar("workflow_run_id", { length: 255 }),
		remoteJobId: varchar("remote_job_id", { length: 255 }),
		source: json("source").$type<unknown>(),
		verification: json("verification").$type<unknown>(),
		output: json("output").$type<unknown>(),
		errorCode: varchar("error_code", { length: 64 }),
		errorMessage: text("error_message"),
		createdAt: datetime("created_at", { fsp: 3 })
			.notNull()
			.$defaultFn(() => new Date()),
		updatedAt: datetime("updated_at", { fsp: 3 })
			.notNull()
			.$defaultFn(() => new Date()),
	},
	(table) => [
		index("processing_state_retry_video_idx").on(
			table.state,
			table.nextRetryAt,
			table.videoId,
		),
		index("processing_state_lease_video_idx").on(
			table.state,
			table.leaseExpiresAt,
			table.videoId,
		),
	],
);

export const mediaProcessingBudgets = mysqlTable(
	"media_processing_budgets",
	{
		id: varchar("id", { length: 64 }).primaryKey().notNull(),
		reservedBytes: bigint("reserved_bytes", { mode: "number", unsigned: true })
			.notNull()
			.default(0),
		limitBytes: bigint("limit_bytes", {
			mode: "number",
			unsigned: true,
		}).notNull(),
		expiresAt: datetime("expires_at", { fsp: 3 }).notNull(),
	},
	(table) => [index("media_budget_expiry_idx").on(table.expiresAt)],
);

export const importedVideos = mysqlTable(
	"imported_videos",
	{
		id: nanoId("id").notNull(),
		orgId: nanoIdNullable("orgId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		source: varchar("source", { length: 255, enum: ["loom"] }).notNull(),
		sourceId: varchar("source_id", { length: 255 }).notNull(),
	},
	(table) => [
		primaryKey({ columns: [table.orgId, table.source, table.sourceId] }),
		index("id_idx").on(table.id),
	],
);

export const loomMigrationRequests = mysqlTable(
	"loom_migration_requests",
	{
		id: nanoId("id").notNull().primaryKey(),
		organizationId: nanoId("organizationId")
			.notNull()
			.$type<Organisation.OrganisationId>(),
		activeOrganizationId: nanoIdNullable(
			"activeOrganizationId",
		).$type<Organisation.OrganisationId>(),
		requestedByUserId: nanoId("requestedByUserId")
			.notNull()
			.$type<User.UserId>(),
		workspaceName: varchar("workspaceName", { length: 255 }),
		customerNote: text("customerNote"),
		customerReply: text("customerReply"),
		invitedAt: datetime("invitedAt", { mode: "date" }),
		status: varchar("status", {
			length: 32,
			enum: ["pending", "in_progress", "needs_information", "completed"],
		})
			.notNull()
			.default("pending"),
		capMessage: text("capMessage"),
		expectedVideoCount: int("expectedVideoCount"),
		importedVideoCount: int("importedVideoCount").notNull().default(0),
		queuedVideoCount: int("queuedVideoCount").notNull().default(0),
		activeImportCount: int("activeImportCount").notNull().default(0),
		activeImportLeaseToken: varchar("activeImportLeaseToken", { length: 36 }),
		activeImportLeaseUntil: datetime("activeImportLeaseUntil", {
			mode: "date",
		}),
		lastOperatorUserId:
			nanoIdNullable("lastOperatorUserId").$type<User.UserId>(),
		lastOperatorAt: datetime("lastOperatorAt", { mode: "date" }),
		completedAt: datetime("completedAt", { mode: "date" }),
		createdAt: datetime("createdAt", { mode: "date" })
			.notNull()
			.$defaultFn(() => new Date()),
		updatedAt: datetime("updatedAt", { mode: "date" })
			.notNull()
			.$defaultFn(() => new Date()),
	},
	(table) => [
		uniqueIndex("loom_migration_active_org_idx").on(table.activeOrganizationId),
		index("loom_migration_org_created_idx").on(
			table.organizationId,
			table.createdAt,
		),
		index("loom_migration_status_created_idx").on(
			table.status,
			table.createdAt,
		),
	],
);

export const loomMigrationImports = mysqlTable(
	"loom_migration_imports",
	{
		videoId: nanoId("videoId").notNull().primaryKey().$type<Video.VideoId>(),
		requestId: nanoId("requestId").notNull(),
		createdAt: datetime("createdAt", { mode: "date" })
			.notNull()
			.$defaultFn(() => new Date()),
	},
	(table) => [index("loom_migration_imports_request_idx").on(table.requestId)],
);

export const developerApps = mysqlTable(
	"developer_apps",
	{
		id: nanoId("id").notNull().primaryKey(),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		name: varchar("name", { length: 255 }).notNull(),
		environment: varchar("environment", {
			length: 16,
			enum: ["development", "production"],
		}).notNull(),
		logoUrl: varchar("logoUrl", { length: 1024 }),
		deletedAt: timestamp("deletedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => [index("owner_deleted_idx").on(table.ownerId, table.deletedAt)],
);

export const developerAppDomains = mysqlTable(
	"developer_app_domains",
	{
		id: nanoId("id").notNull().primaryKey(),
		appId: nanoId("appId")
			.notNull()
			.references(() => developerApps.id),
		domain: varchar("domain", { length: 253 }).notNull(),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [unique("app_domain_unique").on(table.appId, table.domain)],
);

export const developerApiKeys = mysqlTable(
	"developer_api_keys",
	{
		id: nanoId("id").notNull().primaryKey(),
		appId: nanoId("appId")
			.notNull()
			.references(() => developerApps.id),
		keyType: varchar("keyType", {
			length: 8,
			enum: ["public", "secret"],
		}).notNull(),
		keyPrefix: varchar("keyPrefix", { length: 12 }).notNull(),
		keyHash: varchar("keyHash", { length: 64 }).notNull(),
		encryptedKey: encryptedText("encryptedKey").notNull(),
		lastUsedAt: timestamp("lastUsedAt"),
		revokedAt: timestamp("revokedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		uniqueIndex("key_hash_idx").on(table.keyHash),
		index("app_key_type_idx").on(table.appId, table.keyType),
	],
);

export const developerVideos = mysqlTable(
	"developer_videos",
	{
		id: nanoId("id").notNull().primaryKey(),
		appId: nanoId("appId")
			.notNull()
			.references(() => developerApps.id),
		externalUserId: varchar("externalUserId", { length: 255 }),
		name: varchar("name", { length: 255 }).notNull().default("Untitled"),
		duration: float("duration"),
		width: int("width"),
		height: int("height"),
		fps: int("fps"),
		s3Key: varchar("s3Key", { length: 512 }),
		transcriptionStatus: varchar("transcriptionStatus", {
			length: 16,
			enum: ["PROCESSING", "COMPLETE", "ERROR", "SKIPPED", "NO_AUDIO"],
		}),
		metadata: json("metadata"),
		deletedAt: timestamp("deletedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => [
		index("app_created_idx").on(table.appId, table.createdAt),
		index("app_user_idx").on(table.appId, table.externalUserId),
		index("app_deleted_idx").on(table.appId, table.deletedAt),
	],
);

export const developerCreditAccounts = mysqlTable(
	"developer_credit_accounts",
	{
		id: nanoId("id").notNull().primaryKey(),
		appId: nanoId("appId")
			.notNull()
			.references(() => developerApps.id),
		ownerId: nanoId("ownerId").notNull().$type<User.UserId>(),
		balanceMicroCredits: bigint("balanceMicroCredits", {
			mode: "number",
			unsigned: true,
		})
			.notNull()
			.default(0),
		stripeCustomerId: varchar("stripeCustomerId", { length: 255 }),
		stripePaymentMethodId: varchar("stripePaymentMethodId", { length: 255 }),
		autoTopUpEnabled: boolean("autoTopUpEnabled").notNull().default(false),
		autoTopUpThresholdMicroCredits: bigint("autoTopUpThresholdMicroCredits", {
			mode: "number",
			unsigned: true,
		})
			.notNull()
			.default(0),
		autoTopUpAmountCents: int("autoTopUpAmountCents").notNull().default(0),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
		updatedAt: timestamp("updatedAt").notNull().defaultNow().onUpdateNow(),
	},
	(table) => [uniqueIndex("app_id_unique").on(table.appId)],
);

export type DeveloperCreditTransactionType =
	| "topup"
	| "video_create"
	| "storage_daily"
	| "refund"
	| "adjustment";

export type DeveloperCreditReferenceType =
	| "developer_video"
	| "stripe_payment_intent"
	| "manual";

export const developerCreditTransactions = mysqlTable(
	"developer_credit_transactions",
	{
		id: nanoId("id").notNull().primaryKey(),
		accountId: nanoId("accountId").notNull(),
		type: varchar("type", {
			length: 16,
			enum: ["topup", "video_create", "storage_daily", "refund", "adjustment"],
		})
			.notNull()
			.$type<DeveloperCreditTransactionType>(),
		amountMicroCredits: bigint("amountMicroCredits", {
			mode: "number",
		}).notNull(),
		balanceAfterMicroCredits: bigint("balanceAfterMicroCredits", {
			mode: "number",
			unsigned: true,
		}).notNull(),
		referenceId: varchar("referenceId", { length: 255 }),
		referenceType: varchar("referenceType", {
			length: 32,
			enum: ["developer_video", "stripe_payment_intent", "manual"],
		}).$type<DeveloperCreditReferenceType>(),
		metadata: json("metadata"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [
		foreignKey({
			name: "dev_credit_txn_account_fk",
			columns: [table.accountId],
			foreignColumns: [developerCreditAccounts.id],
		}),
		index("account_type_created_idx").on(
			table.accountId,
			table.type,
			table.createdAt,
		),
		index("account_ref_dedup_idx").on(
			table.accountId,
			table.referenceId,
			table.referenceType,
		),
	],
);

export const developerDailyStorageSnapshots = mysqlTable(
	"developer_daily_storage_snapshots",
	{
		id: nanoId("id").notNull().primaryKey(),
		appId: nanoId("appId")
			.notNull()
			.references(() => developerApps.id),
		snapshotDate: varchar("snapshotDate", { length: 10 }).notNull(),
		totalDurationMinutes: float("totalDurationMinutes").notNull().default(0),
		videoCount: int("videoCount").notNull().default(0),
		microCreditsCharged: bigint("microCreditsCharged", {
			mode: "number",
			unsigned: true,
		})
			.notNull()
			.default(0),
		processedAt: timestamp("processedAt"),
		createdAt: timestamp("createdAt").notNull().defaultNow(),
	},
	(table) => [unique("app_date_unique").on(table.appId, table.snapshotDate)],
);

export const developerAppsRelations = relations(
	developerApps,
	({ one, many }) => ({
		owner: one(users, {
			fields: [developerApps.ownerId],
			references: [users.id],
		}),
		domains: many(developerAppDomains),
		apiKeys: many(developerApiKeys),
		videos: many(developerVideos),
		creditAccount: one(developerCreditAccounts, {
			fields: [developerApps.id],
			references: [developerCreditAccounts.appId],
		}),
		storageSnapshots: many(developerDailyStorageSnapshots),
	}),
);

export const developerAppDomainsRelations = relations(
	developerAppDomains,
	({ one }) => ({
		app: one(developerApps, {
			fields: [developerAppDomains.appId],
			references: [developerApps.id],
		}),
	}),
);

export const developerApiKeysRelations = relations(
	developerApiKeys,
	({ one }) => ({
		app: one(developerApps, {
			fields: [developerApiKeys.appId],
			references: [developerApps.id],
		}),
	}),
);

export const developerVideosRelations = relations(
	developerVideos,
	({ one }) => ({
		app: one(developerApps, {
			fields: [developerVideos.appId],
			references: [developerApps.id],
		}),
	}),
);

export const developerCreditAccountsRelations = relations(
	developerCreditAccounts,
	({ one, many }) => ({
		app: one(developerApps, {
			fields: [developerCreditAccounts.appId],
			references: [developerApps.id],
		}),
		owner: one(users, {
			fields: [developerCreditAccounts.ownerId],
			references: [users.id],
		}),
		transactions: many(developerCreditTransactions),
	}),
);

export const developerCreditTransactionsRelations = relations(
	developerCreditTransactions,
	({ one }) => ({
		account: one(developerCreditAccounts, {
			fields: [developerCreditTransactions.accountId],
			references: [developerCreditAccounts.id],
		}),
	}),
);

export const developerDailyStorageSnapshotsRelations = relations(
	developerDailyStorageSnapshots,
	({ one }) => ({
		app: one(developerApps, {
			fields: [developerDailyStorageSnapshots.appId],
			references: [developerApps.id],
		}),
	}),
);