import { integer, sqliteTable, text, index, uniqueIndex, customType, type AnySQLiteColumn } from "drizzle-orm/sqlite-core";
import { defineRelations } from 'drizzle-orm';
import dayjs, { type Dayjs } from "dayjs";
import { COMPONENT_TYPE_CLIENT, COMPONENT_TYPE_SOURCE, type ErrorLike, type PlayObject, type QueueContext } from "../../../../../core/Atomic.ts";
import { asPlayCheap } from "../../../../../core/PlayMarshalUtils.ts";
import type {ExternalMetadataTerm, PlayTransformPartsConfig, SearchAndReplaceTerm} from "../../../../../core/Transform.ts";
import type {JobRangeCount, JobRangeTime} from "../../../infrastructure/Job.ts";
import { serializeError, deserializeError } from "serialize-error";
import { generatePlayUid } from "../../../../../core/StringUtils.ts";

const DayjsTimestamp = customType<
  {
    data: Dayjs;
    driverData: number;
  }
>({
  dataType() {
    return 'number'
  },
  toDriver(value: Dayjs): number {
    return value.valueOf();
  },
  fromDriver(value: number): Dayjs {
    return dayjs(value);
  },
});

const PlayJson = customType<
  {
    data: PlayObject;
    driverData: string;
  }
>({
  dataType() {
    return 'text'
  },
  toDriver(value: PlayObject): string {
    const {
      id,
      uid,
      ...rest
    } = value;
    return JSON.stringify(rest);
  },
  fromDriver(value: string): PlayObject {
    return asPlayCheap(JSON.parse(value));
  },
});

const ErrorLikeJson = customType<
  {
    data: ErrorLike;
    driverData: string;
  }
>({
  dataType() {
    return 'text'
  },
  toDriver(value: ErrorLike): string {
    return JSON.stringify(serializeError(value));
  },
  fromDriver(value: string): ErrorLike {
    return deserializeError(JSON.parse(value));
  },
});


