package db

import "database/sql"
import "errors"
import "strings"
import "time"

import "codit/internal/models"

func (s *Store) ListSubjectPermissions(permission string, subjectType string, subjectID string) ([]models.SubjectPermission, error) {
	var rows *sql.Rows
	var err error
	var items []models.SubjectPermission
	var item models.SubjectPermission
	permission = strings.TrimSpace(permission)
	subjectType = strings.ToLower(strings.TrimSpace(subjectType))
	subjectID = strings.TrimSpace(subjectID)
	rows, err = s.Query(`SELECT
			sp.permission,
			sp.subject_type,
			sp.subject_public_id,
			CASE
				WHEN sp.subject_type = 'user' THEN
					CASE
						WHEN u.public_id IS NULL THEN sp.subject_public_id
						WHEN u.display_name <> '' THEN u.display_name || ' (' || u.username || ')'
						ELSE u.username
					END
				WHEN sp.subject_type = 'group' THEN COALESCE(g.name, sp.subject_public_id)
				WHEN sp.subject_type = 'principal' THEN COALESCE(p.name, sp.subject_public_id)
				ELSE sp.subject_public_id
			END AS subject_name,
			CASE
				WHEN sp.subject_type = 'user' THEN CASE WHEN u.public_id IS NOT NULL AND u.disabled = 0 THEN 1 ELSE 0 END
				WHEN sp.subject_type = 'group' THEN CASE WHEN g.public_id IS NOT NULL AND g.disabled = 0 THEN 1 ELSE 0 END
				WHEN sp.subject_type = 'principal' THEN CASE WHEN p.public_id IS NOT NULL AND p.disabled = 0 THEN 1 ELSE 0 END
				ELSE 0
			END AS subject_active,
			sp.created_at,
			sp.updated_at
		FROM subject_permissions sp
		LEFT JOIN users u
			ON sp.subject_type = 'user'
			AND u.public_id = sp.subject_public_id
		LEFT JOIN user_groups g
			ON sp.subject_type = 'group'
			AND g.public_id = sp.subject_public_id
		LEFT JOIN service_principals p
			ON sp.subject_type = 'principal'
			AND p.public_id = sp.subject_public_id
		WHERE (? = '' OR sp.permission = ?)
		  AND (? = '' OR sp.subject_type = ?)
		  AND (? = '' OR sp.subject_public_id = ?)
		ORDER BY sp.permission, sp.subject_type, subject_name, sp.subject_public_id`,
		permission,
		permission,
		subjectType,
		subjectType,
		subjectID,
		subjectID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&item.Permission,
			&item.SubjectType,
			&item.SubjectID,
			&item.SubjectName,
			&item.SubjectActive,
			&item.CreatedAt,
			&item.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSubjectPermission(permission string, subjectType string, subjectID string) (models.SubjectPermission, error) {
	var items []models.SubjectPermission
	var item models.SubjectPermission
	var err error
	items, err = s.ListSubjectPermissions(permission, subjectType, subjectID)
	if err != nil {
		return item, err
	}
	if len(items) == 0 {
		return item, sql.ErrNoRows
	}
	return items[0], nil
}

func (s *Store) UpsertSubjectPermission(item models.SubjectPermission) (models.SubjectPermission, error) {
	var now int64
	var err error
	var result sql.Result
	var rowsAffected int64
	now = time.Now().UTC().Unix()
	item.Permission = strings.TrimSpace(item.Permission)
	item.SubjectType = strings.ToLower(strings.TrimSpace(item.SubjectType))
	item.SubjectID = strings.TrimSpace(item.SubjectID)
	item.CreatedAt = now
	item.UpdatedAt = now
	switch item.SubjectType {
	case "user":
		result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
			SELECT ?, 'user', u.public_id, ?, ?
			FROM users u
			WHERE u.public_id = ?
			ON CONFLICT(permission, subject_type, subject_public_id)
			DO UPDATE SET updated_at = excluded.updated_at`,
			item.Permission,
			item.CreatedAt,
			item.UpdatedAt,
			item.SubjectID,
		)
	case "group":
		result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
			SELECT ?, 'group', g.public_id, ?, ?
			FROM user_groups g
			WHERE g.public_id = ?
			ON CONFLICT(permission, subject_type, subject_public_id)
			DO UPDATE SET updated_at = excluded.updated_at`,
			item.Permission,
			item.CreatedAt,
			item.UpdatedAt,
			item.SubjectID,
		)
	case "principal":
		result, err = s.Exec(`INSERT INTO subject_permissions (permission, subject_type, subject_public_id, created_at, updated_at)
			SELECT ?, 'principal', p.public_id, ?, ?
			FROM service_principals p
			WHERE p.public_id = ?
			ON CONFLICT(permission, subject_type, subject_public_id)
			DO UPDATE SET updated_at = excluded.updated_at`,
			item.Permission,
			item.CreatedAt,
			item.UpdatedAt,
			item.SubjectID,
		)
	default:
		return item, errors.New("invalid subject_type")
	}
	if err != nil {
		return item, err
	}
	rowsAffected, err = result.RowsAffected()
	if err != nil {
		return item, err
	}
	if rowsAffected <= 0 {
		return item, errors.New("subject not found")
	}
	return s.GetSubjectPermission(item.Permission, item.SubjectType, item.SubjectID)
}

func (s *Store) DeleteSubjectPermission(permission string, subjectType string, subjectID string) error {
	var err error
	_, err = s.Exec(`DELETE FROM subject_permissions
		WHERE permission = ?
		  AND subject_type = ?
		  AND subject_public_id = ?`,
		strings.TrimSpace(permission),
		strings.ToLower(strings.TrimSpace(subjectType)),
		strings.TrimSpace(subjectID),
	)
	return err
}

func (s *Store) ListUserPermissions(userID string) ([]string, error) {
	var rows *sql.Rows
	var err error
	var items []string
	var item string
	userID = strings.TrimSpace(userID)
	rows, err = s.Query(`SELECT DISTINCT sp.permission
		FROM subject_permissions sp
		WHERE (sp.subject_type = 'user' AND sp.subject_public_id = ?)
		   OR (
				sp.subject_type = 'group'
				AND sp.subject_public_id IN (
					SELECT g.public_id
					FROM user_group_members gm
					JOIN users u ON u.id = gm.user_id
					JOIN user_groups g ON g.id = gm.group_id
					WHERE u.public_id = ?
					  AND g.disabled = 0
				)
		   )
		ORDER BY sp.permission`,
		userID,
		userID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) UserHasPermission(userID string, permission string) (bool, error) {
	var row *sql.Row
	var value int
	var err error
	userID = strings.TrimSpace(userID)
	permission = strings.TrimSpace(permission)
	row = s.QueryRow(`SELECT 1
		FROM subject_permissions sp
		WHERE sp.permission = ?
		  AND (
				(sp.subject_type = 'user' AND sp.subject_public_id = ?)
				OR
				(
					sp.subject_type = 'group'
					AND sp.subject_public_id IN (
						SELECT g.public_id
						FROM user_group_members gm
						JOIN users u ON u.id = gm.user_id
						JOIN user_groups g ON g.id = gm.group_id
						WHERE u.public_id = ?
						  AND g.disabled = 0
					)
				)
		  )
		LIMIT 1`,
		permission,
		userID,
		userID,
	)
	err = row.Scan(&value)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

func (s *Store) PrincipalHasPermission(principalID string, permission string) (bool, error) {
	var row *sql.Row
	var value int
	var err error
	principalID = strings.TrimSpace(principalID)
	permission = strings.TrimSpace(permission)
	row = s.QueryRow(`SELECT 1
		FROM subject_permissions
		WHERE permission = ?
		  AND subject_type = 'principal'
		  AND subject_public_id = ?
		LIMIT 1`,
		permission,
		principalID,
	)
	err = row.Scan(&value)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}