'use strict';

const Sequelize = require('sequelize');
console.log(Sequelize.version);

const sequelize = new Sequelize({ dialect: 'sqlite', storage: ':memory:', operatorsAliases: false, logging: console.log });

const Project = sequelize.define('Project', { name: Sequelize.STRING });
const LevelTwo = sequelize.define('LevelTwo', { name: Sequelize.STRING });
const LevelThree = sequelize.define('LevelThree', { type: Sequelize.INTEGER });

Project.hasMany(LevelTwo);
LevelTwo.belongsTo(Project);

LevelTwo.hasMany(LevelThree, { as: 'type_ones' });
LevelTwo.hasMany(LevelThree, { as: 'type_twos' });
LevelThree.belongsTo(LevelTwo);

function callQuery() {
    return Project.find({
        where: { id: 1 },
        include: [
            {
                model: LevelTwo,
                include: [
                    {
                        model: LevelThree,
                        as: 'type_ones',
                        where: { type: 0 },
                        separate: true,
                        limit: 1,
                        order: [['createdAt', 'DESC']]
                    },
                    {
                        model: LevelThree,
                        as: 'type_twos',
                        where: { type: 1 },
                        separate: true,
                        limit: 1,
                        order: [['createdAt', 'DESC']]
                    }
                ]
            }
        ]
    });
}

sequelize.sync()
    .then(function () {
        return Sequelize.Promise.all([
            Project.create({ name: 'testProject' }),
            LevelTwo.create({ name: 'testL2-1' }),
            LevelTwo.create({ name: 'testL2-2' })
        ]);
    })
    .bind({})
    .spread(function (project, L2_1, L2_2) {
        this.project = project;
        this.L2_1 = L2_1;
        this.L2_2 = L2_2;

        return this.project.addLevelTwo(this.L2_1);
    })
    .then(function () {
        return callQuery();
    })
    .then(function (result) {
        console.log(result.get({ plain: true }));

        return this.project.addLevelTwo(this.L2_2);
    })
    .then(function () {
        return callQuery();
    })
    .then(function (result) {
        console.log(result.get({ plain: true }));
    });