Relationships

/**
 * Define hasOne relationship - this model owns foreign key in target table
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} [foreignKey] - Foreign key in target table (default: singular_table_name + '_id')
 * @returns {Relation} Relation instance for chaining
 */
hasOne(Target: string | ModelConstructor, foreignKey?: string): Relation;

/**
 * Define belongsTo relationship - this model contains foreign key to target
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} [foreignKey] - Foreign key in this table (default: singular_target_table + '_id')
 * @returns {Relation} Relation instance for chaining
 */
belongsTo(Target: string | ModelConstructor, foreignKey?: string): Relation;

// User hasOne Profile
const User = bookshelf.model('User', {
  tableName: 'users',
  profile() {
    return this.hasOne('Profile'); // foreign key: user_id in profiles table
  }
});

// Profile belongsTo User  
const Profile = bookshelf.model('Profile', {
  tableName: 'profiles', 
  user() {
    return this.belongsTo('User'); // foreign key: user_id in profiles table
  }
});

// Custom foreign key
const Account = bookshelf.model('Account', {
  tableName: 'accounts',
  owner() {
    return this.belongsTo('User', 'owner_id'); // custom foreign key
  }
});

// Fetch with relation
const user = await new User({id: 1}).fetch({withRelated: ['profile']});
const profile = user.related('profile');

/**
 * Define hasMany relationship - this model is referenced by many target models
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} [foreignKey] - Foreign key in target table (default: singular_table_name + '_id')
 * @returns {Relation} Relation instance for chaining
 */
hasMany(Target: string | ModelConstructor, foreignKey?: string): Relation;

// User hasMany Posts
const User = bookshelf.model('User', {
  tableName: 'users',
  posts() {
    return this.hasMany('Post'); // foreign key: user_id in posts table
  }
});

// Post belongsTo User
const Post = bookshelf.model('Post', {
  tableName: 'posts',
  user() {
    return this.belongsTo('User'); // foreign key: user_id in posts table
  }
});

// Custom foreign key
const Author = bookshelf.model('Author', {
  tableName: 'authors',
  books() {
    return this.hasMany('Book', 'author_id'); // custom foreign key
  }
});

// Fetch with relation
const user = await new User({id: 1}).fetch({withRelated: ['posts']});
const posts = user.related('posts'); // Collection of posts

/**
 * Define belongsToMany relationship - many-to-many through pivot table
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} [tableName] - Pivot table name (default: alphabetical table names joined by _)
 * @param {string} [foreignKey] - This model's foreign key in pivot (default: singular_table_name + '_id')
 * @param {string} [otherKey] - Target model's foreign key in pivot (default: singular_target_table + '_id')
 * @returns {Relation} Relation instance for chaining
 */
belongsToMany(Target: string | ModelConstructor, tableName?: string, foreignKey?: string, otherKey?: string): Relation;

// User belongsToMany Role (through user_roles pivot table)
const User = bookshelf.model('User', {
  tableName: 'users',
  roles() {
    return this.belongsToMany('Role'); // pivot table: roles_users (alphabetical)
  }
});

// Role belongsToMany User
const Role = bookshelf.model('Role', {
  tableName: 'roles',
  users() {
    return this.belongsToMany('User'); // same pivot table
  }
});

// Custom pivot table and keys
const Post = bookshelf.model('Post', {
  tableName: 'posts',
  tags() {
    return this.belongsToMany('Tag', 'post_tags', 'post_id', 'tag_id');
  }
});

// Include pivot columns
const Tag = bookshelf.model('Tag', {
  tableName: 'tags',
  posts() {
    return this.belongsToMany('Post', 'post_tags', 'tag_id', 'post_id')
      .withPivot(['created_at', 'order']);
  }
});

// Working with many-to-many relations
const user = await new User({id: 1}).fetch({withRelated: ['roles']});
const roles = user.related('roles'); // Collection with pivot data

// Attach/detach relations
await user.roles().attach([1, 2, 3]); // Attach role IDs
await user.roles().detach([2]); // Detach specific role
await user.roles().detach(); // Detach all roles

// Update pivot attributes
await user.roles().updatePivot({created_at: new Date()});

/**
 * Define through relationship - access related models through intermediate model
 * @param {string|ModelConstructor} Interim - Intermediate model constructor or registered name  
 * @param {string} [throughForeignKey] - Foreign key from this model to interim
 * @param {string} [otherKey] - Foreign key from interim to target
 * @returns {Relation} Relation instance for chaining
 */
through(Interim: string | ModelConstructor, throughForeignKey?: string, otherKey?: string): Relation;

// Country hasMany Cities through States
const Country = bookshelf.model('Country', {
  tableName: 'countries',
  states() {
    return this.hasMany('State');
  },
  cities() {
    return this.hasMany('City').through('State');
  }
});

const State = bookshelf.model('State', {
  tableName: 'states',
  country() {
    return this.belongsTo('Country');
  },
  cities() {  
    return this.hasMany('City');
  }
});

const City = bookshelf.model('City', {
  tableName: 'cities',
  state() {
    return this.belongsTo('State');
  }
});

// Fetch cities through states
const country = await new Country({id: 1}).fetch({withRelated: ['cities']});
const cities = country.related('cities'); // All cities in country's states

/**
 * Define morphOne relationship - polymorphic one-to-one
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} name - Relation name for column naming
 * @param {string[]} [columnNames] - Custom column names [type_column, id_column]
 * @param {*} [morphValue] - Value stored in type column for this model
 * @returns {Relation} Relation instance for chaining
 */
morphOne(Target: string | ModelConstructor, name: string, columnNames?: string[], morphValue?: any): Relation;

