/**
 * MySQL Drizzle schema — mirrors `src/lib/db/schema.ts` (SQLite source-of-truth).
 *
 * Keep these two files in sync. The high-level rules from `./README.md`:
 *
 *   text('col')                          → varchar('col', { length: N }) for IDs/labels
 *                                          OR text('col') for long content (MJML/HTML)
 *   text('col', { mode: 'json' })        → json('col')
 *   integer('col', { mode: 'timestamp_ms' }) → bigint('col', { mode: 'number' })
 *   integer('col', { mode: 'boolean' })  → boolean('col')
 *   integer('col')                       → int('col')
 *   sql`(unixepoch() * 1000)`            → sql`(UNIX_TIMESTAMP(NOW(3)) * 1000)`
 *   uniqueIndex / index                  → same name, from mysql-core
 *
 * Length conventions:
 *   - UUID id columns:          varchar(36)
 *   - email:                    varchar(255)
 *   - slug / key / enum-as-text varchar(64)
 *   - generic short labels:     varchar(255)
 *   - hash / token (sha256 hex) varchar(128)
 *   - hex color:                varchar(16)
 *   - long content (MJML/HTML): text  (longtext if very large — we use text here)
 */

import { sql } from 'drizzle-orm';
import {
  mysqlTable,
  varchar,
  text,
  int,
  bigint,
  boolean,
  json,
  index,
} from 'drizzle-orm/mysql-core';

const id = () =>
  varchar('id', { length: 36 }).primaryKey().$defaultFn(() => crypto.randomUUID());
const ts = (n: string) => bigint(n, { mode: 'number' });
const now = sql`(UNIX_TIMESTAMP(NOW(3)) * 1000)`;

export const users = mysqlTable('users', {
  id: id(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 255 }),
  passwordHash: varchar('password_hash', { length: 255 }),
  role: varchar('role', { length: 32 }).$type<'admin' | 'editor'>().notNull().default('editor'),
  createdAt: ts('created_at').notNull().default(now),
  lastLoginAt: ts('last_login_at'),
});

