text: // Copyright 2019 Google LLC
  //
  // Licensed under the Apache License, Version 2.0 (the "License");
  // you may not use this file except in compliance with the License.
  // You may obtain a copy of the License at
  //
  //      http://www.apache.org/licenses/LICENSE-2.0
  //
  // Unless required by applicable law or agreed to in writing, software
  // distributed under the License is distributed on an "AS IS" BASIS,
  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  // See the License for the specific language governing permissions and
  // limitations under the License.
  
  'use strict';
  
  const express = require('express');
  const mysql = require('promise-mysql');
  const fs = require('fs');
  
  const app = express();
  app.set('view engine', 'pug');
  app.enable('trust proxy');
  
  // Automatically parse request body as form data.
  app.use(express.urlencoded({extended: false}));
  // This middleware is available in Express v4.16.0 onwards
  app.use(express.json());
  
  // Set Content-Type for all responses for these routes.
  app.use((req, res, next) => {
    res.set('Content-Type', 'text/html');
    next();
  });
  
  // Create a Winston logger that streams to Stackdriver Logging.
  const winston = require('winston');
  const {LoggingWinston} = require('@google-cloud/logging-winston');
  const loggingWinston = new LoggingWinston();
  const logger = winston.createLogger({
    level: 'info',
    transports: [new winston.transports.Console(), loggingWinston],
  });
  
  // [START cloud_sql_mysql_mysql_create_tcp_sslcerts]
  const createTcpPoolSslCerts = async config => {
    // Extract host and port from socket address
    const dbSocketAddr = process.env.DB_HOST.split(':');
  
    // Establish a connection to the database
    return mysql.createPool({
      user: process.env.DB_USER, // e.g. 'my-db-user'
      password: process.env.DB_PASS, // e.g. 'my-db-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
      host: dbSocketAddr[0], // e.g. '127.0.0.1'
      port: dbSocketAddr[1], // e.g. '3306'
      ssl: {
        sslmode: 'verify-full',
        ca: fs.readFileSync(process.env.DB_ROOT_CERT), // e.g., '/path/to/my/server-ca.pem'
        key: fs.readFileSync(process.env.DB_KEY), // e.g. '/path/to/my/client-key.pem'
        cert: fs.readFileSync(process.env.DB_CERT), // e.g. '/path/to/my/client-cert.pem'
      },
      // ... Specify additional properties here.
      ...config,
    });
  };
  // [END cloud_sql_mysql_mysql_create_tcp_sslcerts]
  
  // [START cloud_sql_mysql_mysql_create_tcp]
  const createTcpPool = async config => {
    // Extract host and port from socket address
    const dbSocketAddr = process.env.DB_HOST.split(':');
  
    // Establish a connection to the database
    return mysql.createPool({
      user: process.env.DB_USER, // e.g. 'my-db-user'
      password: process.env.DB_PASS, // e.g. 'my-db-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
      host: dbSocketAddr[0], // e.g. '127.0.0.1'
      port: dbSocketAddr[1], // e.g. '3306'
      // ... Specify additional properties here.
      ...config,
    });
  };
  // [END cloud_sql_mysql_mysql_create_tcp]
  
  // [START cloud_sql_mysql_mysql_create_socket]
  const createUnixSocketPool = async config => {
    const dbSocketPath = process.env.DB_SOCKET_PATH || '/cloudsql';
  
    // Establish a connection to the database
    return mysql.createPool({
      user: process.env.DB_USER, // e.g. 'my-db-user'
      password: process.env.DB_PASS, // e.g. 'my-db-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
      // If connecting via unix domain socket, specify the path
      socketPath: `${dbSocketPath}/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
      // Specify additional properties here.
      ...config,
    });
  };
  // [END cloud_sql_mysql_mysql_create_socket]
  
  const createPool = async () => {
    const config = {
      // [START cloud_sql_mysql_mysql_limit]
      // 'connectionLimit' is the maximum number of connections the pool is allowed
      // to keep at once.
      connectionLimit: 5,
      // [END cloud_sql_mysql_mysql_limit]
  
      // [START cloud_sql_mysql_mysql_timeout]
      // 'connectTimeout' is the maximum number of milliseconds before a timeout
      // occurs during the initial connection to the database.
      connectTimeout: 10000, // 10 seconds
      // 'acquireTimeout' is the maximum number of milliseconds to wait when
      // checking out a connection from the pool before a timeout error occurs.
      acquireTimeout: 10000, // 10 seconds
      // 'waitForConnections' determines the pool's action when no connections are
      // free. If true, the request will queued and a connection will be presented
      // when ready. If false, the pool will call back with an error.
      waitForConnections: true, // Default: true
      // 'queueLimit' is the maximum number of requests for connections the pool
      // will queue at once before returning an error. If 0, there is no limit.
      queueLimit: 0, // Default: 0
      // [END cloud_sql_mysql_mysql_timeout]
  
      // [START cloud_sql_mysql_mysql_backoff]
      // The mysql module automatically uses exponential delays between failed
      // connection attempts.
      // [END cloud_sql_mysql_mysql_backoff]
    };
    if (process.env.DB_HOST) {
      if (process.env.DB_ROOT_CERT) {
        return createTcpPoolSslCerts(config);
      } else {
        return createTcpPool(config);
      }
    } else {
      return createUnixSocketPool(config);
    }
  };
  
  const ensureSchema = async pool => {
    // Wait for tables to be created (if they don't already exist).
    await pool.query(
      `CREATE TABLE IF NOT EXISTS votes
        ( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL,
        candidate CHAR(6) NOT NULL, PRIMARY KEY (vote_id) );`
    );
    console.log("Ensured that table 'votes' exists");
  };
  
  const createPoolAndEnsureSchema = async () =>
    await createPool()
      .then(async pool => {
        await ensureSchema(pool);
        return pool;
      })
      .catch(err => {
        logger.error(err);
        throw err;
      });
  
  // Set up a variable to hold our connection pool. It would be safe to
  // initialize this right away, but we defer its instantiation to ease
  // testing different configurations.
  let pool;
  
  app.use(async (req, res, next) => {
    if (pool) {
      return next();
    }
    try {
      pool = await createPoolAndEnsureSchema();
      next();
    } catch (err) {
      logger.error(err);
      return next(err);
    }
  });
  
  // Serve the index page, showing vote tallies.
  app.get('/', async (req, res) => {
    pool = pool || (await createPoolAndEnsureSchema());
    try {
      // Get the 5 most recent votes.
      const recentVotesQuery = pool.query(
        'SELECT candidate, time_cast FROM votes ORDER BY time_cast DESC LIMIT 5'
      );
  
      // Get votes
      const stmt = 'SELECT COUNT(vote_id) as count FROM votes WHERE candidate=?';
      const tabsQuery = pool.query(stmt, ['TABS']);
      const spacesQuery = pool.query(stmt, ['SPACES']);
  
      // Run queries concurrently, and wait for them to complete
      // This is faster than await-ing each query object as it is created
      const recentVotes = await recentVotesQuery;
      const [tabsVotes] = await tabsQuery;
      const [spacesVotes] = await spacesQuery;
  
      res.render('index.pug', {
        recentVotes,
        tabCount: tabsVotes.count,
        spaceCount: spacesVotes.count,
      });
    } catch (err) {
      logger.error(err);
      res
        .status(500)
        .send(
          'Unable to load page. Please check the application logs for more details.'
        )
        .end();
    }
  });
  
  // Handle incoming vote requests and inserting them into the database.
  app.post('/', async (req, res) => {
    const {team} = req.body;
    const timestamp = new Date();
  
    if (!team || (team !== 'TABS' && team !== 'SPACES')) {
      return res.status(400).send('Invalid team specified.').end();
    }
  
    pool = pool || (await createPoolAndEnsureSchema());
    // [START cloud_sql_mysql_mysql_connection]
    try {
      const stmt = 'INSERT INTO votes (time_cast, candidate) VALUES (?, ?)';
      // Pool.query automatically checks out, uses, and releases a connection
      // back into the pool, ensuring it is always returned successfully.
      await pool.query(stmt, [timestamp, team]);
    } catch (err) {
      // If something goes wrong, handle the error in this section. This might
      // involve retrying or adjusting parameters depending on the situation.
      // [START_EXCLUDE]
      logger.error(err);
      return res
        .status(500)
        .send(
          'Unable to successfully cast vote! Please check the application logs for more details.'
        )
        .end();
      // [END_EXCLUDE]
    }
    // [END cloud_sql_mysql_mysql_connection]
  
    res.status(200).send(`Successfully voted for ${team} at ${timestamp}`).end();
  });
  
  const PORT = process.env.PORT || 8080;
  const server = app.listen(PORT, () => {
    console.log(`App listening on port ${PORT}`);
    console.log('Press Ctrl+C to quit.');
  });
  
  process.on('unhandledRejection', err => {
    console.error(err);
    throw err;
  });
  
  module.exports = server;
  