import { pgTable, serial, timestamp, varchar, text, index, boolean, integer } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"
import { createSchemaFactory } from "drizzle-zod"
import { z } from "zod"

// 系统健康检查表 - 保留，禁止删除
export const healthCheck = pgTable("health_check", {
	id: serial().notNull(),
	updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(),
});

// 管理员表
export const admins = pgTable(
	"admins",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		username: varchar("username", { length: 50 }).notNull().unique(),
		password: varchar("password", { length: 255 }).notNull(),
		name: varchar("name", { length: 100 }).notNull(),
		role: varchar("role", { length: 20 }).notNull().default('admin'), // admin, super_admin
		isActive: boolean("is_active").notNull().default(true),
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("admins_username_idx").on(table.username),
	]
);

// 订单状态枚举
export const ORDER_STATUS = {
	NEW_INQUIRY: '新询价',
	PENDING_CONFIRM: '待确认',
	LOADING_PLAN: '装车方案设计中',
	CONFIRMED: '确认',
	IN_TRANSIT_CN: '运输中（中国境内）',
	MANZHOULI_STORAGE: '满洲里仓储中',
	CUSTOMS_UNFINISHED: '清关未完成',
	IN_TRANSIT_RU: '运输中（俄罗斯境内）',
	DELIVERED: '配送完成',
} as const

export type OrderStatusType = typeof ORDER_STATUS[keyof typeof ORDER_STATUS]

// 客户表
export const customers = pgTable(
	"customers",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		name: varchar("name", { length: 100 }).notNull(),
		phone: varchar("phone", { length: 20 }).notNull().unique(),
		password: varchar("password", { length: 255 }).notNull(),
		email: varchar("email", { length: 128 }),
		company: varchar("company", { length: 200 }),
		isAdmin: boolean("is_admin").notNull().default(false),
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("customers_phone_idx").on(table.phone),
	]
);

// 网站内容表（服务、轮播图等）
export const siteContent = pgTable(
	"site_content",
	{
		id: varchar("id", { length: 50 }).primaryKey(),
		contentType: varchar("content_type", { length: 50 }).notNull(),
		title: varchar("title", { length: 200 }).notNull(),
		description: text("description"),
		content: text("content"), // JSON字符串，存储完整内容
		icon: varchar("icon", { length: 50 }),
		category: varchar("category", { length: 50 }),
		sortOrder: integer("sort_order").default(0),
		isActive: boolean("is_active").default(true),
		// 新增字段：支持图片和文档
		coverImage: varchar("cover_image", { length: 500 }), // 封面图片URL
		attachments: text("attachments"), // JSON字符串，存储附件列表
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("site_content_type_idx").on(table.contentType),
		index("site_content_sort_idx").on(table.sortOrder),
	]
);

// 订单表
export const orders = pgTable(
	"orders",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		// 订单号（递增数字，如001、002...）
		orderNumber: varchar("order_number", { length: 20 }).unique(),
		// 关联客户（可选，支持游客下单）
		customerId: varchar("customer_id", { length: 36 }),
		// 联系人信息
		contactName: varchar("contact_name", { length: 100 }).notNull(),
		contactPhone: varchar("contact_phone", { length: 20 }).notNull(),
		contactEmail: varchar("contact_email", { length: 128 }),
		// 运输信息
		originCity: varchar("origin_city", { length: 100 }).notNull(), // 起运地（中国城市）
		destinationCity: varchar("destination_city", { length: 100 }).notNull(), // 目的地（俄罗斯城市）
		customsCity: varchar("customs_city", { length: 100 }).notNull(), // 清关地点（俄罗斯城市）
		// 订单状态
		status: varchar("status", { length: 20 }).notNull().default('新询价'),
		// 备注
		notes: text("notes"),
		// 消息已读时间戳
		customerLastReadAt: timestamp("customer_last_read_at", { withTimezone: true }),
		adminLastReadAt: timestamp("admin_last_read_at", { withTimezone: true }),
		// 时间戳
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("orders_customer_id_idx").on(table.customerId),
		index("orders_status_idx").on(table.status),
		index("orders_created_at_idx").on(table.createdAt),
	]
);

