// src/schemas/user.schema.ts
import type { EntitySchema } from '@mostajs/orm'

export const UserSchema: EntitySchema = {
  name: 'User',
  collection: 'users',       // table name in SQL, collection name in MongoDB
  timestamps: true,          // auto createdAt / updatedAt
  fields: {
    email:     { type: 'string', required: true, unique: true, lowercase: true },
    username:  { type: 'string', required: true, unique: true },
    password:  { type: 'string', required: true },
    role:      { type: 'string', enum: ['user', 'admin'], default: 'user' },
    status:    { type: 'string', enum: ['active', 'banned'], default: 'active' },
    lastLogin: { type: 'date' },
    score:     { type: 'number', default: 0 },
  },
  relations: {},
  indexes: [
    { fields: { email: 'asc' }, unique: true },
    { fields: { role: 'asc' } },
  ],
}

// src/repositories/user.repository.ts
import { BaseRepository } from '@mostajs/orm'
import type { IDialect } from '@mostajs/orm'
import { UserSchema } from '../schemas/user.schema.js'

export interface UserDTO {
  id: string
  email: string
  username: string
  role: string
  status: string
  createdAt: string
}

export class UserRepository extends BaseRepository<UserDTO> {
  constructor(dialect: IDialect) {
    super(UserSchema, dialect)
  }

  // Custom method — uses built-in findOne()
  async findByEmail(email: string): Promise<UserDTO | null> {
    return this.findOne({ email: email.toLowerCase() })
  }

  async findAdmins(): Promise<UserDTO[]> {
    return this.findAll({ role: 'admin' }, { sort: { createdAt: -1 } })
  }

  async countActive(): Promise<number> {
    return this.count({ status: 'active' })
  }
}

// src/index.ts
import { createConnection, registerSchema } from '@mostajs/orm'
import { UserSchema } from './schemas/user.schema.js'
import { UserRepository } from './repositories/user.repository.js'

// Register all schemas
registerSchema(UserSchema)

// Connect — reads DB_DIALECT and SGBD_URI from environment
const dialect = await createConnection()

// Create a repository instance
const userRepo = new UserRepository(dialect)

// --- CRUD Operations ---

// Create
const user = await userRepo.create({
  email: 'alice@example.com',
  username: 'alice',
  password: 'hashed_password',
})
console.log(user.id) // auto-generated ID

// Find
const found = await userRepo.findByEmail('alice@example.com')
const allAdmins = await userRepo.findAdmins()
const activeCount = await userRepo.countActive()

// Update
const updated = await userRepo.update(user.id, { role: 'admin' })

// Delete
const deleted = await userRepo.delete(user.id)

console.log('Done!')

import { createConnection, registerSchema, registerSchemas } from '@mostajs/orm'

// Register schemas before connecting
registerSchema(UserSchema)
registerSchemas([UserSchema, PostSchema, CommentSchema])

// Connect (reads from environment)
const dialect = await createConnection()

// Or pass config directly
const dialect = await createConnection({
  dialect: 'postgres',
  uri: 'postgresql://localhost/mydb',
  schemaStrategy: 'update',  // validate | update | create | create-drop
  showSQL: false,
})

// ── READ ────────────────────────────────────────────────────────────────────

// Get all records (with optional filter & options)
findAll(filter?: FilterQuery, options?: QueryOptions): Promise<T[]>

// Get one record by filter
findOne(filter: FilterQuery, options?: QueryOptions): Promise<T | null>

// Get by ID
findById(id: string, options?: QueryOptions): Promise<T | null>

// Get by ID with related entities populated
findByIdWithRelations(id: string, relations?: string[], options?): Promise<T | null>

// Get all with relations
findWithRelations(filter?, relations?, options?): Promise<T[]>

// ── WRITE ────────────────────────────────────────────────────────────────────

// Create a new record
create(data: Partial<T>): Promise<T>

// Update one record by ID (partial)
update(id: string, data: Partial<T>): Promise<T | null>

// Update many records matching filter
updateMany(filter: FilterQuery, data: Partial<T>): Promise<number>

// Delete one record by ID
delete(id: string): Promise<boolean>

// Delete many records matching filter
deleteMany(filter: FilterQuery): Promise<number>