export const plays = sqliteTable("plays", {
  id: integer().primaryKey(),
  uid: text({ length: 30 }).notNull().unique().$defaultFn(() => generatePlayUid()),
  componentId: integer().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  error: ErrorLikeJson('error'),
  playedAt: DayjsTimestamp('playedAt'),
  seenAt: DayjsTimestamp('seenAt'),
  updatedAt: DayjsTimestamp('updatedAt').notNull().$defaultFn(() => dayjs()).$onUpdate(() => dayjs()),
  play: PlayJson('play').notNull(), //  text({ mode: 'json' }).notNull().$type<PlayObject>(),
  state: text({enum: ['queued','discovered','discarded','scrobbled','failed','duped']}).notNull(),
  // https://orm.drizzle.team/docs/indexes-constraints#foreign-key
  parentId: integer().references((): AnySQLiteColumn => plays.id, {onDelete: 'set null', onUpdate: 'cascade'}),
  jobId: integer().references(() => jobs.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  playHash: text(),
  mbidIdentifier: text(),
  compacted: text()
}, (table) => [
  index("play_parent_id_idx").on(table.parentId),
  index("play_component_id_idx").on(table.componentId),
  uniqueIndex("play_uid_idx").on(table.uid),
  index("play_playedAt_idx").on(table.playedAt),
  index("play_seenAt_idx").on(table.seenAt)
]);

export const playInputs = sqliteTable("play_inputs", {
  id: integer({ mode: 'number' }).primaryKey(),
  playId: integer().notNull().references(() => plays.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  data: text({ mode: 'json' }).$type<object>(),
  play: PlayJson('play').notNull(),//text({ mode: 'json' }).notNull().$type<PlayObject>(),
  playHash: text(),
  createdAt: DayjsTimestamp('createdAt').$defaultFn(() => dayjs())
}, (table) => [
  uniqueIndex('play_input_id_idx').on(table.playId)
]);

export const playsHistorical = sqliteTable("plays_historical", {
  id: integer().primaryKey(),
  uid: text({ length: 200 }).unique(),
  componentId: integer().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  playedAt: DayjsTimestamp('playedAt'),
  seenAt: DayjsTimestamp('seenAt'),
  play: PlayJson('play').notNull(),
  playHash: text(),
  mbidIdentifier: text()
}, (table) => [
  index("play_historical_component_id_idx").on(table.componentId),
  uniqueIndex("play_historical_uid_idx").on(table.uid),
  index("play_historical_playedAt_idx").on(table.playedAt)
]);

// export const playParentRelations = defineRelations({plays}, (r) => ({
//   plays: {
//     parent: r.one.plays({
//       from: r.plays.parentId,
//       to: r.plays.id
//     }),
//     children: r.many.plays()
//   }
// }))


// export const playInputRelations = defineRelations({ plays, playInputs }, (r) => ({
//   plays: {
//     input: r.one.playInputs({
//       from: r.plays.id,
//       to: r.playInputs.playId,
//       optional: false,
//     })
//   }
// }));

export const queueStates = sqliteTable("play_queue_states", {
  id: integer({ mode: 'number' }).primaryKey(),
  playId: integer().notNull().references(() => plays.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  componentId: integer().notNull().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  queueName: text({length: 50}).notNull(),
  queueStatus: text({enum: ['queued','completed','failed']}).notNull().default('queued'),
  retries: integer().notNull().default(0),
  error: ErrorLikeJson('error'),
  context: text({mode: 'json'}).$type<QueueContext & {isRetry?: boolean}>(),
  createdAt: DayjsTimestamp('createdAt').notNull().$defaultFn(() => dayjs()),
  updatedAt: DayjsTimestamp('updatedAt').notNull().$defaultFn(() => dayjs()).$onUpdate(() => dayjs())
}, (table) => [
  index('play_queue_state_id_idx').on(table.playId)
]);

// export const playQueueRelations = defineRelations({ plays, queueStates }, (r) => ({
//   plays: {
//     queueStates: r.many.queueStates()
//   },
//   queueStates: {
//     play: r.one.plays({
//       from: r.queueStates.playId,
//       to: r.plays.id
//     })
//   }
// }));

export const components = sqliteTable("components", {
  id: integer({ mode: 'number' }).primaryKey(),
  // user-provided id
  uid: text({ length: 200 }).notNull(),
  mode: text({enum: [COMPONENT_TYPE_SOURCE,COMPONENT_TYPE_CLIENT]}).notNull(),
  // spotify, lastfm, etc...
  type: text({length: 50}).notNull(),
  // vanity display name
  // used as uid if no user-provided id
  name: text().notNull(),
  // number of discovered/scrobbled plays found in real time
  countLive: integer().notNull().default(0),
  // number of discovered/scrobbled plays from backlog/jobs
  countNonLive: integer().notNull().default(0),
  createdAt: DayjsTimestamp('createdAt').$defaultFn(() => dayjs()),
  lastReadyAt: DayjsTimestamp('lastReadyAt'),
  lastActiveAt: DayjsTimestamp('lastActiveAt')
},
(table) => [
  uniqueIndex('uid_mode_type_idx').on(table.uid,table.mode,table.type)
]);

export const componentMigrations = sqliteTable("component_migrations", {
  id: integer({ mode: 'number' }).primaryKey(),
  componentId: integer().notNull().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  name: text().notNull(),
  success: integer({mode: 'boolean'}),
  error: ErrorLikeJson('error'),
  attemptedAt: DayjsTimestamp('attemptedAt').notNull().$defaultFn(() => dayjs()),
});

export const jobs = sqliteTable("jobs", {
  id: integer({ mode: 'number' }).primaryKey(),
  componentFromId: integer().notNull().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  componentToId: integer().notNull().references(() => components.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  name: text({length: 50}).notNull(),
  status: text({enum: ['idle','completed','failed','processing']}).notNull().default('idle'),
  retries: integer().notNull().default(0),
  error: ErrorLikeJson('error'),
  transformOptions: text({ mode: 'json' }).$type<PlayTransformPartsConfig<SearchAndReplaceTerm[] | ExternalMetadataTerm>>(),
  initialParameters: text({ mode: 'json' }).$type<JobRangeCount | JobRangeTime>(),
  cursor: text({ mode: 'json' }),
  total: integer(),
  imported: integer().notNull().default(0),
  scrobbled: integer().notNull().default(0),
  createdAt: DayjsTimestamp('createdAt').notNull().$defaultFn(() => dayjs()),
  updatedAt: DayjsTimestamp('updatedAt').notNull().$defaultFn(() => dayjs()).$onUpdate(() => dayjs()),
  completedAt: DayjsTimestamp('completedAt')
});

export const playEvents = sqliteTable("play_events", {
  id: integer({ mode: 'number' }).primaryKey(),
  playId: integer().notNull().references(() => plays.id, {onDelete: 'cascade', onUpdate: 'cascade'}),
  eventName: text({length: 50}).notNull(),
  data: text({ mode: 'json' }),
  error: ErrorLikeJson('error'),
  createdAt: DayjsTimestamp('createdAt').notNull().$defaultFn(() => dayjs()),
}, (table) => [
  index('play_event_id_idx').on(table.playId)
]);

const playRelations = defineRelations({ plays, queueStates, playEvents, playInputs, components, jobs, componentMigrations,playsHistorical }, (r) => ({
  plays: {
    queueStates: r.many.queueStates(),
    input: r.one.playInputs({
      from: r.plays.id,
      to: r.playInputs.playId,
      optional: false,
    }),
    parent: r.one.plays({
      from: r.plays.parentId,
      to: r.plays.id
    }),
    children: r.many.plays(),
    component: r.one.components({
      from: r.plays.componentId,
      to: r.components.id,
      optional: true
    }),
    job: r.one.jobs({
      from: r.plays.jobId,
      to: r.jobs.id,
      optional: true
    }),
    events: r.many.playEvents(),
  },
  playsHistorical: {
    component: r.one.components({
      from: r.playsHistorical.componentId,
      to: r.components.id,
    }),
  },
  queueStates: {
    play: r.one.plays({
      from: r.queueStates.playId,
      to: r.plays.id
    }),
    component: r.one.components({
      from: r.queueStates.componentId,
      to: r.components.id
    })
  },
  components: {
    plays: r.many.plays(),
    playsHistorical: r.many.plays(),
    queueStates: r.many.queueStates(),
    migrations: r.many.componentMigrations(),
  },
  componentMigrations: {
    component: r.one.components({
      from: r.componentMigrations.componentId,
      to: r.components.id
    })
  },
  jobs: {
    plays: r.many.plays()
  },
  playEvents: {
    play: r.one.plays({
      from: r.playEvents.playId,
      to: r.plays.id
    }),
  },
}));

export const relations = playRelations;

export const getConfigByTableName = <T extends TableName>(name: T) => {
  switch(name) {
    case 'plays':
      return plays;
    case 'playsHistorical':
      return playsHistorical;
    case 'components':
      return components;
    case 'playInputs':
      return playInputs;
    case 'playEvents':
      return playEvents;
    case 'queueStates':
      return queueStates;
    case 'componentMigrations':
      return componentMigrations;
    case 'jobs':
      return jobs;
  }
}

export const schema = {playInputs, plays, playEvents, components, componentMigrations, queueStates, jobs};

export type TSchema = typeof relations;
export type Schema = typeof schema;
export type TableName = keyof TSchema;