whisky/graphql/whisky.js

const graphql = require("graphql");
const sqlite3 = require('sqlite3').verbose();

const database = new sqlite3.Database("whiskies.db");

const createWhiskiesTable = () => {
  const query = `
    CREATE TABLE IF NOT EXISTS Whiskies (
      code text UNIQUE PRIMARY KEY,
      description text,
      lastSeen text,
      size integer,
      price real
    )`;
  return database.run(query);
}

const createLocationsTable = () => {
  const query = `
    CREATE TABLE IF NOT EXISTS Locations (
      code text UNIQUE PRIMARY KEY,
      city text,
      address text,
      latitude real,
      longitude real,
      phone text
    )`;
  return database.run(query);
}

const createInventoriesTable = () => {
  const query = `
    CREATE TABLE IF NOT EXISTS Inventories (
      id integer PRIMARY KEY,
      whisky integer,
      location integer,
      quantity integer,
      updated text
    )`;
    return database.run(query);
}

createWhiskiesTable();
createInventoriesTable();
createLocationsTable();

const WhiskyType = new graphql.GraphQLObjectType({
  name: "Whisky",
  fields: () => ({
    code: { type: graphql.GraphQLString },
    description: { type: graphql.GraphQLString },
    lastSeen: { type: graphql.GraphQLString },
    size: { type: graphql.GraphQLInt },      
    price: { type: graphql.GraphQLFloat },
    inventories: { type: graphql.GraphQLList(InventoryType) },
    quantity: { type: graphql.GraphQLInt },
    updated: { type: graphql.GraphQLString }
  })
});

const LocationType = new graphql.GraphQLObjectType({
  name: "Location",
  fields: () => ({
    code: { type: graphql.GraphQLString },
    address: { type: graphql.GraphQLString },
    city: { type: graphql.GraphQLString },
    phone: { type: graphql.GraphQLString },
    longitude: { type: graphql.GraphQLFloat },
    latitude: { type: graphql.GraphQLFloat },
    inventories: { type: graphql.GraphQLList(InventoryType) }      
  })
});

const InventoryType = new graphql.GraphQLObjectType({
  name: "Inventory",
  fields: {
    id: { type: graphql.GraphQLID },
    location: { type: LocationType },
    whisky: { type: WhiskyType },
    quantity: { type: graphql.GraphQLInt },
    updated: { type: graphql.GraphQLString }
  }
});

const buildWhiskies = (rows) => {
  const whiskies = {};
  rows.forEach(row => {
    let whisky = {};
    if (!(row.code in whiskies)) {
      whisky = {
        description: row.description, 
        price: row.price, 
        size: row.size, 
        code: row.code,
        inventories: [],
        quantity: 0,
        updated: 0
      }
      whiskies[row.code] = whisky;
    } else {
      whisky = whiskies[row.code];
    }
    if (row.quantity) {
      whisky.inventories.push({
        location: {
          code: row.location,
          city: row.city,
          address: row.address,
          phone: row.phone,
          latitude: row.latitude,
          longitude: row.longitude
        },
        quantity: row.quantity,
        updated: row.updated
      });
      whisky.updated = (new Date(row.updated) > new Date(whisky.updated)) ? row.updated : whisky.updated;
      whisky.quantity += row.quantity;
    }
  });
  const results = [];
  for (let key in whiskies) {
    results.push(whiskies[key]);
  }
  return results;
};

const buildLocations = (rows) => {
  const locations = {};
  rows.forEach(row => {
    let location = {};
    if (!(row.code in locations)) {
      location = {
        code: row.code,
        address: row.address,
        city: row.city,
        phone: row.phone,
        latitude: row.latitude,
        longitude: row.longitude,
        inventories: []
      };
      locations[row.code] = location;
    } else {
      location = locations[row.code];
    }
    if (row.quantity) {
      location.inventories.push({
        whisky: {
          code: row.whisky,
          description: row.description, 
          price: row.price, 
          size: row.size,
        },
        quantity: row.quantity,
        updated: row.updated
      });
    }
  });
  const results = [];
  for (let key in locations) {
    results.push(locations[key]);
  }
  return results;
};

const buildInventories = (rows) => {
  const inventories = [];
  rows.forEach(row => {
    const inventory = {
      whisky: { code: row.whisky, description: row.description, price: row.price, size: row.size },
      location: { code: row.location, address: row.address, city: row.city, phone: row.phone, latitude: row.latitude, longitude: row.longitude },
      quantity: row.quantity,
      updated: row.updated
    };
    inventories.push(inventory);
  });
  return inventories;
};

