/* IMPORT */
import {
  boolean,
  integer,
  pgSchema,
  serial,
  text,
  timestamp,
  varchar,
} from 'drizzle-orm/pg-core';
import { eq, relations, sql } from 'drizzle-orm';
import { users } from '../users';
import { vendors } from '../vendors';

// ----------------------------------------------------------------------------------------------------

/* TABLE SCHEMATA */
const risksSchema = pgSchema('risks');
const riskEvents = risksSchema.table('risk_events', {
  id: serial('id').primaryKey(),
  vendorId: integer('vendor_id')
    .notNull()
    .references(() => vendors.id, { onDelete: 'cascade' }),
  provider: varchar('provider', { length: 128 }).notNull(),
  eventType: varchar('event_type', { length: 50 }).notNull(),
  content: text('content'),
  eventStatus: boolean('event_status').default(true).notNull(),
  managerId: integer('manager_id')
    .references(() => users.id, { onDelete: 'set null' }),
  adminComment: text('admin_comment'),
  occuredAt: timestamp('occurred_at').defaultNow().notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  createdBy: integer('created_by')
    .references(() => users.id, { onDelete: 'set null' }),
  updatedBy: integer('updated_by')
    .references(() => users.id, { onDelete: 'set null' }),
});

// ----------------------------------------------------------------------------------------------------

/* VIEWS */
const risksView = risksSchema.view('risks_view').as((qb) => {

  return qb
    .select({
      id: riskEvents.id,
      eventType: riskEvents.eventType,
      vendorId: riskEvents.vendorId,
      vendorCode: vendors.vendorCode,
      vendorName: vendors.vendorName,
      businessNumber: vendors.taxId,
      provider: riskEvents.provider,
      content: riskEvents.content,
      prevRatingTotal: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '종합등급' THEN 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at < ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('prev_rating_total'),
      curRatingTotal: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '종합등급' THEN 
            ${riskEvents.content}
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '종합등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('cur_rating_total'),
      prevRatingCredit: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '신용등급' THEN 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at < ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('prev_rating_credit'),
      curRatingCredit: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '신용등급' THEN 
            ${riskEvents.content}
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '신용등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('cur_rating_credit'),
      prevRatingCashflow: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '현금흐름등급' THEN 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at < ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('prev_rating_cashflow'),
      curRatingCashflow: sql`
        CASE 
          WHEN ${riskEvents.eventType} = '현금흐름등급' THEN 
            ${riskEvents.content}
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = '현금흐름등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('cur_rating_cashflow'),
      prevRatingWatch: sql`
        CASE 
          WHEN ${riskEvents.eventType} = 'WATCH등급' THEN 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at < ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('prev_rating_watch'),
      curRatingWatch: sql`
        CASE 
          WHEN ${riskEvents.eventType} = 'WATCH등급' THEN 
            ${riskEvents.content}
          ELSE 
            COALESCE((
              SELECT content 
              FROM ${risksSchema}.risk_events re2 
              WHERE re2.vendor_id = ${riskEvents.vendorId} 
                AND re2.event_type = 'WATCH등급' 
                AND re2.occurred_at <= ${riskEvents.occuredAt}
              ORDER BY re2.occurred_at DESC 
              LIMIT 1
            ), '-')
        END
      `.as('cur_rating_watch'),
      eventStatus: riskEvents.eventStatus,
      managerId: riskEvents.managerId,
      managerName: users.name,
      adminComment: riskEvents.adminComment,
      occuredAt: riskEvents.occuredAt,
    })
    .from(riskEvents)
    .leftJoin(vendors, eq(vendors.id, riskEvents.vendorId))
    .leftJoin(users, eq(users.id, riskEvents.managerId))
});

// ----------------------------------------------------------------------------------------------------

/* RELATIONS */
const riskEventsRelations = relations(riskEvents, ({ one }) => ({
  vendor: one(vendors, {
    fields: [riskEvents.vendorId],
    references: [vendors.id],
  }),
  manager: one(users, {
    fields: [riskEvents.managerId],
    references: [users.id],
  }),
  createdByUser: one(users, {
    fields: [riskEvents.createdBy],
    references: [users.id],
  }),
  updatedByUser: one(users, {
    fields: [riskEvents.updatedBy],
    references: [users.id],
  }),
}));

// ----------------------------------------------------------------------------------------------------

/* TYPES */
type RiskEvents = typeof riskEvents.$inferSelect;
type NewRiskEvents = typeof riskEvents.$inferInsert;
type RisksView = typeof risksView.$inferSelect;

// ----------------------------------------------------------------------------------------------------

/* Export */
export {
  risksSchema,
  risksView,
  riskEvents,
  riskEventsRelations,
  type NewRiskEvents,
  type RiskEvents,
  type RisksView,
};