/*Built for the Long Haul, operate with Confidence*/

React

// frontend
import React, { useState, useEffect } from 'react';
import axios from 'axios';
import { Container, Typography, Box, TextField, Button, TableContainer, Table, TableHead, TableRow, TableCell, TableBody, Paper } from '@material-ui/core';

function App() {
  const [nawData, setNawData] = useState([]);
  const [firstname, setFirstname] = useState('');
  const [lastname, setLastname] = useState('');
  const [address, setAddress] = useState('');
  const [postalcode, setPostalcode] = useState('');
  const [city, setCity] = useState('');
  const [country, setCountry] = useState('');

  useEffect(() => {
    axios.get('api/person')
      .then(response => {
        setNawData(response.data);
      })
      .catch(error => {
        console.error('There was an error while trying to get person data:', error);
      });
  }, []);

  const handleSubmit = (e) => {
    e.preventDefault();
    const payload = { firstname, lastname, address, postalcode, city, country };

    axios.post('api/person', payload)
      .then(response => {
        setNawData([...nawData, { id: response.data.id, ...payload }]);
        setFirstname('');
        setLastname('');
        setAddress('');
        setPostalcode('');
        setCity('');
        setCountry('');
      })
      .catch(error => {
        console.error('There was an error trying to save a new person record', error);
      });
  };

  return (
    <Container>
      <Typography variant="h4" gutterBottom>
        Person Details
      </Typography>
      <form onSubmit={handleSubmit}>
        <Box display="flex" flexDirection="row" flexWrap="wrap" mb={2}>
          <Box mr={1}>
            <TextField label="Firstname" value={firstname} onChange={e => setFirstname(e.target.value)} />
          </Box>
          <Box mr={1}>
            <TextField label="Lastname" value={lastname} onChange={e => setLastname(e.target.value)} />
          </Box>
          <Box mr={1}>
            <TextField label="Address" value={address} onChange={e => setAddress(e.target.value)} />
          </Box>
          <Box mr={1}>
            <TextField label="Postalcode" value={postalcode} onChange={e => setPostalcode(e.target.value)} />
          </Box>
          <Box mr={1}>
            <TextField label="City" value={city} onChange={e => setCity(e.target.value)} />
          </Box>
          <Box mr={1}>
            <TextField label="Country" value={country} onChange={e => setCountry(e.target.value)} />
          </Box>
          <Button type="submit" variant="contained" color="primary">
            Save
          </Button>
        </Box>
      </form>
      <TableContainer component={Paper}>
        <Table>
          <TableHead>
            <TableRow>
              <TableCell>Firstname</TableCell>
              <TableCell>Lastname</TableCell>
              <TableCell>Address</TableCell>
              <TableCell>Postalcode</TableCell>
              <TableCell>City</TableCell>
              <TableCell>Country</TableCell>
            </TableRow>
          </TableHead>
          <TableBody>
            {nawData.map(record => (
              <TableRow key={record.id}>
                <TableCell>{record.firstname}</TableCell>
                <TableCell>{record.lastname}</TableCell>
                <TableCell>{record.address}</TableCell>
                <TableCell>{record.postalcode}</TableCell>
                <TableCell>{record.city}</TableCell>
                <TableCell>{record.country}</TableCell>
              </TableRow>
            ))}
          </TableBody>
        </Table>
      </TableContainer>
    </Container>
  );
}

export default App;

// backend
app.get('/api/person', (req, res) => {
  const connection = new Connection(config);

  connection.on('connect', err => {
    if (err) {
      console.error(err);
      res.status(500).send('Database connection failed');
      return;
    }

    const request = new Request('SELECT * FROM Persons', (err, rowCount) => {
      if (err) {
        throw err;
      }

      connection.close();
    });

    let results = []
    request.on('row', (columns) => {
      const record = {};
      columns.forEach(column => {
        record[column.metadata.colName] = column.value;
      });
      results.push(record);
    });

    request.on('doneInProc', (rowCount, more) => {
      console.log(results);
      res.json(results);
      console.log(rowCount + ' rows returned');
    });

    connection.execSql(request);
  });

  connection.connect();
});

app.post('/api/person', (req, res) => {
  const { firstname, lastname, address, postalcode, city, country } = req.body;
  const connection = new Connection(config);

  connection.on('connect', err => {
    if (err) {
      console.error(err);
      res.status(500).send('Database connection failed');
      return;
    }

    const request = new Request(
      `INSERT INTO Persons (firstname, lastname, address, postalcode, city, country)
      OUTPUT INSERTED.id
       VALUES (@firstname, @lastname, @address, @postalcode, @city, @country)`,
      (err, rowCount, rows) => {
        if (err) {
          console.error(err);
          res.status(500).send('Failed to insert data');
        } else {
          res.status(201);
        }
        connection.close();
      }
    );

    request.addParameter('firstname', TYPES.NVarChar, firstname);
    request.addParameter('lastname', TYPES.NVarChar, lastname);
    request.addParameter('address', TYPES.NVarChar, address);
    request.addParameter('postalcode', TYPES.NVarChar, postalcode);
    request.addParameter('city', TYPES.NVarChar, city);
    request.addParameter('country', TYPES.NVarChar, country);

    let results = []
    request.on('row', (columns) => {
      const record = {};
      columns.forEach(column => {
        record[column.metadata.colName] = column.value;
      });
      results.push(record);
    });

    request.on('doneInProc', (rowCount, more) => {
      res.json(results);
    });

    connection.execSql(request);
  });

  connection.connect();
});