// 消息类型枚举
export const MESSAGE_TYPE = {
	TEXT: 'text',
	IMAGE: 'image',
	FILE: 'file',
} as const

export type MessageTypeType = typeof MESSAGE_TYPE[keyof typeof MESSAGE_TYPE]

// 发送者类型枚举
export const SENDER_TYPE = {
	CUSTOMER: 'customer',
	ADMIN: 'admin',
} as const

export type SenderTypeType = typeof SENDER_TYPE[keyof typeof SENDER_TYPE]

// 订单消息表
export const orderMessages = pgTable(
	"order_messages",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		// 关联订单
		orderId: varchar("order_id", { length: 36 }).notNull(),
		// 发送者信息
		senderType: varchar("sender_type", { length: 20 }).notNull(), // customer 或 admin
		senderId: varchar("sender_id", { length: 36 }).notNull(), // 客户ID 或 管理员ID
		senderName: varchar("sender_name", { length: 100 }), // 发送者名称
		// 消息内容
		messageType: varchar("message_type", { length: 20 }).notNull().default('text'), // text, image, file
		content: text("content"), // 文字内容或文件说明
		// 附件信息
		fileUrl: varchar("file_url", { length: 500 }), // 文件/图片URL
		fileName: varchar("file_name", { length: 255 }), // 原始文件名
		fileSize: varchar("file_size", { length: 20 }), // 文件大小
		// 时间戳
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("order_messages_order_id_idx").on(table.orderId),
		index("order_messages_created_at_idx").on(table.createdAt),
	]
);

// 案例展示表
export const cases = pgTable(
	"cases",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		title: varchar("title", { length: 200 }).notNull(),
		description: text("description"),
		// 图片列表（JSON数组）
		images: text("images").notNull(), // JSON数组，存储图片URL列表
		// 分类标签
		category: varchar("category", { length: 50 }), // 如：铁路运输、公路运输、清关服务等
		// 排序和状态
		sortOrder: integer("sort_order").default(0),
		isActive: boolean("is_active").notNull().default(true),
		// 时间戳
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("cases_category_idx").on(table.category),
		index("cases_sort_idx").on(table.sortOrder),
		index("cases_is_active_idx").on(table.isActive),
	]
);

// 常见问题表
export const faqs = pgTable(
	"faqs",
	{
		id: varchar("id", { length: 36 })
			.primaryKey()
			.default(sql`gen_random_uuid()`),
		question: text("question").notNull(),
		answer: text("answer").notNull(),
		// 分类
		category: varchar("category", { length: 50 }), // 如：运输问题、清关问题、费用问题等
		// 排序和状态
		sortOrder: integer("sort_order").default(0),
		isActive: boolean("is_active").notNull().default(true),
		// 时间戳
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("faqs_category_idx").on(table.category),
		index("faqs_sort_idx").on(table.sortOrder),
		index("faqs_is_active_idx").on(table.isActive),
	]
);

