package repositories

import (
	"errors"
	"fmt"
	"nearle/models"
	"strconv"
	"strings"

	"github.com/jinzhu/copier"
	"gorm.io/gorm"
)

type TenantRepository interface {
	SearchTenant(status, searchstr string) ([]models.Tenantinfo, error)
	GetAllTenants(pageno, pagesize, aid int, status, tenanttype, keyword string) ([]models.Tenantinfo, error)
	GetTenantLocations(tid int) ([]models.Tenantlocations, error)
	GetTenantSlot() (models.Tenantslot, error)
	CreateTenantCustomer(customer models.Tenantcustomer) (*models.Tenantcustomer, error)
	GetCustomerTenants(customerID int, categoryID int, tenantFlag int) ([]models.TenantInfo, error)
	GetTenantPricing(tid, aid int) (*models.Tenantpricing, error)
	UpdateLocation(input models.Tenantlocations) error
	CreateLocation(data models.Tenantlocations) error
	GetStaffs(tid int) ([]models.StaffInfo, error)
	CreateStaff(user models.User) error
	UpdateStaff(user models.User) error
	CreateTenantLocation(data models.Tenantlocations) error
	UpdateTenantLocation(data models.Tenantlocations) error
	CheckTenantByNo(cno string) int
	CreateTenantUser(data models.Tenants) (bool, error)
	GetUserByNo(cno string) models.UserInfo
	GetTenantByID(tid int, locationid int) (models.Tenantinfo, error)
	GetTenantByKeyword(keyword string) ([]models.TenantSearch, error)
}

type tenantRepository struct {
	db *gorm.DB
}

func NewTenantRepository(db *gorm.DB) TenantRepository {
	return &tenantRepository{db: db}
}

func (r *tenantRepository) SearchTenant(status, keyword string) ([]models.Tenantinfo, error) {
	var data []models.Tenantinfo
	var query string

	searchStr := strings.ToLower(keyword)

	if strings.ToLower(status) != "pending" {
		query = `
			SELECT a.*, b.subcategoryname, c.firstname, c.lastname, 
				   CONCAT(c.firstname, ' ', c.lastname) AS accountname 
			FROM tenants a
			INNER JOIN app_subcategory b ON a.subcategoryid = b.subcategoryid
			LEFT JOIN app_users c ON c.userid = a.partneruserid
			WHERE a.approved = 1 
			  AND LOWER(a.status) = ? 
			  AND LOWER(a.tenantname) LIKE ?
		`
		r.db.Raw(query, strings.ToLower(status), searchStr+"%").Scan(&data)
	} else {
		query = `
			SELECT a.*, b.subcategoryname, c.firstname, c.lastname, 
				   CONCAT(c.firstname, ' ', c.lastname) AS accountname 
			FROM tenants a
			INNER JOIN app_subcategory b ON a.subcategoryid = b.subcategoryid
			LEFT JOIN app_users c ON c.userid = a.partneruserid
			WHERE a.approved = 0 
			  AND LOWER(a.tenantname) LIKE ?
		`
		r.db.Raw(query, searchStr+"%").Scan(&data)
	}

	return data, nil
}

func (r *tenantRepository) GetAllTenants(pageno, pagesize, aid int, status, tenanttype, keyword string) ([]models.Tenantinfo, error) {
	offset := (pageno - 1) * pagesize

	var data []models.Tenantinfo

	base := `SELECT * FROM tenants a WHERE 1 = 1`

	var (
		conds  []string
		params []interface{}
	)

	switch strings.ToLower(status) {
	case "active":
		conds = append(conds, "a.approved = 1 AND a.status = 'Active'")
	case "inactive":
		conds = append(conds, "a.approved = 1 AND a.status = 'InActive'")
	case "pending":
		conds = append(conds, "a.approved = 0")
	}

	if aid != 0 {
		conds = append(conds, "a.applocationid = ?")
		params = append(params, aid)
	}

	if tenanttype != "" {
		conds = append(conds, "a.tenanttype = ?")
		params = append(params, tenanttype)
	}

	if keyword != "" {

		kw := "%" + strings.ToLower(keyword) + "%"

		conds = append(conds,
			"(LOWER(a.tenantname) LIKE ? OR LOWER(a.primarycontact) LIKE ?)")

		params = append(params, kw, kw)
	}

	if len(conds) > 0 {
		base += " AND " + strings.Join(conds, " AND ")
	}

	base += " ORDER BY a.tenantid DESC LIMIT ? OFFSET ?"
	params = append(params, pagesize, offset)

	err := r.db.Raw(base, params...).Scan(&data).Error
	if err != nil {
		return nil, err
	}

	return data, nil
}

