exports.up = function (knex, Promise) {
    return knex.schema.createTable('messages_events', function (table) {
        table.increments('mid'); //autoincrementing (non-nullable) primary key
        table.timestamps(true, true); //adds timestamptz-type (a PostgresQL data type) updated_at and created_at columns

        table.string('title').notNullable();
        table.text('content');
        table.string('location').notNullable();
        table.dateTime('start_time').notNullable();
        table.dateTime('end_time').notNullable(); //TODO: add a CHECK (https://www.postgresql.org/docs/current/ddl-constraints.html) that start_time < end_time

        table.string('recipient',128).notNullable().defaultTo('noone')
            .references('gid').inTable('groups')
            .onDelete('SET DEFAULT'); //if recipient is deleted, direct to the special "no-one" group
        //TODO: for now, we support only 1 author (where as graphql schema indicates support for [Group] authors)
        table.string('author',128).notNullable()
            .references('gid').inTable('groups')
            .onDelete('CASCADE'); //delete message if author is deleted

        table.integer('for_announcement')
            .references('mid').inTable('messages_announcements')
            .onDelete('SET NULL');

        //TODO: add support for participatingGroups and participatingUsers from the graphql schema

    }).then(() => {
        //update 'announcements' table by adding the 'for_event' column
        knex.schema.table('announcements', function (table) {
            table.integer('for_event')
                .references('mid').inTable('messages_events')
                .onDelete('SET NULL');
        });
    });
};

exports.down = function (knex, Promise) {
    return knex.schema.dropTable('messages_events');
};