package datamysql

import (
	"database/sql"
	"fmt"

	"bitbucket.org/nemt/nemt-portal-api/domain/entity"
	"bitbucket.org/nemt/nemt-portal-api/infra/errors"
	"github.com/go-sql-driver/mysql"
	uuid "github.com/satori/go.uuid"
)

// visitRepo maps methods to database
type visitRepo struct {
	conn executor
}

func newVisitRepo(conn executor) *visitRepo {
	return &visitRepo{
		conn: conn,
	}
}

func (c *visitRepo) getProfileQuery(user entity.User) (query string, where string, err error) {
	if len(user.Profiles) > 0 {
		for _, p := range user.Profiles {
			switch p.Key {
			case "AD", "BCBSIAD", "BDCAD", "PLANAD":
				return
			case "SP", "SPT":
				switch p.Organization.Type.Key {
				case "techsupport", "bcbsi", "bcbsa", "plan":
					return
				case "provider":
					query = ` INNER JOIN viw_visit_provider f
                        							ON      f.visit_id    = a.visit_id `
					where = fmt.Sprintf(`	AND (f.organization_uuid = '%s' OR f.parent_organization_uuid = '%s') `, p.Organization.UUID, p.Organization.UUID)
					return
				}
			}
		}

		if query == "" && where == "" {
			return "", "", fmt.Errorf("Invalid Query")
		} else {
			return
		}
	} else {
		return "", "", fmt.Errorf("User has no profile to search")
	}
}

func (c *visitRepo) getQuery() string {
	return `SELECT 
								a.visit_id,
								a.visit_uuid,
								a.visit_status_id,
								b.visit_status_key,
								b.visit_status,
								a.user_id,
								c.user_uuid,
								c.name user_name,
								c.member user_member,
								c.birth_date user_birth_date,
								c.gender user_gender,
								a.visit_datetime,
								a.pickup_datetime,
								IFNULL(a.notes, '') notes,
								a.created_user_id,
								d.user_uuid created_user_uuid,
								d.name created_user_name,
								a.created_date,
								a.updated_date,
								IFNULL(a.visit_duration, 0) visit_duration,
								a.visit_external_id,
								e.trip_type_id,
								e.trip_type_key,
								e.trip_type,
								IFNULL(a.pickup_id, '') visit_pickup_id,
								IFNULL(a.pickup_address_id, 0) pickup_address_id,
								IFNULL(a.address_id, 0) address_id,
								IFNULL(a.provider_id, 0) provider_id,
								IFNULL(f.provider_uuid, '') provider_uuid, 
								IFNULL(f.provider_internal_id, '') provider_internal_id, 
								IFNULL(f.provider_muk_id, '') provider_muk_id, 
								IFNULL(f.provider_name, '') provider_name 
					FROM 
										tab_visit a
				INNER JOIN tab_visit_status b
						ON      a.visit_status_id = b.visit_status_id
				INNER JOIN tab_user c
						ON      c.user_id = a.user_id
				INNER JOIN tab_user d
						ON      d.user_id = a.created_user_id
				INNER JOIN tab_trip_type e
						ON      e.trip_type_id = a.trip_type_id
                INNER JOIN tab_provider f
                        ON      f.provider_id  = a.provider_id `
}

func (c *visitRepo) GetAll(user entity.User) ([]entity.Visit, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	query = c.getQuery() + query + " WHERE 1 = 1 and c.active = 1 " + where

	return c.parseSet(c.conn.Query(query))
}

func (c *visitRepo) GetByUUID(visitUUID string, user entity.User) (entity.Visit, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return entity.Visit{}, err
	}

	query = c.getQuery() + query + " WHERE a.visit_uuid = ? " + where
	return c.parseEntity(c.conn.QueryRow(query, visitUUID))
}

func (c *visitRepo) GetByID(visitID int64, user entity.User) (entity.Visit, error) {
	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return entity.Visit{}, err
	}

	query = c.getQuery() + query + " WHERE a.visit_id = ? " + where
	return c.parseEntity(c.conn.QueryRow(query, visitID))
}