var queryType = new graphql.GraphQLObjectType({
  name: 'Query',
  fields: {
    Whiskies: {
      type: graphql.GraphQLList(WhiskyType),
      args: {
        code: { type: graphql.GraphQLString },
      },
      resolve: (root, {code}, context, info) => {
        return new Promise((resolve, reject) => {
          if (code && code.trim() != "") {
            code = `${code.replace(/%/g, '')}%`;
          }
          database.all(
            "SELECT w.*,i.quantity,i.updated " +
            "FROM Whiskies AS w " +
            "LEFT JOIN Inventories AS i ON w.code=i.whisky " +
            (code ? "WHERE w.code LIKE (?) OR w.description LIKE (?)" : "") +
            ";", [code, code], function(err, rows) {  
            if (err) { console.error(err); return reject(null); }
            resolve(buildWhiskies(rows));
          });
        });
      }
    },
    Whisky: {
      type: WhiskyType,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }               
      },
      resolve: (root, {code}, context, info) => {
        return new Promise((resolve, reject) => {
          database.all(
            "SELECT w.*,l.code AS 'location',l.address,l.city,l.phone,l.latitude,l.longitude,i.quantity,i.updated " +
            "FROM Whiskies AS w " +
            "LEFT JOIN Inventories AS i ON w.code=i.whisky " +
            "LEFT JOIN Locations AS l ON l.code=i.location " +
            "WHERE w.code = (?);", [code], function(err, rows) {  
            if (err) { console.error(err); return reject(null); }
            resolve(buildWhiskies(rows)[0]);
          });
        });
      }
    },
    Locations: {
      type: graphql.GraphQLList(LocationType),
      resolve: (root, args, context, info) => {
        console.log(`Locations resolve`);
        return new Promise((resolve, reject) => {
          database.all(
            "SELECT l.*,w.code AS 'whisky',w.description,w.price,w.size,i.quantity,i.updated " +
            "FROM Locations AS l " +
            "LEFT JOIN Inventories AS i ON l.code=i.location " +
            "LEFT JOIN Whiskies AS w ON w.code=i.whisky " +
            ";", function(err, rows) {  
            if (err) { console.error(err); return reject(null); }
            resolve(buildLocations(rows));
          });
        });
      }
    },
    Location: {
      type: LocationType,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }               
      },
      resolve: (root, {code}, context, info) => {
        console.log(`Location resolve`);
        return new Promise((resolve, reject) => {
          database.all(
            "SELECT l.*,w.code AS 'whisky',w.description,w.price,w.size,i.quantity,i.updated " +
            "FROM Locations AS l " +
            "INNER JOIN Inventories AS i ON l.code=i.location " +
            "INNER JOIN Whiskies AS w ON w.code=i.whisky " +
            "WHERE code = (?);", [code], function(err, rows) {                           
            if (err) { console.error(err); return reject(null); }
            resolve(buildLocations(rows)[0]);
          });
        });
      }
    },
    Inventories: {
      type: graphql.GraphQLList(InventoryType),
      resolve: (root, args, context, info) => {
        console.log(`Inventories resolve`);
        return new Promise((resolve, reject) => {
          database.all(
            "SELECT * FROM Inventories AS i " +
            "INNER JOIN Whiskies AS w ON w.code=i.whisky " +
            "INNER JOIN Locations AS l ON l.code=i.location;", function(err, rows) {
            if (err) { console.error(err); return reject(null); }
            resolve(buildInventories(rows));            
          });
        });
      }
    },
    Inventory: {
      type: InventoryType,
      args: {
        location: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        whisky: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
      },
      resolve: (root, {location, whisky}, context, info) => {
        console.log(`Inventory resolve`, root, id, context, info);
        return new Promise((resolve, reject) => {
          database.all(
            "SELECT * FROM Inventories AS i " +
            "INNER JOIN Whiskies AS w ON w.code=i.whisky " +
            "INNER JOIN Locations AS l ON l.code=i.location " +
            "WHERE i.whisky = (?) AND i.location = (?);", [whisky, location], function(err, rows) {                           
            if (err) { console.error(err); return reject(null); }
            resolve(buildInventories(rows)[0]);            
          });
        });
      }
    }

  }
});

