package datamysql

import (
	"database/sql"
	"fmt"
	"strconv"
	"time"

	"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"
)

// rideRepo maps methods to database
type providerRepo struct {
	conn executor
}

func newProviderRepo(conn executor) *providerRepo {
	return &providerRepo{
		conn: conn,
	}
}

func (c *providerRepo) 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_organization_provider c
                        ON          a.provider_id   =   c.provider_id `
					where = fmt.Sprintf(`	AND (c.organization_uuid = '%s' OR c.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 *providerRepo) getSelectQueryBaseKey() string {
	return `SELECT  
									a.provider_key_id,
									a.provider_id,
									a.internal_id,
									a.internal_id_suffix,
									a.location_seq_num,
									a.plan_code,
									a.product_id,
									a.treatment_category_code,
									a.active,
									a.enabled,
									a.create_at,
									a.update_at,
									a.created_user
						FROM          tab_provider_key a `
}

func (c *providerRepo) getSelectQueryBase() string {
	return `SELECT DISTINCT
								a.provider_id,
								a.provider_uuid,
								a.provider_internal_id,
								a.provider_internal_id_suffix,
								a.provider_muk_id,
								a.organization_name,
								a.gender,
								a.accept_new_patients,
								a.provider_name,
								a.first_name,
								a.last_name,
								a.middle_name,
								a.provider_title,
								a.street_name1,
								a.street_name2,
								a.city_name,
								a.state,
								a.zipcode,
								a.country,
								a.latitude,
								a.longitude,
								a.phone_number,
								a.create_at,
								a.update_at,
								a.active,
								a.enabled,
								a.created_user,
								(3959 * acos (
									cos ( radians(?) )
									* cos( radians( a.latitude ) )
									* cos( radians( a.longitude ) - radians(?) )
									+ sin ( radians(?) )
									* sin( radians( a.latitude ) )
								)) AS distance_in_miles
						FROM 
												tab_provider     a
				INNER JOIN     tab_provider_key b
						ON          a.provider_id   =   b.provider_id `
}

func (c *providerRepo) GetAll(user entity.User) ([]entity.Provider, error) {
	lat := 41.886406
	long := -87.624225

	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	query = c.getSelectQueryBase() + query + " WHERE 1 = 1 " + where

	providers, err := c.parseSet(c.conn.Query(query, lat, long, lat))
	if err != nil {
		return nil, err
	}

	return c.getKeys("", "", providers)
}

func (c *providerRepo) GetByMukID(mukID string, user entity.User) (entity.Provider, error) {
	lat := 41.886406
	long := -87.624225

	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return entity.Provider{}, err
	}

	query = c.getSelectQueryBase() + query + " WHERE a.provider_muk_id = ? " + where

	return c.parseEntity(c.conn.QueryRow(query, lat, long, lat, mukID))
}

func (c *providerRepo) Get(query string, lat float64, long float64, distance int64, planCode string, productID string, mukID string, internalID string, sort string, user entity.User) ([]entity.Provider, error) {
	filter := " WHERE 1 = 1 "
	params := make([]interface{}, 0)

	if lat == 0 && long == 0 {
		lat = 41.886406
		long = -87.624225
	}
	params = append(params, lat, long, lat)

	if query != "" && (mukID == "" && internalID == "") {
		filter += " AND (a.provider_name LIKE ? OR a.street_name1 LIKE ? OR a.street_name2 LIKE ? OR a.zipcode LIKE ?) "
		// MATCH(a.provider_internal_id, a.provider_internal_id_suffix, a.provider_muk_id, a.provider_name, a.street_name1, a.street_name2, a.city_name, a.state, a.zipcode, a.country, a.phone_number) AGAINST (?)

		query = "%" + query + "%"
		params = append(params, query, query, query, query)
	}

	if planCode != "" {
		filter += " AND b.plan_code = ? "
		params = append(params, planCode)
	}

	if productID != "" {
		filter += " AND b.product_id = ? "
		params = append(params, productID)
	}

	if mukID != "" {
		filter += " AND a.provider_muk_id = ? "
		params = append(params, mukID)
	}

	if internalID != "" {
		filter += " AND a.provider_internal_id = ? "
		params = append(params, internalID)
	}

	final := " HAVING distance_in_miles < ? "
	params = append(params, distance)

	if sort == "name" {
		final += " ORDER BY a.provider_name ASC "
	} else if sort == "distance" {
		final += " ORDER BY distance_in_miles ASC "
	}

	query, where, err := c.getProfileQuery(user)
	if err != nil {
		return nil, err
	}

	query = c.getSelectQueryBase() + query + filter + where + final

	providers, err := c.parseSet(c.conn.Query(query, params...))
	if err != nil {
		return nil, err
	}
	if len(providers) > 0 {
		return c.getKeys(planCode, productID, providers)
	} else {
		return providers, nil
	}
}

