/* ds0112Company.ecpg */
/* Diwas Sharma */
/* Due: 2017-10-31 */
/* CS 687-01 */

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

EXEC SQL INCLUDE sqlca;

EXEC SQL WHENEVER SQLERROR sqlprint;

int main(int argc, char *argv[])
{
    if(argc <= 1){
        printf("Insufficient arguments\n");
        return 0;
    }

    /* Parse argument */
    if(strcmp(argv[1], "-insert") == 0){
        EXEC SQL BEGIN DECLARE SECTION;
        char *ssn=NULL, *fname=NULL, *lname=NULL, *project_name=NULL;
        char *mgr_ssn=NULL, *mgr_fname=NULL, *mgr_lname=NULL;
        int pno, dnum;
        float hours, retrived_hours=0;

        char *iterator_ssn=NULL, *max_ssn=NULL, *max_fname=NULL, *max_lname=NULL;
        float iterator_hours=0, max_hours=0;
        EXEC SQL END DECLARE SECTION;

        EXEC SQL CONNECT TO unix:postgresql://localhost DATABASE USER USER_NAME USING PASSWORD;

        /* Assign parameters obtained from system */
        ssn = argv[3];
        pno = atoi(argv[5]);
        hours = atof(argv[7]);

        /* Retrive employee detail */
        EXEC SQL SELECT fname, lname INTO :fname, :lname FROM ds0112.employee WHERE ssn=:ssn;

        /* Retrive project info */
        EXEC SQL SELECT pname, dnum INTO :project_name, :dnum FROM ds0112.project WHERE pnumber=:pno;

        /* Check if pno already exists */
        EXEC SQL SELECT hours INTO :retrived_hours FROM ds0112.works_on WHERE essn=:ssn AND pno=:pno;
        if(retrived_hours > 0){
            printf("Employee %s %s is already working %f hours in project %s. The operation is rejected.\n", fname, lname, retrived_hours, project_name);
        }
        else{
            /* Check if number of hours is greater than 0.1 */
            if(hours < 0.1){
                printf("The number of hours should be greater than 0.1\n");
            }
            else{
                /* Perform INSERT */
                EXEC SQL INSERT INTO ds0112.works_on VALUES (:ssn, :pno, :hours);

                printf("Employee %s %s started to work %f hours in project %s.\n", fname, lname, hours, project_name);
            }
        }

        /* Display project info */
        /* Print project name */
        printf("Project name: %s\n", project_name);

        /* Obtain department info */
        EXEC SQL SELECT mgr_ssn INTO :mgr_ssn FROM ds0112.department WHERE dnumber=:dnum;

        /* Obtain manager information */
        EXEC SQL SELECT fname, lname INTO :mgr_fname, :mgr_lname FROM ds0112.employee WHERE ssn=:mgr_ssn;

        printf("Manager name: %s %s\n", mgr_fname, mgr_lname);

        /* Store total hours */
        float total_hours = 0;

        /* Make a database cursor for iteration */
        EXEC SQL DECLARE works_on_cursor CURSOR FOR SELECT essn, hours FROM ds0112.works_on WHERE pno=:pno;

        /* Open cursor */
        EXEC SQL OPEN works_on_cursor;

        EXEC SQL WHENEVER NOT FOUND DO BREAK;

        while (SQLCODE==0)
        {
            EXEC SQL FETCH IN works_on_cursor INTO :iterator_ssn, :iterator_hours;

            if(iterator_hours > max_hours){
             max_ssn = iterator_ssn;
             max_hours=iterator_hours;
            }
            total_hours += iterator_hours;

            iterator_ssn = NULL;
            iterator_hours = 0;
        }
        EXEC SQL WHENEVER NOT FOUND CONTINUE;

        /* Fetch Fname and lname of employee with maximum number of working hours */
        EXEC SQL SELECT fname, lname INTO :max_fname, :max_lname FROM ds0112.employee WHERE ssn=:max_ssn;

        printf("Total number of hours: %f\n", total_hours);
        printf("Employee works most on the project is %s %s with number of hours %f\n", max_fname, max_lname, max_hours);

        /* Close cursor */
        EXEC SQL CLOSE works_on_cursor;

        /* Commit changes and disconnect */
        EXEC SQL COMMIT;
        EXEC SQL DISCONNECT;
    }
    else if(strcmp(argv[1], "-delete") == 0){
        EXEC SQL BEGIN DECLARE SECTION;
        char      *ssn=NULL, *fname=NULL, *lname=NULL, *project_name=NULL;
        int       pno;
        float     hours, retrived_hours=0;
        EXEC SQL END DECLARE SECTION;

        EXEC SQL CONNECT TO unix:postgresql://localhost DATABASE USER USER_NAME USING PASSWORD;

        /* Assign parameters obtained from system */
        ssn = argv[3];
        pno = atoi(argv[5]);

        /* Retrive employee detail */
        EXEC SQL SELECT fname, lname INTO :fname, :lname FROM ds0112.employee WHERE ssn=:ssn;

        /* Retrive project info */
        EXEC SQL SELECT pname INTO :project_name FROM ds0112.project WHERE pnumber=:pno;

        /* Check if employee works in a project */
        EXEC SQL SELECT hours INTO :retrived_hours FROM ds0112.works_on WHERE essn=:ssn AND pno=:pno;
        if(retrived_hours == 0){
            printf("Employee %s %s was not working on project %s\n", fname, lname, project_name);
        }
        else{
            /* Delete tuple */
            EXEC SQL DELETE FROM ds0112.works_on WHERE essn=:ssn AND pno=:pno;

            printf("Employee %s %s stopped working on project %s\n", fname, lname, project_name);
        }

        /* Perform changes and disconnect */
        EXEC SQL COMMIT;
        EXEC SQL DISCONNECT;
    }
    else{
        printf("Operation not found\n");
        return 0;
    }

    return 0;
}