// Create or update (upsert)
upsert(filter: FilterQuery, data: Partial<T>): Promise<T>

// ── QUERY ────────────────────────────────────────────────────────────────────

// Count matching records
count(filter?: FilterQuery): Promise<number>

// Get distinct values of a field
distinct(field: string, filter?: FilterQuery): Promise<unknown[]>

// Full-text search across string fields
search(query: string, options?: QueryOptions): Promise<T[]>

// ── ATOMIC ───────────────────────────────────────────────────────────────────

// Increment a numeric field
increment(id: string, field: string, amount?: number): Promise<T | null>

// Add value to array field (no duplicates)
addToSet(id: string, field: string, value: unknown): Promise<T | null>

// Remove value from array field
pull(id: string, field: string, value: unknown): Promise<T | null>

// ── AGGREGATE ────────────────────────────────────────────────────────────────

// Aggregation pipeline
aggregate<R>(stages: AggregateStage[]): Promise<R[]>

// Equality (shorthand)
findAll({ status: 'active' })
findAll({ role: 'admin', status: 'active' })

// Comparison operators
findAll({ score: { $gt: 100 } })
findAll({ score: { $gte: 0, $lte: 1000 } })
findAll({ age:   { $lt: 18 } })
findAll({ name:  { $ne: 'anonymous' } })

// Array membership
findAll({ status: { $in: ['active', 'pending'] } })
findAll({ role:   { $nin: ['banned', 'deleted'] } })

// Existence
findAll({ photo: { $exists: true } })
findAll({ deletedAt: { $exists: false } })

// Regex
findAll({ email: { $regex: '@gmail\\.com$' } })
findAll({ name:  { $regex: 'alice', $options: 'i' } })  // case-insensitive

// Logical
findAll({ $or:  [{ role: 'admin' }, { score: { $gt: 9000 } }] })
findAll({ $and: [{ status: 'active' }, { score: { $gte: 100 } }] })

findAll(filter, {
  sort:    { createdAt: -1, name: 1 },   // -1 = DESC, 1 = ASC
  skip:    0,
  limit:   20,
  select:  ['id', 'email', 'role'],      // include only these fields
  exclude: ['password', '__v'],          // exclude these fields
})

const PostSchema: EntitySchema = {
  name: 'Post',
  collection: 'posts',
  timestamps: true,

  fields: {
    title:     { type: 'string',  required: true },
    slug:      { type: 'string',  required: true, unique: true },
    body:      { type: 'string',  required: true },
    status:    { type: 'string',  enum: ['draft', 'published'], default: 'draft' },
    views:     { type: 'number',  default: 0 },
    published: { type: 'boolean', default: false },
    tags:      { type: 'array' },
    metadata:  { type: 'json' },
    publishedAt: { type: 'date' },
  },

  relations: {
    author:   { target: 'User', type: 'many-to-one', required: true },
    comments: { target: 'Comment', type: 'one-to-many' },
    likes:    { target: 'User', type: 'many-to-many', through: 'post_likes' },
  },

  indexes: [
    { fields: { slug: 'asc' }, unique: true },
    { fields: { status: 'asc', publishedAt: -1 } },
    { fields: { author: 'asc' } },
  ],
}

// Count users by role
const stats = await userRepo.aggregate<{ role: string; count: number }>([
  { $match: { status: 'active' } },
  { $group: { _by: 'role', count: { $sum: 1 } } },
  { $sort: { count: -1 } },
])
// → [{ role: 'admin', count: 5 }, { role: 'user', count: 142 }]

// Sum revenue by month
const revenue = await orderRepo.aggregate([
  { $match: { status: 'paid' } },
  { $group: { _by: 'month', total: { $sum: 'amount' } } },
])

// Top 10 most viewed posts
const top = await postRepo.aggregate([
  { $match: { status: 'published' } },
  { $sort: { views: -1 } },
  { $limit: 10 },
])

// Schema with relations
const OrderSchema: EntitySchema = {
  name: 'Order',
  collection: 'orders',
  timestamps: true,
  fields: {
    total:  { type: 'number', required: true },
    status: { type: 'string', default: 'pending' },
  },
  relations: {
    customer: { target: 'User',    type: 'many-to-one', required: true },
    items:    { target: 'Product', type: 'many-to-many', through: 'order_items' },
  },
  indexes: [],
}