export const workspaces = mysqlTable('workspaces', {
  id: id(),
  slug: varchar('slug', { length: 64 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  primaryColor: varchar('primary_color', { length: 16 }).notNull(),
  secondaryColor: varchar('secondary_color', { length: 16 }),
  logoUrl: varchar('logo_url', { length: 1024 }),
  senderDomain: varchar('sender_domain', { length: 255 }).notNull(),
  senderName: varchar('sender_name', { length: 255 }).notNull(),
  senderEmail: varchar('sender_email', { length: 255 }).notNull(),
  replyToEmail: varchar('reply_to_email', { length: 255 }).notNull(),
  shopDomain: varchar('shop_domain', { length: 255 }),
  webhookSecret: varchar('webhook_secret', { length: 128 }),
  ga4PropertyId: varchar('ga4_property_id', { length: 64 }),
  ga4CredentialsJson: json('ga4_credentials_json'),
  footerText: text('footer_text').notNull(),
  brandVoice: text('brand_voice').notNull(),
  wordingStyle: varchar('wording_style', { length: 512 }),
  designStyle: varchar('design_style', { length: 512 }),
  companyId: int('company_id'),
  imprintUrl: varchar('imprint_url', { length: 1024 }).notNull(),
  privacyUrl: varchar('privacy_url', { length: 1024 }).notNull(),
  brandKitJson: json('brand_kit_json').notNull(),
  attributionModel: varchar('attribution_model', { length: 32 })
    .$type<'last-click' | 'first-click' | 'linear' | 'time-decay'>()
    .notNull()
    .default('last-click'),
  attributionLookbackDays: int('attribution_lookback_days').notNull().default(30),
  cumasRecipientField: varchar('cumas_recipient_field', { length: 128 }),
  activeConversionKinds: json('active_conversion_kinds')
    .$type<Array<'order' | 'lead' | 'magic_login' | 'form_submit'>>()
    .notNull(),
  createdAt: ts('created_at').notNull().default(now),
  updatedAt: ts('updated_at').notNull().default(now),
});

export const appSettings = mysqlTable('app_settings', {
  key: varchar('key', { length: 128 }).primaryKey(),
  value: json('value').notNull(),
  updatedAt: ts('updated_at').notNull().default(now),
});

export const templates = mysqlTable('templates', {
  id: id(),
  workspaceId: varchar('workspace_id', { length: 36 })
    .notNull()
    .references(() => workspaces.id),
  name: varchar('name', { length: 255 }).notNull(),
  description: varchar('description', { length: 1024 }),
  tiptapJson: json('tiptap_json').notNull(),
  briefingJson: json('briefing_json'),
  createdAt: ts('created_at').notNull().default(now),
  updatedAt: ts('updated_at').notNull().default(now),
});

export const sectionTemplates = mysqlTable(
  'section_templates',
  {
    id: id(),
    workspaceId: varchar('workspace_id', { length: 36 })
      .notNull()
      .references(() => workspaces.id, { onDelete: 'cascade' }),
    kind: varchar('kind', { length: 32 })
      .$type<'contact_service' | 'app_download' | 'testimonial' | 'faq' | 'custom'>()
      .notNull(),
    name: varchar('name', { length: 255 }).notNull(),
    description: varchar('description', { length: 1024 }),
    defaultContentJson: json('default_content_json').notNull(),
    mjmlBlock: text('mjml_block').notNull(),
    aiPromptHint: text('ai_prompt_hint'),
    defaultPosition: varchar('default_position', { length: 32 })
      .$type<'before_cta' | 'after_cta' | 'before_signature' | 'after_signature'>()
      .notNull()
      .default('before_signature'),
    bodyMode: varchar('body_mode', { length: 16 })
      .$type<'tiptap' | 'mjml_raw' | 'html_raw'>()
      .notNull()
      .default('tiptap'),
    isSystem: boolean('is_system').notNull().default(false),
    createdAt: ts('created_at').notNull().default(now),
    updatedAt: ts('updated_at').notNull().default(now),
  },
  (t) => ({
    workspaceKindIdx: index('section_templates_workspace_kind').on(t.workspaceId, t.kind),
  }),
);

export const campaigns = mysqlTable('campaigns', {
  id: id(),
  workspaceId: varchar('workspace_id', { length: 36 })
    .notNull()
    .references(() => workspaces.id),
  templateId: varchar('template_id', { length: 36 }).references(() => templates.id),
  createdById: varchar('created_by_id', { length: 36 })
    .notNull()
    .references(() => users.id),
  name: varchar('name', { length: 255 }).notNull(),
  briefingJson: json('briefing_json').notNull(),
  tiptapJson: json('tiptap_json').notNull(),
  outputDir: varchar('output_dir', { length: 512 }),
  strategyJson: json('strategy_json'),
  emailHtmlPath: varchar('email_html_path', { length: 512 }),
  emailTxtPath: varchar('email_txt_path', { length: 512 }),
  heroAssetId: varchar('hero_asset_id', { length: 36 }),
  spamReportJson: json('spam_report_json'),
  spamScore: int('spam_score'),
  spamVerdict: varchar('spam_verdict', { length: 16 }).$type<'ship' | 'fix' | 'reject'>(),
  abEnabled: boolean('ab_enabled').notNull().default(false),
  abWinnerHours: int('ab_winner_hours').notNull().default(4),
  abWinnerVariantId: varchar('ab_winner_variant_id', { length: 36 }),
  utmDefaultsJson: json('utm_defaults_json'),
  status: varchar('status', { length: 32 })
    .$type<'draft' | 'building' | 'review' | 'scheduled' | 'sending' | 'sent' | 'failed' | 'cancelled'>()
    .notNull()
    .default('draft'),
  kind: varchar('kind', { length: 32 })
    .$type<'marketing' | 'trigger_container'>()
    .notNull()
    .default('marketing'),
  scheduledFor: ts('scheduled_for'),
  startedAt: ts('started_at'),
  completedAt: ts('completed_at'),
  createdAt: ts('created_at').notNull().default(now),
  updatedAt: ts('updated_at').notNull().default(now),
});

export const campaignVersions = mysqlTable(
  'campaign_versions',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id, { onDelete: 'cascade' }),
    source: varchar('source', { length: 16 }).$type<'auto' | 'manual' | 'milestone'>().notNull(),
    label: varchar('label', { length: 255 }),
    milestone: varchar('milestone', { length: 16 }).$type<
      'generate' | 'render' | 'send' | 'pre_restore'
    >(),
    name: varchar('name', { length: 255 }).notNull(),
    briefingJson: json('briefing_json').notNull(),
    tiptapJson: json('tiptap_json').notNull(),
    strategyJson: json('strategy_json'),
    emailHtml: text('email_html'),
    emailText: text('email_text'),
    spamReportJson: json('spam_report_json'),
    heroAssetId: varchar('hero_asset_id', { length: 36 }),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    campaignCreatedIdx: index('versions_campaign_created').on(t.campaignId, t.createdAt),
  }),
);