// 多语言内容表
export const i18nContent = pgTable(
	"i18n_content",
	{
		id: varchar("id", { length: 50 }).primaryKey(), // 如：home.heroTitle
		groupName: varchar("group_name", { length: 50 }).notNull(), // 分组：home, about, services等
		keyName: varchar("key_name", { length: 100 }).notNull(), // 键名
		contentZh: text("content_zh"), // 中文内容
		contentRu: text("content_ru"), // 俄语内容
		description: varchar("description", { length: 255 }), // 描述说明
		sortOrder: integer("sort_order").default(0),
		createdAt: timestamp("created_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
		updatedAt: timestamp("updated_at", { withTimezone: true })
			.defaultNow()
			.notNull(),
	},
	(table) => [
		index("i18n_content_group_idx").on(table.groupName),
	]
);

// 使用 createSchemaFactory 配置 date coercion
const { createInsertSchema: createCoercedInsertSchema } = createSchemaFactory({
	coerce: { date: true },
});

// 客户注册的 Zod schema
export const registerCustomerSchema = z.object({
	name: z.string().min(2, '姓名至少2个字符').max(100),
	phone: z.string().regex(/^1[3-9]\d{9}$/, '请输入有效的手机号'),
	password: z.string().min(6, '密码至少6个字符').max(50),
	email: z.string().email('请输入有效的邮箱').optional().or(z.literal('')),
	company: z.string().max(200).optional().or(z.literal('')),
});

// 客户登录的 Zod schema
export const loginCustomerSchema = z.object({
	phone: z.string().min(1, '请输入手机号'),
	password: z.string().min(1, '请输入密码'),
});

// 创建订单的 Zod schema
export const insertOrderSchema = createCoercedInsertSchema(orders).pick({
	contactName: true,
	contactPhone: true,
	contactEmail: true,
	originCity: true,
	destinationCity: true,
	customsCity: true,
	notes: true,
});

// 更新订单状态的 Zod schema
export const updateOrderStatusSchema = z.object({
	status: z.enum([
		ORDER_STATUS.NEW_INQUIRY,
		ORDER_STATUS.PENDING_CONFIRM,
		ORDER_STATUS.LOADING_PLAN,
		ORDER_STATUS.CONFIRMED,
		ORDER_STATUS.IN_TRANSIT_CN,
		ORDER_STATUS.MANZHOULI_STORAGE,
		ORDER_STATUS.CUSTOMS_UNFINISHED,
		ORDER_STATUS.IN_TRANSIT_RU,
		ORDER_STATUS.DELIVERED,
	]),
});

// 创建消息的 Zod schema
export const createMessageSchema = z.object({
	orderId: z.string().min(1, '订单ID不能为空'),
	messageType: z.enum(['text', 'image', 'file']).default('text'),
	content: z.string().optional(),
	fileUrl: z.string().optional(),
	fileName: z.string().optional(),
	fileSize: z.string().optional(),
});

// 管理员登录的 Zod schema
export const loginAdminSchema = z.object({
	username: z.string().min(1, '请输入用户名'),
	password: z.string().min(1, '请输入密码'),
});

// 创建/更新案例的 Zod schema
export const upsertCaseSchema = z.object({
	title: z.string().min(1, '标题不能为空').max(200),
	description: z.string().optional(),
	images: z.array(z.string()).min(1, '至少上传一张图片'),
	category: z.string().optional(),
	sortOrder: z.number().optional(),
	isActive: z.boolean().optional(),
});

// 创建/更新常见问题的 Zod schema
export const upsertFaqSchema = z.object({
	question: z.string().min(1, '问题不能为空'),
	answer: z.string().min(1, '答案不能为空'),
	category: z.string().optional(),
	sortOrder: z.number().optional(),
	isActive: z.boolean().optional(),
});

// 更新多语言内容的 Zod schema
export const updateI18nSchema = z.object({
	contentZh: z.string().optional(),
	contentRu: z.string().optional(),
});

// 批量更新多语言内容的 Zod schema
export const batchUpdateI18nSchema = z.array(z.object({
	id: z.string(),
	contentZh: z.string().optional(),
	contentRu: z.string().optional(),
}));

// TypeScript 类型
export type Admin = typeof admins.$inferSelect;
export type LoginAdmin = z.infer<typeof loginAdminSchema>;
export type Customer = typeof customers.$inferSelect;
export type RegisterCustomer = z.infer<typeof registerCustomerSchema>;
export type LoginCustomer = z.infer<typeof loginCustomerSchema>;
export type Order = typeof orders.$inferSelect;
export type InsertOrder = z.infer<typeof insertOrderSchema>;
export type UpdateOrderStatus = z.infer<typeof updateOrderStatusSchema>;
export type OrderMessage = typeof orderMessages.$inferSelect;
export type CreateMessage = z.infer<typeof createMessageSchema>;
export type Case = typeof cases.$inferSelect;
export type UpsertCase = z.infer<typeof upsertCaseSchema>;
export type Faq = typeof faqs.$inferSelect;
export type UpsertFaq = z.infer<typeof upsertFaqSchema>;
export type I18nContent = typeof i18nContent.$inferSelect;
export type UpdateI18n = z.infer<typeof updateI18nSchema>;
export type BatchUpdateI18n = z.infer<typeof batchUpdateI18nSchema>;