// Populate relations
const order = await orderRepo.findByIdWithRelations(orderId, ['customer', 'items'])
// → { id, total, status, customer: { id, email, ... }, items: [{ id, name, price }] }

// Filter with relations
const orders = await orderRepo.findWithRelations(
  { status: 'pending' },
  ['customer'],
  { sort: { createdAt: -1 }, limit: 50 }
)

import {
  saveJarFile,      // Save an uploaded JAR to jar_files/
  deleteJarFile,    // Delete a JAR from jar_files/
  listJarFiles,     // List all JARs with dialect detection
  getJdbcDialectStatus,  // Status of each JDBC dialect (JAR present?)
  detectDialectFromJar,  // Detect dialect from JAR filename
} from '@mostajs/orm'

// src/app/api/setup/upload-jar/route.ts
import { createUploadJarHandlers } from '@mostajs/setup/api/upload-jar'

const { GET, POST, DELETE } = createUploadJarHandlers()
export { GET, POST, DELETE }

import { BridgeManager } from '@mostajs/orm'

const manager = BridgeManager.getInstance()
// Bridges are managed automatically by the dialect's connect()
// Multiple bridges can run simultaneously on incrementing ports

const dialect = await getDialect({
  dialect: 'oracle',
  uri: 'oracle://user:pass@localhost:1521/XEPDB1',
  showSql: true,
  formatSql: true,
  highlightSql: true,       // SQL keywords in yellow, tables in green, params in magenta
  schemaStrategy: 'update',
  poolSize: 20,
})

// Two entity types sharing ONE table "entities"
const ArticleSchema: EntitySchema = {
  name: 'Article',
  collection: 'entities',          // shared table
  discriminator: '_type',           // discriminator column
  discriminatorValue: 'article',    // value for this type
  fields: { title: { type: 'string' }, body: { type: 'text' } },
  // ...
}

const PageSchema: EntitySchema = {
  name: 'Page',
  collection: 'entities',          // same table
  discriminator: '_type',
  discriminatorValue: 'page',
  fields: { title: { type: 'string' }, slug: { type: 'string' } },
  // ...
}

const articleRepo = new BaseRepository(ArticleSchema, dialect)
const pageRepo = new BaseRepository(PageSchema, dialect)

await articleRepo.findAll()        // SELECT * FROM entities WHERE _type = 'article'
await pageRepo.findAll()           // SELECT * FROM entities WHERE _type = 'page'
await articleRepo.findById(pageId) // Returns null — cross-type isolation
await articleRepo.count()          // Only counts articles

const CommentSchema: EntitySchema = {
  name: 'Comment',
  collection: 'comments',
  softDelete: true,                 // adds deletedAt column
  fields: { text: { type: 'string' } },
  // ...
}

const repo = new BaseRepository(CommentSchema, dialect)
await repo.delete(id)              // UPDATE comments SET deletedAt = NOW() WHERE id = ?
await repo.findAll()               // Automatically filters: WHERE deletedAt IS NULL
await repo.count()                 // Only counts non-deleted
await repo.findById(deletedId)     // Returns null

// Query deleted records explicitly
await repo.findAll({ deletedAt: { $ne: null } })

import { EntityService } from '@mostajs/orm'

const service = new EntityService(dialect, schemas)

// CRUD via canonical OrmRequest/OrmResponse
const response = await service.execute({
  op: 'findAll',
  entity: 'User',
  filter: { status: 'active' },
})

// Events for real-time (SSE, WebSocket)
service.on('entity.created', ({ entity, data }) => { /* broadcast */ })
service.on('entity.updated', ({ entity, data }) => { /* broadcast */ })
service.on('entity.deleted', ({ entity, id }) => { /* broadcast */ })

import { diffSchemas, generateMigrationSQL } from '@mostajs/orm'

const ops = diffSchemas(oldSchemas, newSchemas)
// [{ type: 'addField', entity: 'User', field: 'phone', def: { type: 'string' } }]

const sql = generateMigrationSQL(ops, 'postgres')
// ["ALTER TABLE users ADD COLUMN phone TEXT"]