func (r *tenantRepository) GetTenantLocations(tid int) ([]models.Tenantlocations, error) {
	var data []models.Tenantlocations

	q1 := `SELECT a.*, b.roleid FROM tenantlocations a
			LEFT JOIN app_users b ON a.locationid = b.locationid 
    		AND a.tenantid = b.tenantid
			WHERE a.tenantid = ?`

	if err := r.db.Raw(q1, tid).Find(&data).Error; err != nil {
		return nil, err
	}

	print(q1)

	return data, nil
}

func (r *tenantRepository) GetTenantSlot() (models.Tenantslot, error) {
	var data models.Tenantslot

	err := r.db.Raw(`SELECT * FROM tenantslot`).Find(&data).Error

	if err != nil {
		return models.Tenantslot{}, err
	}

	return data, nil
}

func (r *tenantRepository) CreateTenantCustomer(customer models.Tenantcustomer) (*models.Tenantcustomer, error) {
	var existing models.Tenantcustomer

	// 🔍 Step 1: Check if a record already exists with same customerid and locationid
	err := r.db.
		Where("customerid = ? AND locationid = ?", customer.CustomerID, customer.LocationID).
		First(&existing).Error

	// If record found, prevent insertion
	if err == nil {
		return nil, fmt.Errorf("customer already exists for this location")
	}

	// If error other than record not found, return it
	if !errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, err
	}

	// ✅ Step 2: Insert new record if no duplicate found
	if err := r.db.Create(&customer).Error; err != nil {
		return nil, err
	}

	return &customer, nil
}

func (r *tenantRepository) GetCustomerTenants(customerID int, categoryID int, tenantFlag int) ([]models.TenantInfo, error) {
	var tenants []models.TenantInfo

	query := `
        SELECT a.customerid, a.locationid, b.tenantid,b.tenantname,b.address,b.licenseno,
               b.primaryemail,b.primarycontact,b.applocationid,b.suburb,b.city,
               b.latitude,b.longitude,b.postcode,b.tenantimage,b.subcategoryid,
               b.categoryid,b.registrationno,d.userfcmtoken,c.locationname,
               COALESCE(o.orderscount, 0) AS orderscount
        FROM tenantcustomers a
        LEFT JOIN tenants b ON a.tenantid = b.tenantid
        INNER JOIN tenantlocations c ON a.locationid = c.locationid
        LEFT JOIN (
            SELECT tenantid, customerid, COUNT(*) AS orderscount
            FROM orders
            GROUP BY tenantid, customerid
        ) o ON b.tenantid = o.tenantid AND o.customerid = a.customerid
        LEFT JOIN (
            SELECT locationid, 
                   MAX(userfcmtoken) AS userfcmtoken
            FROM app_users
            GROUP BY locationid
        ) d ON d.locationid = a.locationid
        WHERE a.customerid = ? 
          AND b.tenantid IS NOT NULL
    `

	args := []interface{}{customerID}

	if categoryID != 0 {
		query += " AND b.categoryid = ?"
		args = append(args, categoryID)
	}

	if tenantFlag == 1 {
		query += " AND COALESCE(o.orderscount,0) > 0"
	}

	if err := r.db.Raw(query, args...).Scan(&tenants).Error; err != nil {
		return nil, err
	}

	if tenants == nil {
		return []models.TenantInfo{}, nil
	}

	// Attach top 5 subcategories
	if len(tenants) > 0 {
		var subcategories []models.ProductSubcategory
		if err := r.db.Table("productsubcategories").
			Order("subcatid ASC").
			Limit(5).
			Find(&subcategories).Error; err != nil {
			return nil, err
		}

		for i := range tenants {
			tenants[i].ProductSubcategory = subcategories
		}
	}

	print(tenants)

	return tenants, nil
}

func (r *tenantRepository) GetTenantPricing(tid, aid int) (*models.Tenantpricing, error) {
	var data models.Tenantpricing
	var q1 string

	if tid != 0 {
		q1 = `SELECT * 
		      FROM tenantpricing 
		      WHERE pricingdate = (
		          SELECT MAX(pricingdate) 
		          FROM tenantpricing 
		          WHERE tenantid=` + strconv.Itoa(tid) + `
		      ) 
		      AND tenantid=? 
		      ORDER BY tenantpricingid DESC`
	}

	if err := r.db.Raw(q1, tid).Find(&data).Error; err != nil {
		return nil, err
	}

	return &data, nil
}

func (r *tenantRepository) UpdateLocation(input models.Tenantlocations) error {
	tx := r.db.Begin()

	if err := tx.Where("locationid=?", input.Locationid).Updates(&input).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Commit().Error; err != nil {
		return err
	}

	return nil
}

