import { pgTable, text, integer, real, timestamp, jsonb, boolean, bigint } from 'drizzle-orm/pg-core';

// ── JSONB field types ─────────────────────────────────────────────────────────

export interface TeamMember {
  name: string;
  role: string;
  avatar?: string;
}

export interface ActivityEntry {
  id: string;
  action: string;
  detail?: string;
  user?: string;
  timestamp: string;
}

/** Allowed values for `projectLogs.level`. Validated at the API boundary. */
export type LogLevel = 'info' | 'warn' | 'error' | 'debug';
export const LOG_LEVELS: readonly LogLevel[] = ['info', 'warn', 'error', 'debug'];

export interface ProjectConfig {
  analysisResults?: Record<string, unknown>;
  [key: string]: unknown;
}

export interface StepMetadata {
  [key: string]: unknown;
}

// ── JSONB field types for Company ────────────────────────────────────────────

export interface CompanyFinancialData {
  billingAddress?: string;
  taxId?: string;
  vatNumber?: string;
  billingEmail?: string;
}

export interface CompanyPaymentMethod {
  type: 'card' | 'sepa_debit' | 'sepa' | 'paypal' | 'link' | 'apple_pay' | 'google_pay';
  last4?: string;
  brand?: string;
  expiryMonth?: number;
  expiryYear?: number;
  email?: string;
  accountHolderName?: string;
  bankCode?: string;
  country?: string;
  stripePaymentMethodId?: string;
}

// ── Tables ────────────────────────────────────────────────────────────────────

// Company table - parent entity for multi-tenant architecture
export const companies = pgTable('companies', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  // Package/tier determines max users and features
  package: text('package').notNull().default('starter'), // 'starter', 'professional', 'enterprise'
  maxUsers: integer('max_users').notNull().default(5),
  // Owner relationship - references a user (must be from this company)
  ownerId: text('owner_id').notNull(),
  // Financial data - only owner can view/edit
  financialData: jsonb('financial_data').$type<CompanyFinancialData>().default({}),
  // Payment method - only owner can view/edit
  paymentMethod: jsonb('payment_method').$type<CompanyPaymentMethod | null>().default(null),
  // Stripe customer ID for billing
  stripeCustomerId: text('stripe_customer_id'),
  // Owned tokens - company's token pool for all users to consume from
  ownedTokens: bigint('owned_tokens', { mode: 'number' }).notNull().default(0),
  // Tracks how many admin-granted free tokens have been applied to billing so far
  freeTokensConsumed: bigint('free_tokens_consumed', { mode: 'number' }).notNull().default(0),
  // Extra conversion slots purchased by the company owner on top of the plan's included slots
  extraConversionSlots: integer('extra_conversion_slots').notNull().default(0),
  /** Plan credits remaining (tokens); resets each billing cycle (see billing-service). */
  licenseCreditsRemaining: bigint('license_credits_remaining', { mode: 'number' }).notNull().default(5_000_000),
  /** Set when the monthly charge fails or no payment method is on file. Null = active. Blocks all members until settled. */
  suspendedAt: timestamp('suspended_at'),
  /** Human-readable reason shown to members on the suspended screen. */
  suspendedReason: text('suspended_reason'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
}, () => ({}));

