17th-Battalion-Tracker/api/db/index.js

// require dbconfig
const dbConfig = require("./config.js");


// create connection to database
const Sequelize = require("sequelize");
const sequelize = new Sequelize(
  dbConfig.DATABASE,
  dbConfig.USER,
  dbConfig.PASSWORD,
  {
    host: dbConfig.HOST,
    port: dbConfig.PORT,
    dialect: dbConfig.dialect,

    pool: {
      max: dbConfig.pool.max,
      min: dbConfig.pool.min,
      acquire: dbConfig.pool.acquire,
      idle: dbConfig.pool.idle
    }
  }
);

try {
  sequelize.authenticate();
} catch (error) {
  console.error('Unable to connect to the database:', error);
  return
}



const db = {};

db.Sequelize = Sequelize;
db.instance = sequelize;

db.Member = db.instance.define("Member", require("./models/Member.model.js"), { paranoid: true })
db.Award = db.instance.define("Award", require("./models/Award.model.js"), { paranoid: true })
db.Course = db.instance.define("Course", require("./models/Course.model.js"), { paranoid: true })
db.Rank = db.instance.define("Rank", require("./models/Rank.model.js"), { paranoid: true })

db.AwardAction = db.instance.define("AwardAction", require("./junctionModels/AwardAction.js"), { paranoid: true })
db.CourseEventTrainingReport = db.instance.define("CourseEventTrainingReport", require("./junctionModels/CourseEventTrainingReport.model.js"), { paranoid: true })
db.CourseEvent = db.instance.define("CourseEvent", require("./models/CourseEvent.js"), { paranoid: true })

// Members have ranks
db.Rank.hasMany(db.Member, {
  as: "members",
  foreignKey: "rankId"
})
db.Member.belongsTo(db.Rank, {
  as: "rank",
  foreignKey: "rankId"
})

// Members have statuses
db.MemberStatus = db.instance.define("MemberStatus", require("./models/MemberStatus.model.js"), { paranoid: true })
db.MemberStatus.hasMany(db.Member, {
  as: "members",
  foreignKey: "statusId"
})
db.Member.belongsTo(db.MemberStatus, {
  as: "status",
  foreignKey: "statusId"
})


// * AWARDS
// Awards have a creator
db.Award.belongsTo(db.Member, {
  as: "createdBy",
  foreignKey: "createdById"
})
db.Member.hasMany(db.Award, {
  as: "awardsCreated",
  foreignKey: "createdById"
})
// Awards have a last modified by
db.Award.belongsTo(db.Member, {
  as: "lastModifiedBy",
  foreignKey: "lastModifiedById"
})
db.Member.hasMany(db.Award, {
  as: "awardsLastModified",
  foreignKey: "lastModifiedById"
})
// Members are granted awards
db.Award.belongsToMany(db.Member, {
  through: db.AwardAction,
  as: "awardHolders",
  foreignKey: "recipientId",
});
db.Member.belongsToMany(db.Award, {
  through: db.AwardAction,
  as: "awardsEarned",
  foreignKey: "awardId"
});
// * AWARDS GRANTED/REVOKED
// Instances of award grants have a creator
db.AwardAction.belongsTo(db.Member, {
  as: "createdBy",
  foreignKey: "createdById"
})
db.Member.hasMany(db.AwardAction, {
  as: "awardGrantsCreated",
  foreignKey: "createdById"
})
// Instances of award grants have a last modified by
db.AwardAction.belongsTo(db.Member, {
  as: "lastModifiedBy",
  foreignKey: "lastModifiedById"
})
db.Member.hasMany(db.AwardAction, {
  as: "awardGrantsLastModified",
  foreignKey: "lastModifiedById"
})
// Instances of award grants have the acting member
db.AwardAction.belongsTo(db.Member, {
  as: "actor",
  foreignKey: "actorId"
})
db.Member.hasMany(db.AwardAction, {
  as: "awardGrantsGiven",
  foreignKey: "actorId"
})
// Instances of award grants have the recipient
db.AwardAction.belongsTo(db.Member, {
  as: "recipient",
  foreignKey: "recipientId"
})
db.Member.hasMany(db.AwardAction, {
  as: "awardGrantsReceived",
  foreignKey: "recipientId"
})
// Instances of award grants have the award
db.AwardAction.belongsTo(db.Award, {
  as: "award",
  foreignKey: "awardId"
})
db.Award.hasMany(db.AwardAction, {
  as: "awardGrants",
  foreignKey: "awardId"
})



// * COURSE EVENTS
// Events have a creator
db.CourseEvent.belongsTo(db.Member, {
  as: "createdBy",
  foreignKey: "createdById"
})
db.Member.hasMany(db.CourseEvent, {
  as: "courseEventsCreated",
  foreignKey: "createdById"
})
// Events have a last modified by
db.CourseEvent.belongsTo(db.Member, {
  as: "lastModifiedBy",
  foreignKey: "lastModifiedById"
})
db.Member.hasMany(db.CourseEvent, {
  as: "courseEventsLastModified",
  foreignKey: "lastModifiedById"
})
// Events have a course that's taught
db.CourseEvent.belongsTo(db.Course, {
  as: "courseTaught",
  foreignKey: "courseTaughtId"
})
db.Course.hasMany(db.CourseEvent, {
  as: "trainingsHeld",
  foreignKey: "courseTaughtId"
})
// Events have one or more trainer
db.CourseEvent.belongsToMany(db.Member, {
  through: "CourseEventsTrainers",
  as: "trainers",
  foreignKey: "trainerId"
})
db.Member.belongsToMany(db.CourseEvent, {
  through: "CourseEventsTrainers",
  as: "courseEventsTaught",
  foreignKey: "courseEventId"
})
// Events have one or more observer
db.CourseEvent.belongsToMany(db.Member, {
  through: "CourseEventsObservers",
  as: "observers",
  foreignKey: "courseEventId"
})
db.Member.belongsToMany(db.CourseEvent, {
  through: "CourseEventsObservers",
  as: "courseEventsObserved",
  foreignKey: "observerId"
})
// Events have one or more attendees, each of which passed or did not
db.CourseEvent.belongsToMany(db.Member, {
  through: db.CourseEventTrainingReport,
  as: "attendees",
  foreignKey: "attendeeId"
});
db.Member.belongsToMany(db.CourseEvent, {
  through: db.CourseEventTrainingReport,
  as: "courseEventsAttended",
  foreignKey: "courseEventId"
});


// * COURSES
// Courses have a creator
db.Course.belongsTo(db.Member, {
  as: "createdBy",
  foreignKey: "createdById"
})
db.Member.hasMany(db.Course, {
  as: "coursesCreated",
  foreignKey: "createdById"
})
// Courses have a last modified by
db.Course.belongsTo(db.Member, {
  as: "lastModifiedBy",
  foreignKey: "lastModifiedById"
})
db.Member.hasMany(db.Course, {
  as: "coursesLastModified",
  foreignKey: "lastModifiedById"
})
// Courses have SMEs
db.Course.belongsToMany(db.Member, {
  through: "CoursesSME",
  as: "sme",
  foreignKey: "smeId"
})
db.Member.belongsToMany(db.Course, {
  through: "CoursesSME",
  as: "coursesSMEFor",
  foreignKey: "courseId"
})



// Courses belong to award paths
db.Award.belongsToMany(db.Course, {
  through: "CoursesAwards",
  as: "coursesRequired",
  foreignKey: "courseId"
});
// Awards have pre-requisite courses
db.Course.belongsToMany(db.Award, {
  through: "CoursesAwards",
  as: "possibleAwards",
  foreignKey: "awardId"
});



module.exports = db;