// 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 }
)