export const campaignVariants = mysqlTable(
  'campaign_variants',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id, { onDelete: 'cascade' }),
    label: varchar('label', { length: 16 }).notNull(),
    subject: varchar('subject', { length: 512 }).notNull(),
    preheader: varchar('preheader', { length: 512 }).notNull(),
    mjml: text('mjml'),
    html: text('html'),
    htmlText: text('html_text'),
    strategyJson: json('strategy_json'),
    spamReportJson: json('spam_report_json'),
    spamScore: int('spam_score'),
    isWinner: boolean('is_winner').notNull().default(false),
    sentCount: int('sent_count').notNull().default(0),
    openCount: int('open_count').notNull().default(0),
    clickCount: int('click_count').notNull().default(0),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    campaignIdx: index('variants_campaign').on(t.campaignId),
  }),
);

export const recipients = mysqlTable(
  'recipients',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id),
    variantId: varchar('variant_id', { length: 36 }).references(() => campaignVariants.id),
    crmId: varchar('crm_id', { length: 128 }),
    email: varchar('email', { length: 255 }).notNull(),
    firstName: varchar('first_name', { length: 128 }),
    lastName: varchar('last_name', { length: 128 }),
    poolType: varchar('pool_type', { length: 16 })
      .$type<'private' | 'business'>()
      .notNull()
      .default('private'),
    customDataJson: json('custom_data_json'),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    campaignEmailIdx: index('recipients_campaign_email').on(t.campaignId, t.email),
  }),
);

export const emailQueue = mysqlTable(
  'email_queue',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id),
    recipientId: varchar('recipient_id', { length: 36 })
      .notNull()
      .references(() => recipients.id),
    variantId: varchar('variant_id', { length: 36 }).references(() => campaignVariants.id),
    status: varchar('status', { length: 32 })
      .$type<'pending' | 'sending' | 'sent' | 'failed' | 'bounced' | 'skipped' | 'champion_held'>()
      .notNull()
      .default('pending'),
    scheduledAt: ts('scheduled_at').notNull().default(now),
    attempts: int('attempts').notNull().default(0),
    lastError: text('last_error'),
    messageId: varchar('message_id', { length: 255 }),
    sentAt: ts('sent_at'),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    statusScheduledIdx: index('queue_status_scheduled').on(t.status, t.scheduledAt),
    campaignStatusIdx: index('queue_campaign_status').on(t.campaignId, t.status),
  }),
);

