import { sql } from "drizzle-orm";
import {
	bigserial,
	check,
	customType,
	index,
	integer,
	jsonb,
	pgTable,
	primaryKey,
	text,
	timestamp,
	unique,
	uuid,
} from "drizzle-orm/pg-core";

const citext = customType<{ data: string }>({
	dataType() {
		return "citext";
	},
});

export const organizations = pgTable("organizations", {
	id: uuid("id").primaryKey().defaultRandom(),
	name: text("name").notNull(),
	createdAt: timestamp("created_at", { withTimezone: true })
		.notNull()
		.defaultNow(),
});

export const users = pgTable(
	"users",
	{
		id: uuid("id").primaryKey().defaultRandom(),
		organizationId: uuid("organization_id")
			.notNull()
			.references(() => organizations.id, { onDelete: "cascade" }),
		email: citext("email").notNull().unique(),
		oauthProvider: text("oauth_provider").notNull(),
		oauthSubject: text("oauth_subject").notNull(),
		createdAt: timestamp("created_at", { withTimezone: true })
			.notNull()
			.defaultNow(),
	},
	(table) => [
		unique("users_oauth_provider_subject_unique").on(
			table.oauthProvider,
			table.oauthSubject,
		),
	],
);

export const sessions = pgTable("sessions", {
	id: text("id").primaryKey(),
	userId: uuid("user_id")
		.notNull()
		.references(() => users.id, { onDelete: "cascade" }),
	expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
	createdAt: timestamp("created_at", { withTimezone: true })
		.notNull()
		.defaultNow(),
});

export const projects = pgTable("projects", {
	id: uuid("id").primaryKey().defaultRandom(),
	organizationId: uuid("organization_id")
		.notNull()
		.references(() => organizations.id, { onDelete: "cascade" }),
	name: text("name").notNull(),
	publicKey: text("public_key").notNull().unique(),
	allowedOrigins: text("allowed_origins").array().notNull().default(sql`'{}'`),
	createdAt: timestamp("created_at", { withTimezone: true })
		.notNull()
		.defaultNow(),
});

export const tours = pgTable(
	"tours",
	{
		id: uuid("id").primaryKey().defaultRandom(),
		projectId: uuid("project_id")
			.notNull()
			.references(() => projects.id, { onDelete: "cascade" }),
		name: text("name").notNull(),
		status: text("status").notNull().default("draft"),
		draftContent: jsonb("draft_content").notNull(),
		publishedVersion: integer("published_version"),
		createdAt: timestamp("created_at", { withTimezone: true })
			.notNull()
			.defaultNow(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.notNull()
			.defaultNow(),
	},
	(table) => [
		check(
			"tours_status_allowed",
			sql`${table.status} IN ('draft', 'published', 'paused', 'archived')`,
		),
		check(
			"tours_draft_has_steps",
			sql`jsonb_typeof(${table.draftContent} -> 'steps') = 'array'`,
		),
		check(
			"tours_published_has_version",
			sql`${table.status} <> 'published' OR ${table.publishedVersion} IS NOT NULL`,
		),
		index("tours_project_id_status_idx").on(table.projectId, table.status),
	],
);

export const tourVersions = pgTable(
	"tour_versions",
	{
		tourId: uuid("tour_id")
			.notNull()
			.references(() => tours.id, { onDelete: "cascade" }),
		version: integer("version").notNull(),
		content: jsonb("content").notNull(),
		publishedAt: timestamp("published_at", { withTimezone: true })
			.notNull()
			.defaultNow(),
		publishedBy: uuid("published_by").references(() => users.id, {
			onDelete: "set null",
		}),
	},
	(table) => [
		primaryKey({ columns: [table.tourId, table.version] }),
		check("tour_versions_version_positive", sql`${table.version} > 0`),
	],
);

export const extensionTokens = pgTable("extension_tokens", {
	id: uuid("id").primaryKey().defaultRandom(),
	token: text("token").notNull().unique(),
	userId: uuid("user_id")
		.notNull()
		.references(() => users.id, { onDelete: "cascade" }),
	label: text("label"),
	lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
	createdAt: timestamp("created_at", { withTimezone: true })
		.notNull()
		.defaultNow(),
});

export const tourEvents = pgTable(
	"tour_events",
	{
		id: bigserial("id", { mode: "bigint" }).primaryKey(),
		projectId: uuid("project_id")
			.notNull()
			.references(() => projects.id, { onDelete: "cascade" }),
		tourId: uuid("tour_id").notNull(),
		tourVersion: integer("tour_version").notNull(),
		stepId: text("step_id"),
		type: text("type").notNull(),
		choiceId: text("choice_id"),
		sessionId: text("session_id").notNull(),
		occurredAt: timestamp("occurred_at", { withTimezone: true })
			.notNull()
			.defaultNow(),
	},
	(table) => [
		check(
			"tour_events_type_allowed",
			sql`${table.type} IN ('start', 'step_view', 'choice', 'complete', 'dismiss', 'target_lost')`,
		),
		index("tour_events_project_tour_occurred_idx").on(
			table.projectId,
			table.tourId,
			table.occurredAt,
		),
		index("tour_events_session_id_idx").on(table.sessionId),
	],
);