func (r *tenantRepository) CreateLocation(data models.Tenantlocations) error {
	var cust models.Customers
	var tcust models.Tenantcustomers
	var custloc models.Customerlocations

	tx := r.db.Begin()

	if err := tx.Create(&data).Error; err != nil {
		tx.Rollback()
		return err
	}

	cust.Firstname = data.Locationname
	cust.Email = data.Email
	cust.Contactno = data.Contactno
	cust.Address = data.Address
	cust.Suburb = data.Suburb
	cust.City = data.City
	cust.State = data.State
	cust.Postcode = data.Postcode
	cust.Applocationid = data.Applocationid
	cust.Latitude = data.Latitude
	cust.Longitude = data.Longitude
	cust.Primaryaddress = 0

	if err := tx.Table("customers").Create(&cust).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := copier.Copy(&custloc, &cust); err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Table("customerlocations").Create(&custloc).Error; err != nil {
		tx.Rollback()
		return err
	}

	tcust.Customerid = cust.Customerid
	tcust.Tenantid = data.Tenantid
	tcust.Locationid = data.Locationid
	tcust.Moduleid = data.Moduleid

	if err := tx.Table("tenantcustomers").Create(&tcust).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Commit().Error; err != nil {
		return err
	}

	return nil
}

func (r *tenantRepository) GetStaffs(tid int) ([]models.StaffInfo, error) {
	var data []models.StaffInfo

	q1 := `SELECT a.userid,a.firstname,a.lastname,
	           CONCAT(a.firstname,' ',a.lastname) AS fullname,
	           a.email,a.contactno,a.address,a.suburb,a.city,
	           a.state,a.postcode,a.userfcmtoken,a.pin,a.applocationid,
	           a.roleid,a.partnerid,a.tenantid,a.locationid,
	           b.locationname
	        FROM app_users a
	        INNER JOIN tenantlocations b ON a.locationid = b.locationid
	        WHERE a.tenantid = ?`

	if err := r.db.Raw(q1, tid).Scan(&data).Error; err != nil {
		return nil, err
	}

	return data, nil
}

func (r *tenantRepository) CreateStaff(user models.User) error {
	if err := r.db.Table("app_users").Create(&user).Error; err != nil {
		return err
	}
	return nil
}

func (r *tenantRepository) UpdateStaff(user models.User) error {
	if err := r.db.Table("app_users").Where("userid = ?", user.Userid).Updates(&user).Error; err != nil {
		return err
	}
	return nil
}

func (r *tenantRepository) CreateTenantLocation(data models.Tenantlocations) error {
	var user models.Tenantuser
	tx := r.db.Begin()

	// Set status BEFORE insert
	data.Status = "InActive"

	// Step 1: Insert into tenantlocations
	if err := tx.Create(&data).Error; err != nil {
		tx.Rollback()
		return err
	}

	// Step 2: Insert into app_users
	user.Authname = data.Email
	user.Firstname = data.Locationname
	user.Email = data.Email
	user.Contactno = data.Contactno
	user.Address = data.Address
	user.Suburb = data.Suburb
	user.City = data.City
	user.State = data.State
	user.Postcode = data.Postcode
	user.Partnerid = data.Partnerid
	user.Tenantid = data.Tenantid
	user.Locationid = data.Locationid
	user.Applocationid = data.Applocationid
	user.Configid = 1
	user.Status = "InActive"
	user.Roleid = 0
	user.Authmode = 0
	user.Password = ""
	user.Dialcode = "+91"

	if err := tx.Table("app_users").Create(&user).Error; err != nil {
		tx.Rollback()
		return err
	}

	// Commit
	if err := tx.Commit().Error; err != nil {
		return err
	}

	return nil
}

