import { z } from 'zod';

export enum ColumnType {
  STRING = 'string',
  DATE = 'date',
  DATETIME = 'datetime',
  INTEGER = 'integer',
  FLOAT = 'float',
  BOOLEAN = 'boolean',
  UUID = 'uuid',
  JSON = 'json',
}

// Postgres supports the same referential actions for ON UPDATE as ON DELETE;
// introspection can return SET NULL / SET DEFAULT, so the schema must accept them.
export const onUpdateActionSchema = z.enum([
  'CASCADE',
  'SET NULL',
  'SET DEFAULT',
  'RESTRICT',
  'NO ACTION',
]);
export const onDeleteActionSchema = z.enum([
  'CASCADE',
  'SET NULL',
  'SET DEFAULT',
  'RESTRICT',
  'NO ACTION',
]);

export const columnTypeSchema = z.enum([
  ColumnType.STRING,
  ColumnType.DATE,
  ColumnType.DATETIME,
  ColumnType.INTEGER,
  ColumnType.FLOAT,
  ColumnType.BOOLEAN,
  ColumnType.UUID,
  ColumnType.JSON,
]);

export const foreignKeyReferenceSchema = z.object({
  sourceColumn: z.string().min(1, 'Source column cannot be empty'),
  referenceColumn: z.string().min(1, 'Reference column cannot be empty'),
});

// A foreign key is a table-level constraint: one entity per constraint, with an
// ordered list of (source -> reference) column pairs. Composite keys are a single
// entity with multiple pairs, never duplicated across columns.
export const foreignKeySchema = z.object({
  // Constraint identity. Populated when reading schema; derived by the backend on create.
  constraintName: z.string().optional(),
  referenceTable: z.string().min(1, 'Target table cannot be empty'),
  referenceColumns: z
    .array(foreignKeyReferenceSchema)
    .min(1, 'At least one column mapping is required'),
  onDelete: onDeleteActionSchema,
  onUpdate: onUpdateActionSchema,
});

export const columnSchema = z.object({
  columnName: z
    .string()
    .min(1, 'Column name cannot be empty')
    .max(64, 'Column name must be less than 64 characters'),
  type: z.union([columnTypeSchema, z.string()]),
  defaultValue: z.string().optional(),
  isPrimaryKey: z.boolean().optional(),
  isNullable: z.boolean(),
  isUnique: z.boolean(),
});

export const tableSchema = z.object({
  schemaName: z.string().optional(),
  tableName: z
    .string()
    .min(1, 'Table name cannot be empty')
    .max(64, 'Table name must be less than 64 characters'),
  columns: z.array(columnSchema).min(1, 'At least one column is required'),
  // Foreign keys are table-level, one entry per constraint.
  foreignKeys: z.array(foreignKeySchema).optional(),
  recordCount: z.number().optional(),
  createdAt: z.string().optional(),
  updatedAt: z.string().optional(),
});

export type TableSchema = z.infer<typeof tableSchema>;
export type ColumnSchema = z.infer<typeof columnSchema>;
export type ForeignKeyReferenceSchema = z.infer<typeof foreignKeyReferenceSchema>;
export type ForeignKeySchema = z.infer<typeof foreignKeySchema>;
export type OnUpdateActionSchema = z.infer<typeof onUpdateActionSchema>;
export type OnDeleteActionSchema = z.infer<typeof onDeleteActionSchema>;

export const databaseSchemaInfoSchema = z.object({
  name: z.string(),
  isProtected: z.boolean(),
});

export type DatabaseSchemaInfo = z.infer<typeof databaseSchemaInfoSchema>;

// Database Metadata Object Schemas
export const databaseFunctionSchema = z.object({
  functionName: z.string(),
  functionDef: z.string(),
  kind: z.string(),
});

export const databaseIndexSchema = z.object({
  tableName: z.string(),
  indexName: z.string(),
  indexDef: z.string(),
  isUnique: z.boolean().nullable(),
  isPrimary: z.boolean().nullable(),
});

export const databasePolicySchema = z.object({
  tableName: z.string(),
  policyName: z.string(),
  cmd: z.string(),
  roles: z.array(z.string()),
  qual: z.string().nullable(),
  withCheck: z.string().nullable(),
});

export const databaseTriggerSchema = z.object({
  tableName: z.string(),
  triggerName: z.string(),
  actionTiming: z.string(),
  eventManipulation: z.string(),
  actionOrientation: z.string(),
  actionCondition: z.string().nullable(),
  actionStatement: z.string(),
});

export const migrationSchema = z.object({
  version: z
    .string()
    .regex(
      /^\d{1,64}$/,
      'Migration version must be a numeric string of at most 64 digits (e.g. 0001 or 20260418091500).'
    ),
  name: z.string().min(1),
  statements: z.array(z.string()).min(1),
  createdAt: z.string(),
});

export const databaseBackupSchema = z.object({
  id: z.string(),
  name: z.string().nullable(),
  triggerSource: z.enum(['manual', 'scheduled']),
  status: z.enum(['running', 'completed', 'failed']),
  sizeBytes: z.number().nullable(),
  errorMessage: z.string().nullable(),
  createdAt: z.string(),
  completedAt: z.string().nullable(),
  createdBy: z.string().nullable(),
});

export type DatabaseFunction = z.infer<typeof databaseFunctionSchema>;
export type DatabaseIndex = z.infer<typeof databaseIndexSchema>;
export type DatabasePolicy = z.infer<typeof databasePolicySchema>;
export type DatabaseTrigger = z.infer<typeof databaseTriggerSchema>;
export type Migration = z.infer<typeof migrationSchema>;
export type DatabaseBackup = z.infer<typeof databaseBackupSchema>;