export const trackingEvents = mysqlTable(
  'tracking_events',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id),
    recipientId: varchar('recipient_id', { length: 36 }).references(() => recipients.id),
    variantId: varchar('variant_id', { length: 36 }).references(() => campaignVariants.id),
    messageId: varchar('message_id', { length: 255 }).notNull(),
    eventType: varchar('event_type', { length: 32 })
      .$type<'delivered' | 'open' | 'click' | 'bounce' | 'unsubscribe' | 'complaint' | 'conversion'>()
      .notNull(),
    linkUrl: varchar('link_url', { length: 2048 }),
    userAgent: varchar('user_agent', { length: 512 }),
    ipHash: varchar('ip_hash', { length: 128 }),
    isMpp: boolean('is_mpp'),
    value: int('value'),
    metaJson: json('meta_json'),
    source: varchar('source', { length: 16 }).$type<
      'pixel' | 'api_poll' | 'webhook' | 'manual' | 'system'
    >(),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    campaignTsIdx: index('events_campaign_ts').on(t.campaignId, t.createdAt),
    messageIdx: index('events_message').on(t.messageId, t.eventType),
    recipientEventTsIdx: index('events_recipient_event_ts').on(
      t.recipientId,
      t.eventType,
      t.createdAt,
    ),
  }),
);

export const behaviorEvents = mysqlTable(
  'behavior_events',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 })
      .notNull()
      .references(() => campaigns.id),
    recipientId: varchar('recipient_id', { length: 36 })
      .notNull()
      .references(() => recipients.id),
    variantId: varchar('variant_id', { length: 36 }).references(() => campaignVariants.id),
    type: varchar('type', { length: 32 })
      .$type<
        | 'pageview'
        | 'product_view'
        | 'add_to_cart'
        | 'quote_form'
        | 'scroll_depth'
        | 'time_on_page'
        | 'custom'
      >()
      .notNull(),
    url: varchar('url', { length: 2048 }),
    productId: varchar('product_id', { length: 128 }),
    value: int('value'),
    durationMs: int('duration_ms'),
    scrollPct: int('scroll_pct'),
    metaJson: json('meta_json'),
    userAgent: varchar('user_agent', { length: 512 }),
    ipHash: varchar('ip_hash', { length: 128 }),
    source: varchar('source', { length: 16 }).$type<'js' | 'webhook'>().notNull(),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    campaignTsIdx: index('behavior_campaign_ts').on(t.campaignId, t.createdAt),
    recipientIdx: index('behavior_recipient').on(t.recipientId),
    recipientTypeIdx: index('behavior_recipient_type').on(t.recipientId, t.type),
  }),
);

export const assets = mysqlTable(
  'assets',
  {
    id: id(),
    campaignId: varchar('campaign_id', { length: 36 }).references(() => campaigns.id),
    workspaceId: varchar('workspace_id', { length: 36 }).references(() => workspaces.id),
    kind: varchar('kind', { length: 16 }).$type<'hero' | 'inline' | 'logo'>().notNull(),
    filePath: varchar('file_path', { length: 1024 }).notNull(),
    publicUrl: varchar('public_url', { length: 1024 }).notNull(),
    altText: varchar('alt_text', { length: 512 }),
    promptHash: varchar('prompt_hash', { length: 128 }),
    promptJson: json('prompt_json'),
    width: int('width'),
    height: int('height'),
    bytes: int('bytes'),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    promptHashIdx: index('assets_prompt_hash').on(t.promptHash),
  }),
);