func (r *tenantRepository) UpdateTenantLocation(input models.Tenantlocations) error {
	tx := r.db.Begin()

	// ✅ Step 1: Prepare map for tenantlocations update
	locationUpdate := make(map[string]interface{})

	if input.Locationname != "" {
		locationUpdate["locationname"] = input.Locationname
	}
	if input.Email != "" {
		locationUpdate["email"] = input.Email
	}
	if input.Contactno != "" {
		locationUpdate["contactno"] = input.Contactno
	}
	if input.Address != "" {
		locationUpdate["address"] = input.Address
	}
	if input.Suburb != "" {
		locationUpdate["suburb"] = input.Suburb
	}
	if input.City != "" {
		locationUpdate["city"] = input.City
	}
	if input.State != "" {
		locationUpdate["state"] = input.State
	}
	if input.Postcode != "" {
		locationUpdate["postcode"] = input.Postcode
	}
	if input.Partnerid != 0 {
		locationUpdate["partnerid"] = input.Partnerid
	}
	if input.Tenantid != 0 {
		locationUpdate["tenantid"] = input.Tenantid
	}
	if input.Applocationid != 0 {
		locationUpdate["applocationid"] = input.Applocationid
	}
	if input.Status != "" {
		locationUpdate["status"] = input.Status
	}

	// ✅ Step 2: Update tenantlocations (only provided fields)
	if len(locationUpdate) > 0 {
		if err := tx.Table("tenantlocations").
			Where("locationid = ?", input.Locationid).
			Updates(locationUpdate).Error; err != nil {
			tx.Rollback()
			return err
		}
	}

	// ✅ Step 3: Prepare map for app_users update (only matching fields)
	userUpdate := make(map[string]interface{})

	if input.Locationname != "" {
		userUpdate["firstname"] = input.Locationname
	}
	if input.Email != "" {
		userUpdate["email"] = input.Email
	}
	if input.Contactno != "" {
		userUpdate["contactno"] = input.Contactno
	}
	if input.Address != "" {
		userUpdate["address"] = input.Address
	}
	if input.Suburb != "" {
		userUpdate["suburb"] = input.Suburb
	}
	if input.City != "" {
		userUpdate["city"] = input.City
	}
	if input.State != "" {
		userUpdate["state"] = input.State
	}
	if input.Postcode != "" {
		userUpdate["postcode"] = input.Postcode
	}
	if input.Tenantid != 0 {
		userUpdate["tenantid"] = input.Tenantid
	}
	if input.Partnerid != 0 {
		userUpdate["partnerid"] = input.Partnerid
	}
	if input.Applocationid != 0 {
		userUpdate["applocationid"] = input.Applocationid
	}
	if input.Status != "" {
		userUpdate["status"] = input.Status
	}
	// ✅ Step 4: Update app_users (only provided fields)
	if len(userUpdate) > 0 {
		if err := tx.Table("app_users").
			Where("locationid = ?", input.Locationid).
			Updates(userUpdate).Error; err != nil {
			tx.Rollback()
			return err
		}
	}

	// ✅ Commit transaction
	if err := tx.Commit().Error; err != nil {
		return err
	}

	return nil
}

// ✅ Check if tenant exists
func (r *tenantRepository) CheckTenantByNo(cno string) int {
	var id int
	q1 := "SELECT tenantid FROM tenants WHERE primarycontact = '" + cno + `'`
	r.db.Raw(q1).Find(&id)
	return id
}

// ✅ Create tenant + user + customer records
func (r *tenantRepository) CreateTenantUser(data models.Tenants) (bool, error) {
	var seq models.Ordersequences
	var user models.User
	var cust models.Customers
	var custloc models.Customerlocations
	var tcust models.Tenantcustomers

	tx := r.db.Begin()

	// Step 1: Insert into tenants
	if err := tx.Create(&data).Error; err != nil {
		tx.Rollback()
		return false, errors.New("error in tenant creation")
	}

	// Step 2: Create order sequence
	seq.Tenantid = data.Tenantid
	if err := tx.Table("ordersequences").Create(&seq).Error; err != nil {
		tx.Rollback()
		return false, errors.New("error in sequence")
	}

	// Step 3: Create app_user
	if err := copier.Copy(&user, &data); err != nil {
		tx.Rollback()
		return false, err
	}
	user.Userfcmtoken = data.Tenanttoken
	user.Contactno = data.Primarycontact
	user.Email = data.Primaryemail
	user.Authname = data.Primaryemail
	user.Deviceid = data.Deviceid
	user.Tenantid = data.Tenantid
	user.Locationid = data.Tenantlocations.Locationid
	user.Roleid = 1

	if err := tx.Table("app_users").Create(&user).Error; err != nil {
		tx.Rollback()
		return false, errors.New("error in user creation")
	}

	// Step 4: Create / Update customers
	cust.Configid = data.Configid
	cust.Firstname = data.Tenantname
	cust.Email = data.Primaryemail
	cust.Contactno = data.Primarycontact
	cust.Deviceid = data.Deviceid
	cust.Devicetype = data.Devicetype
	cust.Customertoken = data.Tenanttoken
	cust.Profileimage = data.Tenantimage
	cust.Address = data.Address
	cust.Suburb = data.Suburb
	cust.City = data.City
	cust.State = data.State
	cust.Postcode = data.Postcode
	cust.Applocationid = data.Applocationid
	cust.Latitude = data.Latitude
	cust.Longitude = data.Longitude
	cust.Primaryaddress = 1

	cid := r.CheckCustomer(data.Primarycontact)

	if cid == 0 {
		if err := tx.Table("customers").Create(&cust).Error; err != nil {
			tx.Rollback()
			return false, errors.New("error in customer creation")
		}

		if err := copier.Copy(&custloc, &cust); err != nil {
			tx.Rollback()
			return false, err
		}

		if err := tx.Table("customerlocations").Create(&custloc).Error; err != nil {
			tx.Rollback()
			return false, errors.New("error in customer location")
		}
	} else {
		if err := tx.Table("customers").Where("customerid=?", cid).Updates(&cust).Error; err != nil {
			tx.Rollback()
			return false, errors.New("error updating customer")
		}

		if err := copier.Copy(&custloc, &cust); err != nil {
			tx.Rollback()
			return false, err
		}

		if err := tx.Table("customerlocations").Where("customerid=?", cid).Updates(&custloc).Error; err != nil {
			tx.Rollback()
			return false, errors.New("error updating customer location")
		}
	}

	// Step 5: Create tenant-customer link
	tcust.Customerid = cust.Customerid
	tcust.Tenantid = data.Tenantid
	tcust.Locationid = data.Tenantlocations.Locationid

	if err := tx.Table("tenantcustomers").Create(&tcust).Error; err != nil {
		tx.Rollback()
		return false, errors.New("error in tenant customer")
	}

	// ✅ Commit transaction
	if err := tx.Commit().Error; err != nil {
		return false, errors.New("error in tenant creation")
	}

	return true, nil
}

