import { sql } from 'drizzle-orm'
import {
  bigserial, boolean, date, index, integer, jsonb, numeric, pgSchema, primaryKey, smallint, text, timestamp, uniqueIndex,
} from 'drizzle-orm/pg-core'

/**
 * Catalog Data Layer. Written only by apps/sync; read via apps/catalog-api.
 * Deliberately not a mirror of Magento's EAV tables — see ARCHITECTURE.md §3.
 */
export const catalog = pgSchema('catalog')
/** Operational data: sync cursors/runs and the MCP audit log. */
export const ops = pgSchema('ops')

const ts = (name: string) => timestamp(name, { withTimezone: true, mode: 'date' })
const money = (name: string) => numeric(name, { precision: 20, scale: 4, mode: 'number' })
const syncedAt = () => ts('synced_at').notNull().defaultNow()

export type TierPrice = { customerGroupId: number; websiteId: number | null; qty: number; value: number; percentage: number | null }
export type AttributeOption = { value: string; label: string }
export type AttributeValues = Record<string, string | string[] | null>

// ── Stores ─────────────────────────────────────────────────────────────────

export const stores = catalog.table('stores', {
  storeId: integer('store_id').primaryKey(),
  code: text('code').notNull().unique(),
  name: text('name').notNull(),
  websiteId: integer('website_id').notNull(),
  websiteCode: text('website_code'),
  groupId: integer('group_id').notNull(),
  locale: text('locale'),
  baseCurrency: text('base_currency'),
  displayCurrency: text('display_currency'),
  timezone: text('timezone'),
  baseUrl: text('base_url'),
  mediaUrl: text('media_url'),
  isActive: boolean('is_active').notNull(),
  syncedAt: syncedAt(),
})

// ── Attributes ─────────────────────────────────────────────────────────────

export const attributes = catalog.table('attributes', {
  attributeCode: text('attribute_code').primaryKey(),
  attributeId: integer('attribute_id').notNull(),
  label: text('label'),
  frontendInput: text('frontend_input').notNull(),
  isRequired: boolean('is_required').notNull(),
  isUserDefined: boolean('is_user_defined').notNull(),
  scope: text('scope'),
  options: jsonb('options').$type<AttributeOption[]>().notNull().default([]),
  syncedAt: syncedAt(),
})

export const attributeSets = catalog.table('attribute_sets', {
  attributeSetId: integer('attribute_set_id').primaryKey(),
  name: text('name').notNull(),
  syncedAt: syncedAt(),
})

export const attributeSetAttributes = catalog.table(
  'attribute_set_attributes',
  {
    attributeSetId: integer('attribute_set_id').notNull().references(() => attributeSets.attributeSetId, { onDelete: 'cascade' }),
    attributeCode: text('attribute_code').notNull(),
    isRequired: boolean('is_required').notNull(),
  },
  t => [primaryKey({ columns: [t.attributeSetId, t.attributeCode] })],
)

// ── Categories ─────────────────────────────────────────────────────────────

export const categories = catalog.table(
  'categories',
  {
    categoryId: integer('category_id').primaryKey(),
    parentId: integer('parent_id').notNull(),
    path: text('path').notNull(),
    level: smallint('level').notNull(),
    position: integer('position').notNull(),
    syncedAt: syncedAt(),
  },
  t => [index('categories_parent_idx').on(t.parentId)],
)

export const categoryStoreData = catalog.table(
  'category_store_data',
  {
    categoryId: integer('category_id').notNull().references(() => categories.categoryId, { onDelete: 'cascade' }),
    storeId: integer('store_id').notNull(),
    name: text('name').notNull(),
    urlKey: text('url_key'),
    isActive: boolean('is_active').notNull(),
    includeInMenu: boolean('include_in_menu').notNull(),
  },
  t => [primaryKey({ columns: [t.categoryId, t.storeId] })],
)

// ── Products ───────────────────────────────────────────────────────────────