export const unsubscribes = mysqlTable('unsubscribes', {
  id: id(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  campaignId: varchar('campaign_id', { length: 36 }).references(() => campaigns.id),
  reason: varchar('reason', { length: 512 }),
  source: varchar('source', { length: 16 })
    .$type<'link' | 'header' | 'manual' | 'complaint'>()
    .notNull(),
  createdAt: ts('created_at').notNull().default(now),
});

export const sessions = mysqlTable('sessions', {
  id: id(),
  userId: varchar('user_id', { length: 36 })
    .notNull()
    .references(() => users.id),
  token: varchar('token', { length: 255 }).notNull().unique(),
  expiresAt: ts('expires_at').notNull(),
  createdAt: ts('created_at').notNull().default(now),
});

export const magicLinks = mysqlTable('magic_links', {
  id: id(),
  email: varchar('email', { length: 255 }).notNull(),
  token: varchar('token', { length: 255 }).notNull().unique(),
  expiresAt: ts('expires_at').notNull(),
  usedAt: ts('used_at'),
  createdAt: ts('created_at').notNull().default(now),
});

export const triggerTemplates = mysqlTable('trigger_templates', {
  id: id(),
  key: varchar('key', { length: 64 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  description: varchar('description', { length: 1024 }),
  workspaceId: varchar('workspace_id', { length: 36 })
    .notNull()
    .references(() => workspaces.id),
  containerCampaignId: varchar('container_campaign_id', { length: 36 })
    .notNull()
    .references(() => campaigns.id),
  containerVariantId: varchar('container_variant_id', { length: 36 })
    .notNull()
    .references(() => campaignVariants.id),
  subject: varchar('subject', { length: 512 }).notNull(),
  preheader: varchar('preheader', { length: 512 }).notNull(),
  mjml: text('mjml').notNull(),
  html: text('html').notNull(),
  htmlText: text('html_text').notNull(),
  variableSchemaJson: json('variable_schema_json').notNull(),
  senderEmail: varchar('sender_email', { length: 255 }),
  apiKeyHash: varchar('api_key_hash', { length: 128 }).notNull(),
  apiKeyPrefix: varchar('api_key_prefix', { length: 16 }).notNull(),
  isActive: boolean('is_active').notNull().default(true),
  utmDefaultsJson: json('utm_defaults_json'),
  rateLimitPerHour: int('rate_limit_per_hour'),
  lastTriggeredAt: ts('last_triggered_at'),
  triggerCount: int('trigger_count').notNull().default(0),
  createdAt: ts('created_at').notNull().default(now),
  updatedAt: ts('updated_at').notNull().default(now),
});

export const triggerLogs = mysqlTable(
  'trigger_logs',
  {
    id: id(),
    triggerTemplateId: varchar('trigger_template_id', { length: 36 })
      .notNull()
      .references(() => triggerTemplates.id, { onDelete: 'cascade' }),
    recipientEmail: varchar('recipient_email', { length: 255 }).notNull(),
    idempotencyKey: varchar('idempotency_key', { length: 128 }),
    status: varchar('status', { length: 32 })
      .$type<
        | 'queued'
        | 'rejected_validation'
        | 'rejected_unsubscribed'
        | 'rejected_rate_limit'
        | 'rejected_auth'
        | 'duplicate'
      >()
      .notNull(),
    errorMessage: text('error_message'),
    requestPayloadJson: json('request_payload_json'),
    emailQueueJobId: varchar('email_queue_job_id', { length: 36 }),
    createdAt: ts('created_at').notNull().default(now),
  },
  (t) => ({
    templateCreatedIdx: index('trigger_logs_template_created').on(
      t.triggerTemplateId,
      t.createdAt,
    ),
    idempotencyIdx: index('trigger_logs_idempotency').on(t.triggerTemplateId, t.idempotencyKey),
  }),
);

export type User = typeof users.$inferSelect;
export type Workspace = typeof workspaces.$inferSelect;
export type Template = typeof templates.$inferSelect;
export type Campaign = typeof campaigns.$inferSelect;
export type CampaignVariant = typeof campaignVariants.$inferSelect;
export type CampaignVersion = typeof campaignVersions.$inferSelect;
export type CampaignVersionInsert = typeof campaignVersions.$inferInsert;
export type Recipient = typeof recipients.$inferSelect;
export type EmailQueueJob = typeof emailQueue.$inferSelect;
export type TrackingEvent = typeof trackingEvents.$inferSelect;
export type BehaviorEvent = typeof behaviorEvents.$inferSelect;
export type BehaviorEventInsert = typeof behaviorEvents.$inferInsert;
export type Asset = typeof assets.$inferSelect;
export type AppSetting = typeof appSettings.$inferSelect;
export type WorkspaceInsert = typeof workspaces.$inferInsert;
export type SectionTemplate = typeof sectionTemplates.$inferSelect;
export type SectionTemplateInsert = typeof sectionTemplates.$inferInsert;
export type TriggerTemplate = typeof triggerTemplates.$inferSelect;
export type TriggerTemplateInsert = typeof triggerTemplates.$inferInsert;
export type TriggerLog = typeof triggerLogs.$inferSelect;
export type TriggerLogInsert = typeof triggerLogs.$inferInsert;