// ✅ Check if customer exists
func (r *tenantRepository) CheckCustomer(cno string) int {
	var id int
	q := "SELECT customerid FROM customers WHERE contactno = '" + cno + `'`
	r.db.Raw(q).Find(&id)
	return id
}

// ✅ Get user by contact number
func (r *tenantRepository) GetUserByNo(cno string) models.UserInfo {
	var user models.UserInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,
	a.firstname,a.lastname,CONCAT(a.firstname,' ',a.lastname) AS fullname,
	a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,a.locationid,
	b.partnerid,b.moduleid,b.categoryid,b.subcategoryid,
	b.applocationid,b.tenantname,b.address AS tenantaddress,b.state AS tenantstate,b.city AS tenantcity,
	b.postcode AS tenantpostcode,b.latitude AS tenantlat,b.longitude AS tenantlong
	FROM app_users a 
	LEFT JOIN tenants b ON a.tenantid = b.tenantid
	WHERE a.contactno = '` + cno + `'`

	r.db.Raw(q1).Find(&user)
	return user
}

func (r *tenantRepository) GetTenantByID(tid int, locationid int) (models.Tenantinfo, error) {
	var data models.Tenantinfo

	q1 := `
        SELECT a.*,b.categoryname,c.locationname AS applocation, d.allocationid AS allocationmode,e.typename AS allocationtype,e.mapid AS allocationid,f.locationid,
            f.locationname, f.contactno as locationcontact
        FROM tenants a
        INNER JOIN app_category b ON a.categoryid = b.categoryid
        INNER JOIN app_location c ON a.applocationid = c.applocationid
        LEFT JOIN partnerinfo d ON a.partnerid = d.partnerid
        LEFT JOIN app_types e ON d.allocationid = e.apptypeid
        LEFT JOIN tenantlocations f ON a.tenantid = f.tenantid
        WHERE a.tenantid = ?
    `

	var args []interface{}
	args = append(args, tid)

	if locationid != 0 {
		q1 += " AND f.locationid = ?"
		args = append(args, locationid)
	}

	if err := r.db.Raw(q1, args...).Find(&data).Error; err != nil {
		return data, err
	}

	return data, nil
}

func (r *tenantRepository) GetTenantByKeyword(keyword string) ([]models.TenantSearch, error) {
	var data []models.TenantSearch

	kw := "%" + strings.ToLower(keyword) + "%"

	query := `
		SELECT a.tenantname, b.productname, c.subcatname 
		FROM tenants a
		LEFT JOIN products b ON a.tenantid = b.tenantid
		LEFT JOIN productsubcategories c ON b.subcategoryid = c.subcatid
		WHERE c.categoryid = 2
		AND (LOWER(a.tenantname) LIKE ? OR LOWER(b.productname) LIKE ? OR LOWER(c.subcatname) LIKE ?)
	`

	if err := r.db.Raw(query, kw, kw, kw).Scan(&data).Error; err != nil {
		return nil, err
	}

	return data, nil
}