// User table
export const users = pgTable('user', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').default(false),
  name: text('name'),
  image: text('image'),
  role: text('role').notNull().default('client'),
  tier: text('tier').notNull().default('starter'),
  stripeCustomerId: text('stripe_customer_id'),
  pilotActivatedAt: timestamp('pilot_activated_at'),
  // 2FA fields
  twoFactorEnabled: boolean('two_factor_enabled').default(false),
  twoFactorMethod: text('two_factor_method'), // 'email' | 'sms' | 'authenticator'
  twoFactorSecret: text('two_factor_secret'), // For TOTP/authenticator
  phoneNumber: text('phone_number'), // For SMS 2FA
  // Company relationship - mandatory for multi-tenant architecture
  companyId: text('company_id').references(() => companies.id, { onDelete: 'cascade' }),
  // Owner flag - only one owner per company
  isOwner: boolean('is_owner').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Session table - now stores refresh tokens for rotation
export const sessions = pgTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  expiresAt: timestamp('expires_at').notNull(),
  refreshTokenHash: text('refresh_token_hash').notNull(), // Hashed refresh token
  tokenFamily: text('token_family'), // Track token family for rotation
  revokedAt: timestamp('revoked_at'), // For token revocation
  replacedByToken: text('replaced_by_token'), // Link to new token in rotation chain
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Account table for credential & OAuth providers
export const accounts = pgTable('account', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  idToken: text('id_token'),
  expiresAt: timestamp('expires_at'),
  password: text('password'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export const projects = pgTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  status: text('status').default('draft'),
  repoUrl: text('repo_url'),
  sourceLanguage: text('source_language'),
  targetLanguage: text('target_language'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
  completedAt: timestamp('completed_at'),
  totalFiles: integer('total_files').default(0),
  totalLines: integer('total_lines').default(0),
  convertedFiles: integer('converted_files').default(0),
  accuracy: real('accuracy').default(0),
  testCoverage: real('test_coverage').default(0),
  riskScore: real('risk_score').default(0),
  estimatedTime: text('estimated_time'),
  elapsedTime: text('elapsed_time'),
  team: jsonb('team').$type<TeamMember[]>().default([]),
  tags: jsonb('tags').$type<string[]>().default([]),
  activity: jsonb('activity').$type<ActivityEntry[]>().default([]),
  maxReachedStep: integer('max_reached_step').default(0),
  currentStep: integer('current_step').default(0),
  config: jsonb('config').$type<ProjectConfig>().default({}),
  tokensUsed: integer('tokens_used').default(0),
  /** Snapshot when customer confirms upfront cost (billing uses approved_net_eur_cents). */
  approvedEstimatedTokens: integer('approved_estimated_tokens'),
  approvedGrossEurCents: integer('approved_gross_eur_cents'),
  approvedNetEurCents: integer('approved_net_eur_cents'),
  approvedCreditsTokensApplied: integer('approved_credits_tokens_applied'),
  costApprovedAt: timestamp('cost_approved_at'),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
});

export const projectSteps = pgTable('project_steps', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  projectId: text('project_id').notNull().references(() => projects.id, { onDelete: 'cascade' }),
  stepNumber: integer('step_number').notNull(),
  stepName: text('step_name').notNull(),
  status: text('status').default('pending'),
  startedAt: timestamp('started_at'),
  completedAt: timestamp('completed_at'),
  metadata: jsonb('metadata').$type<StepMetadata>().default({}).notNull(),
});

/**
 * Verbose real-time migration log — one row per engine log line emitted during a
 * conversion run. Written by MigrationFlow via POST /api/conversions/[id]/logs and
 * re-hydrated on component mount so a user can review past runs.
 *
 * Distinct from:
 *  - `projects.activity` (JSONB) — coarse lifecycle events ("Started Migration", "Completed Verification")
 *  - `tokenLogs`                 — billing events, one row per step that consumed tokens
 *
 * `level`  is one of: 'info' | 'warn' | 'error' | 'debug'
 * `time`   is a wall-clock string formatted as HH:mm:ss.mmm (local time at write)
 * `message` has already been sanitized by sanitizeEngineLogForDisplay before storage
 */
export const projectLogs = pgTable('project_logs', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  projectId: text('project_id').notNull().references(() => projects.id, { onDelete: 'cascade' }),
  time: text('time').notNull(),
  level: text('level').notNull(),
  message: text('message').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

/** Token usage event log — one row per step completion that consumed tokens */
export const tokenLogs = pgTable('token_logs', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  companyId: text('company_id').notNull().references(() => companies.id, { onDelete: 'cascade' }),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  userName: text('user_name').notNull(),
  userEmail: text('user_email'),
  projectId: text('project_id').notNull().references(() => projects.id, { onDelete: 'cascade' }),
  projectName: text('project_name').notNull(),
  stepName: text('step_name').notNull(),
  tokensConsumed: integer('tokens_consumed').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

/** 2FA challenges for pending authentication */
export const twoFactorChallenges = pgTable('two_factor_challenges', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  method: text('method').notNull(), // 'email' | 'sms' | 'authenticator'
  codeHash: text('code_hash'), // Hashed verification code (for email/sms)
  expiresAt: timestamp('expires_at').notNull(),
  verified: boolean('verified').default(false),
  attempts: integer('attempts').default(0),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  createdAt: timestamp('created_at').defaultNow(),
});

/** Queued migration alerts — workers (cron + SMTP/Slack/etc.) consume `pending` rows. */
export const notificationOutbox = pgTable('notification_outbox', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  projectId: text('project_id')
    .notNull()
    .references(() => projects.id, { onDelete: 'cascade' }),
  channel: text('channel').notNull(),
  event: text('event').notNull(),
  status: text('status').notNull().default('pending'),
  payload: jsonb('payload').$type<Record<string, unknown>>().default({}).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

/** Monthly billing records — one row per user per billing cycle. */
export const billingRecords = pgTable('billing_records', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  periodStart: timestamp('period_start').notNull(),
  periodEnd: timestamp('period_end').notNull(),
  totalTokens: bigint('total_tokens', { mode: 'number' }).notNull().default(0),
  amountCents: integer('amount_cents').notNull().default(0),
  currency: text('currency').notNull().default('eur'),
  status: text('status').notNull().default('pending'), // 'pending' | 'charged' | 'failed' | 'skipped'
  stripePaymentIntentId: text('stripe_payment_intent_id'),
  stripeInvoiceId: text('stripe_invoice_id'),
  fattureInCloudDocumentId: text('fatture_in_cloud_document_id'),
  errorMessage: text('error_message'),
  createdAt: timestamp('created_at').defaultNow(),
});

/** PayPal orders for tracking transactions */
export const paypalOrders = pgTable('paypal_orders', {
  id: text('id').primaryKey(), // PayPal order ID
  companyId: text('company_id').notNull().references(() => companies.id, { onDelete: 'cascade' }),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  amount: text('amount').notNull(),
  currency: text('currency').notNull().default('EUR'),
  status: text('status').notNull().default('CREATED'), // 'CREATED' | 'APPROVED' | 'COMPLETED' | 'FAILED'
  captureId: text('capture_id'), // PayPal capture ID when order is completed
  paypalFee: real('paypal_fee'), // PayPal transaction fee
  completedAt: timestamp('completed_at'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

/** Payment methods for companies (Stripe + PayPal) */
export const paymentMethods = pgTable('payment_methods', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  companyId: text('company_id').notNull().references(() => companies.id, { onDelete: 'cascade' }),
  type: text('type').notNull(), // 'card' | 'sepa_debit' | 'sepa' | 'paypal' | 'link' | 'apple_pay' | 'google_pay'
  
  // Card / wallet fields
  last4: text('last4'),
  brand: text('brand'),
  expiryMonth: integer('expiry_month'),
  expiryYear: integer('expiry_year'),
  
  // PayPal fields
  paypalEmail: text('paypal_email'),
  paypalOrderId: text('paypal_order_id'),
  paypalCaptureId: text('paypal_capture_id'),
  
  // SEPA / bank account fields
  accountHolderName: text('account_holder_name'),
  bankCode: text('bank_code'),
  country: text('country'),
  
  // Stripe payment method ID (for syncing with Stripe customer)
  stripePaymentMethodId: text('stripe_payment_method_id'),
  
  isDefault: boolean('is_default').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

/** Extra conversion slot purchases — one row per purchase event. */
export const slotPurchases = pgTable('slot_purchases', {
  id: text('id').primaryKey(),
  companyId: text('company_id').notNull().references(() => companies.id, { onDelete: 'cascade' }),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  quantity: integer('quantity').notNull().default(1),
  pricePerSlotCents: integer('price_per_slot_cents').notNull(),
  totalCents: integer('total_cents').notNull(),
  purchasedAt: timestamp('purchased_at').defaultNow().notNull(),
});

export type SlotPurchase = typeof slotPurchases.$inferSelect;

/** Billing transactions for all payment methods */
export const billingTransactions = pgTable('billing_transactions', {
  id: integer('id').primaryKey().generatedByDefaultAsIdentity(),
  companyId: text('company_id').notNull().references(() => companies.id, { onDelete: 'cascade' }),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  type: text('type').notNull(), // 'payment' | 'refund' | 'chargeback'
  amount: real('amount').notNull(),
  currency: text('currency').notNull().default('EUR'),
  status: text('status').notNull().default('pending'), // 'pending' | 'completed' | 'failed' | 'refunded'
  paymentMethodType: text('payment_method_type').notNull(), // 'stripe' | 'paypal'
  paymentMethodId: text('payment_method_id').notNull(),
  description: text('description'),
  metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

/**
 * Fatture in Cloud configuration + OAuth tokens (single row, id = 'default').
 * Managed via the Admin → FIC Settings panel — no env vars required after setup.
 */
export const ficSettings = pgTable('fic_settings', {
  id: text('id').primaryKey(), // always 'default'
  clientId: text('client_id'),
  clientSecret: text('client_secret'),
  companyId: text('company_id'),
  vatId: text('vat_id').default('0'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  expiresAt: timestamp('expires_at'),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export type FicSettings = typeof ficSettings.$inferSelect;

export type BillingRecord = typeof billingRecords.$inferSelect;
export type NewBillingRecord = typeof billingRecords.$inferInsert;

export type PayPalOrder = typeof paypalOrders.$inferSelect;
export type NewPayPalOrder = typeof paypalOrders.$inferInsert;

export interface PaymentMethod {
  id: number;
  companyId: string;
  type: string;
  last4?: string | null;
  brand?: string | null;
  expiryMonth?: number | null;
  expiryYear?: number | null;
  paypalEmail?: string | null;
  paypalOrderId?: string | null;
  paypalCaptureId?: string | null;
  accountHolderName?: string | null;
  bankCode?: string | null;
  country?: string | null;
  stripePaymentMethodId?: string | null;
  isDefault: boolean;
  createdAt: Date;
  updatedAt: Date;
}

export interface NewPaymentMethod {
  companyId: string;
  type: string;
  last4?: string | null;
  brand?: string | null;
  expiryMonth?: number | null;
  expiryYear?: number | null;
  paypalEmail?: string | null;
  paypalOrderId?: string | null;
  paypalCaptureId?: string | null;
  accountHolderName?: string | null;
  bankCode?: string | null;
  country?: string | null;
  stripePaymentMethodId?: string | null;
  isDefault?: boolean;
  createdAt?: Date;
  updatedAt?: Date;
}

export type BillingTransaction = typeof billingTransactions.$inferSelect;
export type NewBillingTransaction = typeof billingTransactions.$inferInsert;

export type TwoFactorChallenge = typeof twoFactorChallenges.$inferSelect;
export type NewTwoFactorChallenge = typeof twoFactorChallenges.$inferInsert;

export type Company = typeof companies.$inferSelect;
export type NewCompany = typeof companies.$inferInsert;

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Session = typeof sessions.$inferSelect;
export type NewSession = typeof sessions.$inferInsert;
export type Account = typeof accounts.$inferSelect;
export type NewAccount = typeof accounts.$inferInsert;
export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;
export type ProjectStep = typeof projectSteps.$inferSelect;
export type NewProjectStep = typeof projectSteps.$inferInsert;
export type NotificationOutbox = typeof notificationOutbox.$inferSelect;
export type NewNotificationOutbox = typeof notificationOutbox.$inferInsert;
export type ProjectLog = typeof projectLogs.$inferSelect;
export type NewProjectLog = typeof projectLogs.$inferInsert;