import { pgTable, text, boolean, integer, uuid, index, uniqueIndex, primaryKey, unique } from 'drizzle-orm/pg-core';
import { id, tenantId, createdAt, updatedAt, ts } from './_common';
import { userStatusEnum } from './enums';
import { tenants } from './platform';
import { warehouses, storers } from './master-data';

export const users = pgTable(
  'users',
  {
    id: id(),
    tenantId: tenantId().references(() => tenants.id),
    email: text('email').notNull(),
    passwordHash: text('password_hash'),
    name: text('name').notNull(),
    phone: text('phone'),
    status: userStatusEnum('status').notNull().default('ACTIVE'),
    isTenantAdmin: boolean('is_tenant_admin').notNull().default(false),
    /** Set for storer-portal users: everything they see is scoped to this storer. */
    storerId: uuid('storer_id').references(() => storers.id),
    locale: text('locale').notNull().default('en'),
    lastLoginAt: ts('last_login_at'),
    failedLoginAttempts: integer('failed_login_attempts').notNull().default(0),
    lockedUntil: ts('locked_until'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('users_tenant_email_uq').on(t.tenantId, t.email), index('users_tenant_status_idx').on(t.tenantId, t.status)],
);

export const roles = pgTable(
  'roles',
  {
    id: id(),
    tenantId: tenantId().references(() => tenants.id),
    code: text('code').notNull(),
    name: text('name').notNull(),
    description: text('description'),
    permissions: text('permissions').array().notNull().default([]),
    isSystem: boolean('is_system').notNull().default(false),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('roles_tenant_code_uq').on(t.tenantId, t.code)],
);

export const userRoles = pgTable(
  'user_roles',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    roleId: uuid('role_id').notNull().references(() => roles.id, { onDelete: 'cascade' }),
    /** null = role applies in every warehouse the user can access */
    warehouseId: uuid('warehouse_id').references(() => warehouses.id),
  },
  (t) => [unique('user_roles_uq').on(t.userId, t.roleId, t.warehouseId).nullsNotDistinct()],
);

export const userWarehouses = pgTable(
  'user_warehouses',
  {
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    warehouseId: uuid('warehouse_id').notNull().references(() => warehouses.id, { onDelete: 'cascade' }),
    isDefault: boolean('is_default').notNull().default(false),
  },
  (t) => [primaryKey({ columns: [t.userId, t.warehouseId] })],
);

export const sessions = pgTable(
  'sessions',
  {
    id: id(),
    userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
    refreshTokenHash: text('refresh_token_hash').notNull().unique(),
    userAgent: text('user_agent'),
    ip: text('ip'),
    deviceId: text('device_id'),
    expiresAt: ts('expires_at').notNull(),
    revokedAt: ts('revoked_at'),
    createdAt: createdAt(),
  },
  (t) => [index('sessions_user_idx').on(t.userId)],
);

export const apiKeys = pgTable(
  'api_keys',
  {
    id: id(),
    tenantId: tenantId().references(() => tenants.id),
    storerId: uuid('storer_id').references(() => storers.id),
    name: text('name').notNull(),
    prefix: text('prefix').notNull(),
    keyHash: text('key_hash').notNull().unique(),
    scopes: text('scopes').array().notNull().default([]),
    rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(600),
    lastUsedAt: ts('last_used_at'),
    expiresAt: ts('expires_at'),
    revokedAt: ts('revoked_at'),
    createdById: uuid('created_by_id'),
    createdAt: createdAt(),
  },
  (t) => [index('api_keys_tenant_idx').on(t.tenantId)],
);