func (c *providerRepo) getKeys(planCode string, productID string, providers []entity.Provider) ([]entity.Provider, error) {
	var params []interface{}
	var queryParams = " WHERE a.provider_id IN ("
	for _, p := range providers {
		queryParams += "?,"
		params = append(params, p.ProviderID)
	}
	queryParams = queryParams[0 : len(queryParams)-1]
	queryParams += ") "

	if planCode != "" {
		queryParams += " AND a.plan_code = ? "
		params = append(params, planCode)
	}

	if productID != "" {
		queryParams += " AND a.product_id = ? "
		params = append(params, productID)
	}

	keys, err := c.parseKeySet(c.conn.Query(c.getSelectQueryBaseKey()+queryParams, params...))
	if err != nil {
		return nil, err
	}
	sortedResults := make(map[string][]entity.ProviderKey)
	for _, k := range keys {
		sortedKey := fmt.Sprintf("provider%v", k.Provider.ProviderID)
		sortedResults[sortedKey] = append(sortedResults[sortedKey], k)
	}

	for i := range providers {
		sortedKey := fmt.Sprintf("provider%v", providers[i].ProviderID)
		providers[i].Keys = sortedResults[sortedKey]
	}

	return providers, nil
}

// parseSet parses a result set result to an entity array
func (c *providerRepo) parseKeySet(rows *sql.Rows, err error) ([]entity.ProviderKey, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.ProviderKey, 0)
	for rows.Next() {
		entity, err := c.parseKeyEntity(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 *providerRepo) parseKeyEntity(row scanner) (retVal entity.ProviderKey, err error) {
	err = row.Scan(
		&retVal.ProviderKeyID, &retVal.Provider.ProviderID, &retVal.InternalID, &retVal.InternalSuffixID, &retVal.LocationSeqNumber, &retVal.PlanCode, &retVal.ProductID, &retVal.TreatmentCategoryCode, &retVal.Active, &retVal.Enabled, &retVal.CreateDate, &retVal.UpdateDate, &retVal.CreatedUser.ID)

	return retVal, errors.Wrap(err)
}

// parseSet parses a result set result to an entity array
func (c *providerRepo) parseSet(rows *sql.Rows, err error) ([]entity.Provider, error) {
	if err != nil {
		return nil, errors.Wrap(err)
	}
	result := make([]entity.Provider, 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 *providerRepo) parseEntity(row scanner) (retVal entity.Provider, err error) {
	err = row.Scan(
		&retVal.ProviderID, &retVal.ProviderUUID, &retVal.InternalID, &retVal.InternalSuffixID, &retVal.MukID, &retVal.OrganizatioName, &retVal.Gender, &retVal.AcceptNewPatients, &retVal.Name, &retVal.FirstName, &retVal.LastName, &retVal.MiddleName, &retVal.Title, &retVal.Address.StreetAddress1, &retVal.Address.StreetAddress2, &retVal.Address.CityName, &retVal.Address.State, &retVal.Address.ZipCode, &retVal.Address.Country, &retVal.Address.Latitude, &retVal.Address.Longitude, &retVal.Address.PhoneNumber, &retVal.CreateDate, &retVal.UpdateDate, &retVal.Active, &retVal.Enabled, &retVal.CreatedUser.ID, &retVal.Distance)

	return retVal, errors.Wrap(err)
}

func (c *providerRepo) Save(providers []entity.ProviderResponse, user entity.User) ([]entity.Provider, error) {
	var savedProviders = make([]entity.Provider, 0)
	for _, p := range providers {
		provider, err := c.saveProvider(p, user)
		if err != nil {
			if driverErr, ok := err.(*mysql.MySQLError); ok {
				if driverErr.Number != 1062 {
					return nil, err
				}
			} else {
				return nil, err
			}
		} else {
			savedProviders = append(savedProviders, provider)
		}
	}

	return savedProviders, nil
}

func (c *providerRepo) saveProvider(provider entity.ProviderResponse, user entity.User) (entity.Provider, error) {
	const (
		query = `INSERT INTO tab_provider(provider_uuid,provider_internal_id,provider_internal_id_suffix,provider_muk_id,organization_name,gender,accept_new_patients,provider_name,first_name,last_name,middle_name,provider_title,street_name1,street_name2,city_name,state,zipcode,country,latitude,longitude,phone_number,created_user)
							VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);`
	)

	UUID, _ := uuid.NewV4()
	lat, _ := strconv.ParseFloat(provider.Latitude, 64)
	long, _ := strconv.ParseFloat(provider.Longitude, 64)

	p := entity.Provider{
		ProviderUUID:      UUID.String(),
		InternalID:        provider.FivePartKeyGroups[0].ProviderNum,
		InternalSuffixID:  provider.FivePartKeyGroups[0].ProviderNumSuffix,
		MukID:             provider.MukID,
		OrganizatioName:   provider.OrgName,
		Gender:            provider.Gender,
		AcceptNewPatients: provider.AcceptNewPatients,
		Name:              provider.ProviderName,
		FirstName:         provider.FirstName,
		MiddleName:        provider.MiddleName,
		LastName:          provider.LastName,
		Title:             provider.ProviderTitle,
		Address: entity.ProviderAddress{
			StreetAddress1: provider.StreetName1,
			StreetAddress2: provider.StreetName2,
			CityName:       provider.CityName,
			State:          provider.State,
			ZipCode:        provider.ZipCode,
			Country:        provider.Country,
			Latitude:       lat,
			Longitude:      long,
			PhoneNumber:    provider.PhoneNumber,
		},
		CreateDate: time.Now(),
		UpdateDate: time.Now(),
		Active:     true,
		Enabled:    true,
	}

	result, err := c.conn.Exec(query, p.ProviderUUID, p.InternalID, p.InternalSuffixID, p.MukID, p.OrganizatioName, p.Gender, p.AcceptNewPatients, p.Name, p.FirstName, p.LastName, p.MiddleName, p.Title, p.Address.StreetAddress1, p.Address.StreetAddress2, p.Address.CityName, p.Address.State, p.Address.ZipCode, p.Address.Country, p.Address.Latitude, p.Address.Longitude, p.Address.PhoneNumber, 1)
	if err != nil {
		if driverErr, ok := err.(*mysql.MySQLError); ok {
			if driverErr.Number != 1062 {
				return entity.Provider{}, err
			} else {
				tempProvider, err := c.GetByMukID(p.MukID, user)
				if err != nil {
					return entity.Provider{}, err
				}
				p.ProviderID = tempProvider.ProviderID
			}
		}
	} else {
		p.ProviderID, err = result.LastInsertId()
		if err != nil {
			return entity.Provider{}, err
		}
	}

	var providerKeys []entity.ProviderKey
	for _, fk := range provider.FivePartKeyGroups {
		key, err := c.saveKeys(p.ProviderID, fk)
		if err != nil {
			return p, err
		}
		providerKeys = append(providerKeys, key)
	}
	p.Keys = providerKeys

	return p, nil
}

func (c *providerRepo) saveKeys(ProviderID int64, fk entity.PartKeyGroup) (entity.ProviderKey, error) {
	const (
		query = `INSERT INTO tab_provider_key (provider_id, internal_id, internal_id_suffix, location_seq_num, plan_code, product_id, treatment_category_code, created_user)
						 VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE provider_id = ?, internal_id = ?, internal_id_suffix = ?, location_seq_num = ?, plan_code = ?, product_id = ?, treatment_category_code = ?, created_user = ?;`
	)

	key := entity.ProviderKey{
		InternalID:            fk.ProviderNum,
		InternalSuffixID:      fk.ProviderNumSuffix,
		TreatmentCategoryCode: fk.TreatmentCategoryCode,
		ProductID:             fk.ProductID,
		PlanCode:              fk.PlanCode,
		LocationSeqNumber:     fk.LocationSeqNum,
		Provider: entity.Provider{
			ProviderID: ProviderID,
		},
	}

	result, err := c.conn.Exec(query, key.Provider.ProviderID, key.InternalID, key.InternalSuffixID, key.LocationSeqNumber, key.PlanCode, key.ProductID, key.TreatmentCategoryCode, 1, key.Provider.ProviderID, key.InternalID, key.InternalSuffixID, key.LocationSeqNumber, key.PlanCode, key.ProductID, key.TreatmentCategoryCode, 1)
	if err != nil {
		return key, err
	}

	key.ProviderKeyID, err = result.LastInsertId()
	if err != nil {
		return key, err
	}

	return key, nil
}