export const products = catalog.table(
  'products',
  {
    productId: integer('product_id').primaryKey(),
    sku: text('sku').notNull(),
    typeId: text('type_id').notNull(),
    attributeSetId: integer('attribute_set_id').notNull(),
    websiteIds: integer('website_ids').array().notNull().default(sql`'{}'::integer[]`),
    price: money('price'),
    specialPrice: money('special_price'),
    specialFrom: date('special_from', { mode: 'string' }),
    specialTo: date('special_to', { mode: 'string' }),
    tierPrices: jsonb('tier_prices').$type<TierPrice[]>().notNull().default([]),
    weight: numeric('weight', { precision: 12, scale: 4, mode: 'number' }),
    magentoCreatedAt: ts('magento_created_at'),
    magentoUpdatedAt: ts('magento_updated_at').notNull(),
    /** Set by nightly reconciliation when Magento no longer returns the product. */
    deletedAt: ts('deleted_at'),
    syncedAt: syncedAt(),
  },
  t => [
    uniqueIndex('products_sku_uidx').on(t.sku),
    index('products_sku_trgm_idx').using('gin', sql`${t.sku} gin_trgm_ops`),
    index('products_updated_idx').on(t.magentoUpdatedAt),
  ],
)

/** Store-scoped product content. store_id 0 = admin/default values; read with fallback to 0. */
export const productStoreData = catalog.table(
  'product_store_data',
  {
    productId: integer('product_id').notNull().references(() => products.productId, { onDelete: 'cascade' }),
    storeId: integer('store_id').notNull(),
    name: text('name').notNull(),
    status: smallint('status').notNull(),
    visibility: smallint('visibility').notNull(),
    urlKey: text('url_key'),
    description: text('description'),
    shortDescription: text('short_description'),
    metaTitle: text('meta_title'),
    metaDescription: text('meta_description'),
    /** Every other custom attribute, raw Magento values (option ids, not labels). */
    attributes: jsonb('attributes').$type<AttributeValues>().notNull().default({}),
    syncedAt: syncedAt(),
  },
  t => [
    primaryKey({ columns: [t.productId, t.storeId] }),
    index('product_store_data_name_trgm_idx').using('gin', sql`${t.name} gin_trgm_ops`),
  ],
)

export const productRelations = catalog.table(
  'product_relations',
  {
    parentId: integer('parent_id').notNull().references(() => products.productId, { onDelete: 'cascade' }),
    /** Child product id; for bundle children Magento only gives the SKU, so child_sku is authoritative. */
    childSku: text('child_sku').notNull(),
    kind: text('kind', { enum: ['configurable', 'bundle', 'grouped'] }).notNull(),
  },
  t => [primaryKey({ columns: [t.parentId, t.childSku, t.kind] }), index('product_relations_child_idx').on(t.childSku)],
)

export const productLinks = catalog.table(
  'product_links',
  {
    productId: integer('product_id').notNull().references(() => products.productId, { onDelete: 'cascade' }),
    linkType: text('link_type').notNull(),
    linkedSku: text('linked_sku').notNull(),
    position: integer('position').notNull().default(0),
  },
  t => [primaryKey({ columns: [t.productId, t.linkType, t.linkedSku] })],
)

export const productCategories = catalog.table(
  'product_categories',
  {
    productId: integer('product_id').notNull().references(() => products.productId, { onDelete: 'cascade' }),
    categoryId: integer('category_id').notNull(),
    position: integer('position').notNull().default(0),
  },
  t => [primaryKey({ columns: [t.productId, t.categoryId] }), index('product_categories_category_idx').on(t.categoryId)],
)

export const productImages = catalog.table(
  'product_images',
  {
    productId: integer('product_id').notNull().references(() => products.productId, { onDelete: 'cascade' }),
    file: text('file').notNull(),
    label: text('label'),
    position: integer('position').notNull().default(0),
    roles: text('roles').array().notNull().default(sql`'{}'::text[]`),
    disabled: boolean('disabled').notNull().default(false),
    mediaType: text('media_type').notNull().default('image'),
  },
  t => [primaryKey({ columns: [t.productId, t.file] })],
)

// ── Inventory ──────────────────────────────────────────────────────────────