var mutationType = new graphql.GraphQLObjectType({
  name: 'Mutation',
  fields: {
    createWhisky: {
      type: WhiskyType,
      args: {
        description: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        lastSeen: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        size: { type: new graphql.GraphQLNonNull(graphql.GraphQLInt) },
        price: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) }
      },
      resolve: (root, {description, code, lastSeen, size, price}) => {
        return new Promise((resolve, reject) => {
          database.run('INSERT INTO Whiskies (description, code, lastSeen, size, price) VALUES (?,?,?,?,?);',
              [description, code, lastSeen, size, price], (err) => {
            if (err) { console.error(err); return reject(null); }
            database.get("SELECT last_insert_rowid() AS id", (_err, row) => {
              if (_err) { console.error(_err); return reject(null); }
              resolve({ description, code, lastSeen, size, price });
            });
          });
        })
      }
    },
    updateWhisky: {
      type: graphql.GraphQLString,
      args: {
        description: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        lastSeen: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        size: { type: new graphql.GraphQLNonNull(graphql.GraphQLInt) },
        price: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) }
      },
      resolve: (root, {code, description, lastSeen, size, price}) => {
        return new Promise((resolve, reject) => {
          database.run('UPDATE Whiskies SET description = (?), lastSeen = (?), size = (?), price = (?) WHERE code = (?);',
              [description, lastSeen, size, price, code], (err) => {
            if (err) { console.error(err); return reject(err); }
            resolve(`Whisky #${code} updated`);
          });
        })
      }
    },
    deleteWhisky: {
      type: graphql.GraphQLString,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }               
      },
      resolve: (root, {code}) => {
        return new Promise((resolve, reject) => {
          database.run('DELETE from Whiskies WHERE code =(?);', [code], (err) => {
            if (err) { console.error(err); return reject(err); }
            resolve(`Whisky #${code} deleted`);                    
          });
        })
      }
    },
    createLocation: {
      type: LocationType,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        address: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        city: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        phone: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        longitude: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) },
        latitude: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) }
      },
      resolve: (root, {code, address, city, phone, longitude, latitude}) => {
        return new Promise((resolve, reject) => {
          database.run('INSERT INTO Locations (code, address, city, phone, longitude, latitude) VALUES (?,?,?,?,?,?);',
              [code, address, city, phone, longitude, latitude], (err) => {
            if (err) { console.error(err); return reject(null); }
            resolve({ code, address, city, phone, longitude, latitude });
          });
        })
      }
    },
    updateLocation: {
      type: graphql.GraphQLString,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        address: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        city: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        phone: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        longitude: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) },
        latitude: { type: new graphql.GraphQLNonNull(graphql.GraphQLFloat) }
      },
      resolve: (root, {code, address, city, phone, longitude, latitude}) => {
        return new Promise((resolve, reject) => {
          database.run('UPDATE Locations SET address = (?), city = (?), phone = (?), longitude = (?), latitude = (?) WHERE code = (?);',
              [address, city, phone, longitude, latitude, code], (err) => {
            if (err) { console.error(err); return reject(null); }
            resolve(`Location #${code} updated`);
          });
        })
      }
    },
    deleteLocation: {
      type: graphql.GraphQLString,
      args: {
        code: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }               
      },
      resolve: (root, {code}) => {
        return new Promise((resolve, reject) => {
          database.run('DELETE from Locations WHERE code = (?);', [code], (err) => {
            if (err) { console.error(err); return reject(null); }
            resolve(`Location #${code} deleted`);                    
          });
        })
      }
    },
    createInventory: {
      type: InventoryType,
      args: {
        location: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        whisky: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        quantity: { type: new graphql.GraphQLNonNull(graphql.GraphQLInt) },
        updated: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }
      },
      resolve: (root, {location, whisky, quantity, updated}) => {
        console.log('createInventory');
        return new Promise((resolve, reject) => {
          database.run('INSERT INTO Inventories (location, whisky, quantity, updated) VALUES (?,?,?,?);',
              [location, whisky, quantity, updated], (err) => {
            if (err) { console.error(err); return reject(null); }
            database.get("SELECT last_insert_rowid() as id", (_err, row) => {
              if (_err) { console.error(_err); return reject(null); }
              database.all(
                "SELECT * FROM Inventories AS i " +
                "INNER JOIN Whiskies AS w ON w.code=i.whisky " +
                "INNER JOIN Locations AS l ON l.code=i.location " +
                "WHERE i.whisky = (?) AND i.location = (?);", [whisky, location], function(err, rows) {                           
                if (err) { console.error(err); return reject(null); }
                resolve(buildInventories(rows)[0]);            
              });
            });
          });
        })
      }
    },
    updateInventory: {
      type: graphql.GraphQLString,
      args: {
        location: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        whisky: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        quantity: { type: new graphql.GraphQLNonNull(graphql.GraphQLInt) },
        updated: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) }
      },
      resolve: (root, {location, whisky, quantity, updated}) => {
        console.log('updateInventory');
        return new Promise((resolve, reject) => {
          database.run('UPDATE Inventories SET quantity = (?), updated = (?) WHERE location = (?) AND whisky = (?);',
              [ quantity, updated, location, whisky], (err) => {
            if (err) { console.error(err); return reject(null); }
            resolve(`Inventory for ${location} of ${whisky} updated to ${quantity}:${updated}`);
          });
        })
      }
    },
    deleteInventory: {
      type: graphql.GraphQLString,
      args: {
        location: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
        whisky: { type: new graphql.GraphQLNonNull(graphql.GraphQLString) },
      },
      resolve: (root, {location, whisky}) => {
        console.log('deleteInventory');
        return new Promise((resolve, reject) => {
          database.run('DELETE from Inventories WHERE location = (?) AND whisky = (?);', [location, whisky], (err) => {
            if (err) { console.error(err); return reject(err); }
            resolve(`Deleted inventory for ${location} of ${whisky}`);                    
          });
        })
      }
    }
  }
});

const schema = new graphql.GraphQLSchema({
  query: queryType,
  mutation: mutationType 
});

module.exports = {
  schema
}