/**
 * Define morphMany relationship - polymorphic one-to-many
 * @param {string|ModelConstructor} Target - Target model constructor or registered name
 * @param {string} name - Relation name for column naming
 * @param {string[]} [columnNames] - Custom column names [type_column, id_column]
 * @param {*} [morphValue] - Value stored in type column for this model
 * @returns {Relation} Relation instance for chaining
 */
morphMany(Target: string | ModelConstructor, name: string, columnNames?: string[], morphValue?: any): Relation;

/**
 * Define morphTo relationship - polymorphic belongs-to
 * @param {string} relationName - Name of the relation
 * @param {...*} targets - Target model definitions or column names
 * @returns {Relation} Relation instance for chaining
 */
morphTo(relationName: string, ...targets: any[]): Relation;

// Photo can belong to User or Post
const Photo = bookshelf.model('Photo', {
  tableName: 'photos', // has photoable_type and photoable_id columns
  photoable() {
    return this.morphTo('photoable', 'User', 'Post');
  }
});

// User morphMany Photos
const User = bookshelf.model('User', {
  tableName: 'users',
  photos() {
    return this.morphMany('Photo', 'photoable'); // morphValue defaults to 'User'
  }
});

// Post morphMany Photos  
const Post = bookshelf.model('Post', {
  tableName: 'posts',
  photos() {
    return this.morphMany('Photo', 'photoable'); // morphValue defaults to 'Post'
  }
});

// Custom column names and morph value
const Comment = bookshelf.model('Comment', {
  tableName: 'comments', // has owner_type and owner_id columns
  owner() {
    return this.morphTo('owner', ['owner_type', 'owner_id'], 'User', 'Post');
  }
});

// Fetch polymorphic relations
const photo = await new Photo({id: 1}).fetch({withRelated: ['photoable']});
const owner = photo.related('photoable'); // Could be User or Post

const user = await new User({id: 1}).fetch({withRelated: ['photos']});
const photos = user.related('photos'); // Photos belonging to this user

/**
 * Add where clause to relation query
 * @param {...*} args - Where clause arguments
 * @returns {Relation} Relation instance for chaining
 */
where(...args: any[]): Relation;

/**
 * Add query constraints to relation
 * @param {Function} callback - Function to modify relation query
 * @returns {Relation} Relation instance for chaining
 */
query(callback: (queryBuilder: Knex.QueryBuilder) => void): Relation;

const User = bookshelf.model('User', {
  tableName: 'users',
  
  // Published posts only
  publishedPosts() {
    return this.hasMany('Post').where('status', 'published');
  },
  
  // Recent posts with query callback
  recentPosts() {
    return this.hasMany('Post').query(qb => {
      qb.where('created_at', '>', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
        .orderBy('created_at', 'desc');
    });
  },
  
  // Active roles only
  activeRoles() {
    return this.belongsToMany('Role').where('active', true);
  }
});

// Eager loading with withRelated option
const options = {
  withRelated: [
    'posts',                    // Load posts relation
    'posts.comments',           // Load comments on each post
    'posts.comments.author',    // Load author of each comment
    'profile',                  // Load user profile
    'roles'                     // Load user roles
  ]
};

// Fetch with multiple relations
const user = await new User({id: 1}).fetch(options);

// Access loaded relations
const posts = user.related('posts');        // Collection of posts
const profile = user.related('profile');    // Profile model
const roles = user.related('roles');        // Collection of roles

// Load relations after initial fetch
await user.load(['posts.tags', 'profile']);

// Access pivot data
const user = await new User({id: 1}).fetch({withRelated: ['roles']});
const roles = user.related('roles');

roles.forEach(role => {
  // Access pivot attributes
  const pivotData = role.pivot; // Contains pivot table data
  console.log('Role assigned at:', role.pivot.get('created_at'));
});

// Include specific pivot columns
const User = bookshelf.model('User', {
  roles() {
    return this.belongsToMany('Role').withPivot(['created_at', 'expires_at', 'notes']);
  }
});

// Modify pivot data
await user.roles().updatePivot({
  expires_at: new Date(Date.now() + 365 * 24 * 60 * 60 * 1000)
}, {
  query: qb => qb.where('role_id', 2) // Update specific pivot rows
});

// Complete relationship example
const User = bookshelf.model('User', {
  tableName: 'users',
  
  // One-to-one
  profile() {
    return this.hasOne('Profile');
  },
  
  // One-to-many
  posts() {
    return this.hasMany('Post');
  },
  
  publishedPosts() {
    return this.hasMany('Post').where('status', 'published');
  },
  
  // Many-to-many
  roles() {
    return this.belongsToMany('Role').withPivot(['created_at', 'expires_at']);
  },
  
  // Through relationship
  comments() {
    return this.hasMany('Comment').through('Post');
  },
  
  // Polymorphic
  photos() {
    return this.morphMany('Photo', 'photoable');
  }
});

const Post = bookshelf.model('Post', {
  tableName: 'posts', 
  
  // Belongs to
  author() {
    return this.belongsTo('User', 'user_id');
  },
  
  // One-to-many
  comments() {
    return this.hasMany('Comment');
  },
  
  // Many-to-many
  tags() {
    return this.belongsToMany('Tag', 'post_tags');
  },
  
  // Polymorphic
  photos() {
    return this.morphMany('Photo', 'photoable');
  }
});

// Usage
const user = await new User({id: 1}).fetch({
  withRelated: [
    'profile',
    'posts.comments.author',
    'posts.tags', 
    'roles',
    'photos'
  ]
});

interface Relation {
  where(...args: any[]): Relation;
  query(callback: (qb: Knex.QueryBuilder) => void): Relation;
  through(Interim: string | ModelConstructor, throughForeignKey?: string, otherKey?: string): Relation;
  withPivot(...columns: string[]): Relation;
}