export const inventorySourceItems = catalog.table(
  'inventory_source_items',
  {
    sku: text('sku').notNull(),
    sourceCode: text('source_code').notNull(),
    quantity: numeric('quantity', { precision: 14, scale: 4, mode: 'number' }).notNull(),
    inStock: boolean('in_stock').notNull(),
    syncedAt: syncedAt(),
  },
  t => [primaryKey({ columns: [t.sku, t.sourceCode] })],
)

export const inventorySalable = catalog.table(
  'inventory_salable',
  {
    sku: text('sku').notNull(),
    stockId: integer('stock_id').notNull(),
    salableQty: numeric('salable_qty', { precision: 14, scale: 4, mode: 'number' }),
    isSalable: boolean('is_salable'),
    checkedAt: ts('checked_at').notNull().defaultNow(),
  },
  t => [primaryKey({ columns: [t.sku, t.stockId] })],
)

// ── Ops ────────────────────────────────────────────────────────────────────

export const syncState = ops.table('sync_state', {
  /** e.g. `products:default`, `inventory`, `reference`. */
  stream: text('stream').primaryKey(),
  cursor: text('cursor'),
  lastSuccessAt: ts('last_success_at'),
  updatedAt: ts('updated_at').notNull().defaultNow(),
})

export const syncRuns = ops.table(
  'sync_runs',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    job: text('job').notNull(),
    startedAt: ts('started_at').notNull().defaultNow(),
    finishedAt: ts('finished_at'),
    status: text('status', { enum: ['running', 'succeeded', 'failed'] }).notNull().default('running'),
    stats: jsonb('stats').$type<Record<string, number>>().notNull().default({}),
    error: text('error'),
  },
  t => [index('sync_runs_job_started_idx').on(t.job, t.startedAt)],
)

export const auditLog = ops.table(
  'audit_log',
  {
    id: bigserial('id', { mode: 'number' }).primaryKey(),
    at: ts('at').notNull().defaultNow(),
    subject: text('subject').notNull(),
    clientId: text('client_id'),
    tool: text('tool').notNull(),
    args: jsonb('args').$type<Record<string, unknown>>().notNull().default({}),
    outcome: text('outcome', { enum: ['ok', 'error', 'denied'] }).notNull(),
    error: text('error'),
    durationMs: integer('duration_ms').notNull(),
  },
  t => [index('audit_log_at_idx').on(t.at), index('audit_log_subject_idx').on(t.subject, t.at)],
)

// ── OAuth 2.1 authorization server (AUTH_MODE=magento) ─────────────────────
// Codes and tokens are stored as SHA-256 hashes only; passwords are never stored.

export const oauthClients = ops.table('oauth_clients', {
  clientId: text('client_id').primaryKey(),
  /** Full RFC 7591 client information as registered. The SDK compares client_secret directly, so it is kept as issued. */
  info: jsonb('info').$type<Record<string, unknown>>().notNull(),
  createdAt: ts('created_at').notNull().defaultNow(),
})

export const oauthCodes = ops.table('oauth_codes', {
  codeHash: text('code_hash').primaryKey(),
  clientId: text('client_id').notNull(),
  subject: text('subject').notNull(),
  scopes: text('scopes').array().notNull(),
  codeChallenge: text('code_challenge').notNull(),
  redirectUri: text('redirect_uri').notNull(),
  resource: text('resource'),
  expiresAt: ts('expires_at').notNull(),
  usedAt: ts('used_at'),
})

export const oauthTokens = ops.table(
  'oauth_tokens',
  {
    tokenHash: text('token_hash').primaryKey(),
    kind: text('kind', { enum: ['access', 'refresh'] }).notNull(),
    clientId: text('client_id').notNull(),
    subject: text('subject').notNull(),
    scopes: text('scopes').array().notNull(),
    resource: text('resource'),
    createdAt: ts('created_at').notNull().defaultNow(),
    expiresAt: ts('expires_at').notNull(),
    revokedAt: ts('revoked_at'),
  },
  t => [index('oauth_tokens_subject_idx').on(t.subject), index('oauth_tokens_expires_idx').on(t.expiresAt)],
)