import express from 'express';
import { Response } from "express-serve-static-core";
import {Client, Connection} from 'pg';
import format from 'pg-format';
// import pool from './pgpool';

export interface Student 
{
	Name: string,
	Age: number,
	Course: string
}

export interface StudentPatch 
{
	Id: number,
	Name?: string,
	Age?: number,
	Course?: string
}

export class PGStudentTable
{
	private GetClient(): Client
	{
		return new Client({
						host: "postgres",		// Service name in docker-compose
						port: 5432,
						user: "docker",
						password: "password",
						database: "mydb"
					});	
	}
	
	Insert = async (student: Student, res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Adding new student named ${student.Name}`);
		try
		{
			await client.connect();
			
			let query: string = format('INSERT INTO "Student"("Name", "Age", "Course") VALUES(%L, %L, %L)  RETURNING *', 
											student.Name,
											student.Age,
											student.Course);
			let results = await client.query(query);
			
			if(results.rowCount > 0)
			{
				console.log('New record added');
				res.status(201).send(results.rows[0]);
			}
			else
			{
				res.status(409).end();
			}			
		}
		catch(error)
		{
			console.log(error);
			res.status(403).send(error);
		}
		finally
		{
			await client.end();
		}	
	};
	
	Select = async (res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Reading all the students details`);
		try
		{
			await client.connect();
			
			let query: string = 'SELECT * FROM "Student"';
			let results = await client.query(query);
			
			if(results.rows.length > 0)
				console.log(`Read ${results.rows.length} records`)
			else
				console.log(`Database is empty`);
			
			res.send(results.rows);
		}
		catch(error)
		{
			console.log(error);
			res.status(403).send(error);
		}
		finally
		{
			await client.end();
		}	
	};
	
	SelectById = async (id: number, res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Looking for the student with id ${id}`);
		try
		{
			await client.connect();
			
			let query: string = format('SELECT * FROM "Student" WHERE "Id"=%L', id);
			let results = await client.query(query);
			
			if(results.rows.length > 0)
			{
				console.log(`Found ${results.rows.length} records`);
				res.send(results.rows[0]);
			}
			else
			{
				console.log(`Record not found for Id=${id}`);
				res.status(409).end();
			}
		}
		catch(error)
		{
			console.log(error);
			res.status(403).send(error);
		}
		finally
		{
			await client.end();
		}
	};
	
	SelectByName = async (name: string, res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Looking for all the students named ${name}`);
		try
		{
			await client.connect();
			
			let query: string = format('SELECT * FROM "Student" WHERE "Name"=%L', name);
			let results = await client.query(query);
			
			if(results.rows.length > 0)
				console.log(`Found ${results.rows.length} records`);
			else
				console.log(`Records not found for ${name}`);
			
			res.send(results.rows);
		}
		catch(error)
		{
			console.log(error);
			res.status(403).send(error);
		}
		finally
		{
			await client.end();
		}	
	};
	
	Delete = async (id: number, res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Delete the student with id = ${id}`);
		try
		{
			await client.connect();
			
			let query: string = format('DELETE FROM "Student" WHERE "Id"=%L', id);
			let results = await client.query(query);
			
			if(results.rowCount > 0)
			{
				console.log('Record deleted');
				res.send({});
			}
			else
			{
				console.log(`Failed to delete record for Id = ${id}`);
				res.status(409).end();
			}			
		}
		catch(error)
		{
			console.log(error);
			res.status(400).send(error);
		}
		finally
		{
			await client.end();
		}	
	};
	
	Patch = async (student: StudentPatch, res: Response): Promise<void> =>
	{		
		let client: Client = this.GetClient();
		console.log(`Modifying the student with Id = ${student.Id}`);
		try
		{
			//let query: string = 'UPDATE "Student" SET ';
			let queryArgs: string[] = [];
			let values: any[] = [];
			await client.connect();
			if(student.Name != null) { values.push(student.Name); queryArgs.push('"Name"=%L'); }
			if(student.Age != null) { values.push(student.Age); queryArgs.push('"Age"=%L'); }
			if(student.Course != null) { values.push(student.Course); queryArgs.push('"Course"=%L'); }
			values.push(student.Id);  // Id of record to change
	
			let query: string = format.withArray('UPDATE "Student" SET ' + queryArgs.join(",") + ' WHERE "Id"=%L', 
											values);
			let results = await client.query(query);
			
			if(results.rowCount > 0)
			{
				console.log('Modified the record');
				res.send({});
			}
			else
			{
				console.log(`Failed to modify record for Id = ${student.Id}`);
				res.status(409).end();
			}			
		}
		catch(error)
		{
			console.log(error);
			res.status(403).send(error);
		}
		finally
		{
			await client.end();
		}		
	};	
}