// parseSet parses a result set result to an entity array
func (c *visitRepo) parseSet(rows *sql.Rows, err error) ([]entity.Visit, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.Visit, 0)
	for rows.Next() {
		entity, err := c.parseEntity(rows)
		if err != nil {
			return nil, errors.Wrap(err)
		}
		result = append(result, entity)
	}
	return result, nil
}

// parseEntity parses a result to an entity
func (c *visitRepo) parseEntity(row scanner) (retVal entity.Visit, err error) {
	var birthDate mysql.NullTime
	var gender sql.NullString

	err = row.Scan(
		&retVal.ID, &retVal.UUID, &retVal.Status.ID, &retVal.Status.Key, &retVal.Status.Value, &retVal.User.ID, &retVal.User.UUID, &retVal.User.Name, &retVal.User.Member, &birthDate, &gender, &retVal.VisitDatetime, &retVal.PickupDatetime, &retVal.Notes, &retVal.CreatedUser.ID, &retVal.CreatedUser.UUID, &retVal.CreatedUser.Name, &retVal.Created, &retVal.Updated, &retVal.VisitDuration, &retVal.ExternalID, &retVal.TripType.ID, &retVal.TripType.Key, &retVal.TripType.Value, &retVal.Pickup.ID, &retVal.PickupAddressID, &retVal.DestinationAddressID, &retVal.Provider.ProviderID, &retVal.Provider.ProviderUUID, &retVal.Provider.InternalID, &retVal.Provider.MukID, &retVal.Provider.Name)

	if birthDate.Valid {
		retVal.User.BirthDate = birthDate.Time
	}

	if gender.Valid {
		retVal.User.Gender = gender.String
	} else {
		retVal.User.Gender = "U"
	}

	return retVal, errors.Wrap(err)
}

func (c *visitRepo) Create(visit entity.Visit) (entity.Visit, error) {
	const (
		statusQuery = `SELECT a.visit_status_id, a.visit_status_key, a.visit_status, b.user_id, b.name user_name, c.user_id create_user_id, c.name create_user_name, d.trip_type_id, d.trip_type_key, d.trip_type FROM tab_visit_status a, tab_user b, tab_user c, tab_trip_type d WHERE a.visit_status_key = ? AND b.user_uuid = ? AND c.user_uuid = ? AND d.trip_type_key = ?;`
		query       = `INSERT INTO tab_visit(visit_uuid, visit_status_id, user_id, visit_datetime, pickup_datetime, notes, created_user_id, visit_duration, visit_external_id, return_date, trip_type_id, pickup_id, pickup_address_id, address_id, provider_id)
									 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`
	)

	retVal := visit

	returnDate := mysql.NullTime{
		Valid: (retVal.ReturnDate != nil),
	}
	if retVal.ReturnDate != nil {
		returnDate.Time = *retVal.ReturnDate
	}

	UUID, _ := uuid.NewV4()
	retVal.UUID = UUID.String()

	if retVal.Status.Key == "" {
		retVal.Status.Key = "accepted"
	}

	row := c.conn.QueryRow(statusQuery, retVal.Status.Key, retVal.User.UUID, retVal.CreatedUser.UUID, retVal.TripType.Key)
	if err := row.Scan(&retVal.Status.ID, &retVal.Status.Key, &retVal.Status.Value, &retVal.User.ID, &retVal.User.Name, &retVal.CreatedUser.ID, &retVal.CreatedUser.Name, &retVal.TripType.ID, &retVal.TripType.Key, &retVal.TripType.Value); err != nil {
		fmt.Println("Error to get base data: ", err.Error())
		return retVal, err
	}

	results, err := c.conn.Exec(query, retVal.UUID, retVal.Status.ID, retVal.User.ID, retVal.VisitDatetime, retVal.PickupDatetime, retVal.Notes, retVal.CreatedUser.ID, toNullInt64(retVal.VisitDuration), retVal.ExternalID, returnDate, retVal.TripType.ID, retVal.Pickup.ID, retVal.PickupAddressID, retVal.DestinationAddressID, retVal.Provider.ProviderID)
	if err != nil {
		return retVal, err
	}

	retVal.ID, err = results.LastInsertId()
	if err != nil {
		return retVal, err
	}

	fmt.Println("Visit ID: ", retVal.ID)
	return retVal, nil
}