repo.go

package main

import (
	"log"

	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
)

// Repository ...
type Repository interface {
	DB() *sqlx.DB

	SetTx(tx *sqlx.Tx)

	StoreWorkspace(x *Workspace)
	GetWorkspace(workspaceID string) (*Workspace, error)
	GetWorkspacesByAccount(id string) ([]*Workspace, error)
	GetWorkspaceByName(name string) (*Workspace, error)
	DeleteWorkspace(workspaceID string)

	GetAccount(id string) (*Account, error)
	GetAccountByEmail(email string) (*Account, error)
	GetAccountByConfirmationKey(key string) (*Account, error)
	GetAccountByPasswordKey(key string) (*Account, error)
	FindAccountsByWorkspace(id string) ([]*Account, error)
	StoreAccount(x *Account)
	DeleteAccount(accountID string)

	StoreMember(x *Member)
	GetMember(workspaceID string, id string) (*Member, error)
	GetMemberByAccountAndWorkspace(accountID string, workspaceID string) (*Member, error)
	GetMembersByAccount(id string) ([]*Member, error)
	GetMemberByEmail(workspaceID string, email string) (*Member, error)
	FindMembersByWorkspace(id string) ([]*Member, error)
	DeleteMember(wsid string, id string)

	StoreSubscription(z *Subscription)
	FindSubscriptionsByWorkspace(id string) ([]*Subscription, error)
	FindSubscriptionsByAccount(accID string) ([]*Subscription, error)
	FindSubscriptionByExternalID(externalSubID string) (*Subscription, error)

	StoreInvite(x *Invite)
	DeleteInvite(wsid string, id string)
	GetInviteByCode(code string) (*Invite, error)
	GetInviteByEmail(wsid string, email string) (*Invite, error)
	GetInvite(workspaceID string, id string) (*Invite, error)
	FindInvitesByWorkspace(wsid string) ([]*Invite, error)

	GetProjectByExternalLink(link string) (*Project, error)
	GetProject(workspaceID string, projectID string) (*Project, error)
	FindProjectsByWorkspace(workspaceID string) ([]*Project, error)
	StoreProject(x *Project)
	DeleteProject(workspaceID string, projectID string)

	GetMilestone(workspaceID string, milestoneID string) (*Milestone, error)
	FindMilestonesByProject(workspaceID string, projectID string) ([]*Milestone, error)
	StoreMilestone(x *Milestone)
	DeleteMilestone(workspaceID string, milestoneID string)

	GetWorkflow(workspaceID string, workflowID string) (*Workflow, error)
	FindWorkflowsByProject(workspaceID string, projectID string) ([]*Workflow, error)
	StoreWorkflow(x *Workflow)
	DeleteWorkflow(workspaceID string, workflowID string)

	GetSubWorkflow(workspaceID string, subWorkflowID string) (*SubWorkflow, error)
	FindSubWorkflowsByProject(workspaceID string, projectID string) ([]*SubWorkflow, error)
	FindSubWorkflowsByWorkflow(workspaceID string, workflowID string) ([]*SubWorkflow, error)
	StoreSubWorkflow(x *SubWorkflow)
	DeleteSubWorkflow(workspaceID string, workflowID string)

	GetFeature(workspaceID string, featureID string) (*Feature, error)
	FindFeaturesByProject(workspaceID string, projectID string) ([]*Feature, error)
	FindFeaturesByMilestoneAndSubWorkflow(workspaceID string, mid string, swid string) ([]*Feature, error)
	StoreFeature(x *Feature)
	DeleteFeature(workspaceID string, workflowID string)

	GetFeatureComment(workspaceID string, ID string) (*FeatureComment, error)
	FindFeatureCommentsByProject(workspaceID string, projectID string) ([]*FeatureComment, error)
	StoreFeatureComment(x *FeatureComment)
	DeleteFeatureComment(workspaceID string, commentID string)

	GetFeatureCommentOwner(workspaceID string, ID string) (*FeatureCommentOwner, error)
	FindFeatureCommentOwnersByProject(workspaceID string, projectID string) ([]*FeatureCommentOwner, error)
	StoreFeatureCommentOwner(x *FeatureCommentOwner)
	GetFeatureCommentOwnerByFeatureComment(workspaceID string, ID string) (*FeatureCommentOwner, error)

	GetPersona(workspaceID string, ID string) (*Persona, error)
	FindPersonasByProject(workspaceID string, projectID string) ([]*Persona, error)
	StorePersona(x *Persona)
	DeletePersona(workspaceID string, id string)

	GetWorkflowPersona(workspaceID string, ID string) (*WorkflowPersona, error)
	FindWorkflowPersonasByProject(workspaceID string, projectID string) ([]*WorkflowPersona, error)
	StoreWorkflowPersona(x *WorkflowPersona)
	DeleteWorkflowPersona(workspaceID string, id string)
}

type repo struct {
	db *sqlx.DB
	tx *sqlx.Tx
}

type txnFunc func(*sqlx.Tx) error

func txnDo(db *sqlx.DB, f txnFunc) (err error) {
	var tx *sqlx.Tx
	tx, err = db.Beginx()
	if err != nil {
		return
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		} else if err != nil {
			_ = tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()

	return f(tx)
}

// NewFeatmapRepository ...
func NewFeatmapRepository(db *sqlx.DB) Repository {
	return &repo{db: db}
}

func (a *repo) DB() *sqlx.DB {
	return a.db
}

func (a *repo) SetTx(tx *sqlx.Tx) {
	a.tx = tx
}

// Workspaces

func (a *repo) GetWorkspace(id string) (*Workspace, error) {
	workspace := &Workspace{}
	if err := a.tx.Get(workspace, "SELECT * FROM workspaces WHERE id = $1", id); err != nil {
		return nil, errors.Wrap(err, "workspace not found")
	}
	return workspace, nil
}

func (a *repo) GetWorkspaceByName(name string) (*Workspace, error) {
	workspace := &Workspace{}
	if err := a.tx.Get(workspace, "SELECT * FROM workspaces WHERE name = $1", name); err != nil {
		return nil, errors.Wrap(err, "workspace not found")
	}
	return workspace, nil
}

func (a *repo) GetWorkspacesByAccount(id string) ([]*Workspace, error) {
	var workspaces []*Workspace
	if err := a.tx.Select(&workspaces, "SELECT * FROM workspaces w where id in (select m.workspace_id from members m where m.account_id = $1) order by w.name", id); err != nil {
		return nil, err
	}
	return workspaces, nil
}

const saveWorkspaceQuery = "INSERT INTO workspaces (id, name, created_at, allow_external_sharing, external_customer_id, eu_vat, external_billing_email) VALUES ($1,$2,$3,$4,$5,$6,$7) ON CONFLICT (id) DO UPDATE SET allow_external_sharing = $4, external_customer_id = $5, eu_vat = $6, external_billing_email = $7"

func (a *repo) StoreWorkspace(x *Workspace) {
	a.tx.MustExec(saveWorkspaceQuery, x.ID, x.Name, x.CreatedAt, x.AllowExternalSharing, x.ExternalCustomerID, x.EUVAT, x.ExternalBillingEmail)
}

func (a *repo) DeleteWorkspace(workspaceID string) {
	a.tx.MustExec("DELETE FROM workspaces WHERE id=$1", workspaceID)
}

// Accounts
func (a *repo) GetAccount(id string) (*Account, error) {

	acc := &Account{}
	if err := a.tx.Get(acc, "SELECT * FROM accounts WHERE id = $1", id); err != nil {
		return nil, errors.Wrap(err, "account not found")
	}

	return acc, nil
}

func (a *repo) GetAccountByEmail(email string) (*Account, error) {
	acc := &Account{}
	if err := a.tx.Get(acc, "SELECT * FROM accounts WHERE email = $1", email); err != nil {
		return nil, errors.Wrap(err, "account not found")
	}
	return acc, nil
}

func (a *repo) GetAccountByConfirmationKey(key string) (*Account, error) {
	acc := &Account{}
	if err := a.tx.Get(acc, "SELECT * FROM accounts WHERE email_confirmation_key = $1", key); err != nil {
		return nil, errors.Wrap(err, "account not found")
	}
	return acc, nil
}

func (a *repo) GetAccountByPasswordKey(key string) (*Account, error) {
	acc := &Account{}
	if err := a.tx.Get(acc, "SELECT * FROM accounts WHERE password_reset_key = $1", key); err != nil {
		return nil, errors.Wrap(err, "account not found")
	}
	return acc, nil
}

const saveAccountQuery = "INSERT INTO accounts (id, email, password, created_at, email_confirmation_sent_to, email_confirmed, email_confirmation_key,email_confirmation_pending, password_reset_key, name) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) ON CONFLICT (id) DO UPDATE SET email = $2, password = $3, email_confirmation_sent_to = $5, email_confirmed = $6,email_confirmation_key = $7,email_confirmation_pending = $8, password_reset_key=$9, name=$10, latest_activity=$11"

func (a *repo) StoreAccount(x *Account) {
	a.tx.MustExec(saveAccountQuery, x.ID, x.Email, x.Password, x.CreatedAt, x.EmailConfirmationSentTo, x.EmailConfirmed, x.EmailConfirmationKey, x.EmailConfirmationPending, x.PasswordResetKey, x.Name, x.LatestActivity)

}

func (a *repo) FindAccountsByWorkspace(id string) ([]*Account, error) {
	accounts := []*Account{}
	if err := a.tx.Select(&accounts, "SELECT * FROM accounts a where a.id in (select m.account_id from members m where m.workspace_id = $1)", id); err != nil {
		return nil, err
	}
	return accounts, nil
}

func (a *repo) DeleteAccount(accountID string) {
	a.tx.MustExec("DELETE FROM accounts WHERE id=$1", accountID)
}

// Members

const saveMemberQuery = "INSERT INTO members (id, workspace_id, account_id, level, created_at) VALUES ($1,$2,$3,$4,$5) ON CONFLICT (workspace_id, id) DO UPDATE SET level = $4"

func (a *repo) StoreMember(x *Member) {
	a.tx.MustExec(saveMemberQuery, x.ID, x.WorkspaceID, x.AccountID, x.Level, x.CreatedAt)
}

func (a *repo) DeleteMember(wsid string, id string) {
	a.tx.MustExec("DELETE FROM members WHERE workspace_id = $1 AND id = $2", wsid, id)

}

func (a *repo) GetMemberByAccountAndWorkspace(accountID string, workspaceID string) (*Member, error) {
	member := &Member{}
	if err := a.tx.Get(member, "SELECT * FROM members WHERE account_id = $1 AND workspace_id = $2", accountID, workspaceID); err != nil {
		return nil, errors.Wrap(err, "member not found")
	}
	return member, nil
}

func (a *repo) GetMember(workspaceID string, id string) (*Member, error) {
	member := &Member{}
	if err := a.tx.Get(member, "SELECT * FROM members WHERE workspace_id = $1 AND id = $2", workspaceID, id); err != nil {
		return nil, errors.Wrap(err, "member not found")
	}
	return member, nil
}

func (a *repo) GetMembersByAccount(id string) ([]*Member, error) {
	var members []*Member
	if err := a.tx.Select(&members, "SELECT * FROM members WHERE account_id = $1", id); err != nil {
		return nil, err
	}
	return members, nil
}

func (a *repo) GetMemberByEmail(workspaceID string, email string) (*Member, error) {
	member := &Member{}
	if err := a.tx.Get(member, "SELECT * FROM members m WHERE m.workspace_id = $1 AND m.account_id IN (SELECT id FROM accounts a WHERE a.email = $2) ", workspaceID, email); err != nil {
		log.Println(err)
		return nil, err
	}
	return member, nil
}

func (a *repo) FindMembersByWorkspace(id string) ([]*Member, error) {
	x := []*Member{}
	if err := a.tx.Select(&x, "SELECT m.workspace_id, m.id, m.account_id, m.level, m.created_at, a.name, a.email FROM members m INNER JOIN accounts a ON m.account_id = a.id WHERE m.workspace_id = $1 ORDER by m.created_at DESC ", id); err != nil {
		//if err := a.tx.Select(&x, "SELECT * FROM members m WHERE m.workspace_id = $1 ", id); err != nil {
		return nil, err
	}
	return x, nil
}

// Subscriptions

const storeSubQuery = "INSERT INTO subscriptions (id, workspace_id,level, number_of_editors, from_date,expiration_date, created_by_name, created_at, last_modified, last_modified_by_name, status, external_customer_id, external_plan_id, external_subscription_id,external_subscription_item_id) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15) ON CONFLICT (workspace_id, id) DO UPDATE SET level = $3, number_of_editors = $4, from_date = $5,expiration_date = $6, created_by_name = $7, created_at = $8, last_modified = $9, last_modified_by_name = $10, status = $11, external_customer_id = $12, external_plan_id = $13,  external_subscription_id = $14, external_subscription_item_id = $15"

func (a *repo) StoreSubscription(x *Subscription) {
	a.tx.MustExec(storeSubQuery, x.ID, x.WorkspaceID, x.Level, x.NumberOfEditors, x.FromDate, x.ExpirationDate, x.CreatedByName, x.CreatedAt, x.LastModified, x.LastModifiedByName, x.Status, x.ExternalCustomerID, x.ExternalPlanID, x.ExternalSubscriptionID, x.ExternalSubscriptionItemID)
}

func (a *repo) FindSubscriptionsByWorkspace(id string) ([]*Subscription, error) {
	x := []*Subscription{}
	err := a.tx.Select(&x, "SELECT * FROM subscriptions WHERE workspace_id = $1 order by created_at desc", id)
	if err != nil {
		log.Println(err)
		return nil, errors.Wrap(err, "no subscriptions found")
	}
	return x, nil
}

func (a *repo) FindSubscriptionsByAccount(accID string) ([]*Subscription, error) {
	x := []*Subscription{}

	err := a.tx.Select(&x, "SELECT DISTINCT ON (s.workspace_id) * FROM subscriptions s WHERE s.workspace_id IN  (select m.workspace_id from members m where m.account_id = $1) order by s.workspace_id, s.from_date desc", accID)
	if err != nil {
		log.Println(err)
		return nil, errors.Wrap(err, "no subscriptions found")
	}

	return x, nil
}

func (a *repo) FindSubscriptionByExternalID(externalSubID string) (*Subscription, error) {
	x := &Subscription{}
	if err := a.tx.Get(x, "SELECT * FROM subscriptions  WHERE external_subscription_id = $1", externalSubID); err != nil {
		return nil, errors.Wrap(err, "no subscription found")
	}
	return x, nil
}

// INVITES

func (a *repo) StoreInvite(x *Invite) {
	a.tx.MustExec("INSERT INTO invites (workspace_id, id, email, level, code, created_by, created_by_name, created_at, created_by_email, workspace_name) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)", x.WorkspaceID, x.ID, x.Email, x.Level, x.Code, x.CreatedBy, x.CreatedByName, x.CreatedAt, x.CreatedByEmail, x.WorkspaceName)
}

func (a *repo) DeleteInvite(wsid string, id string) {
	a.tx.MustExec("DELETE FROM invites WHERE workspace_id = $1 AND id = $2", wsid, id)
}

func (a *repo) GetInviteByCode(code string) (*Invite, error) {
	x := &Invite{}
	if err := a.tx.Get(x, "SELECT * FROM invites WHERE code = $1", code); err != nil {
		log.Println(err)
		return nil, err
	}
	return x, nil
}

func (a *repo) GetInviteByEmail(workspaceID string, email string) (*Invite, error) {
	x := &Invite{}
	if err := a.tx.Get(x, "SELECT * FROM invites  WHERE workspace_id = $1 AND email = $2 ", workspaceID, email); err != nil {
		log.Println(err)
		return nil, err
	}
	return x, nil
}

func (a *repo) GetInvite(workspaceID string, id string) (*Invite, error) {
	x := &Invite{}
	if err := a.tx.Get(x, "SELECT * FROM invites WHERE workspace_id = $1 AND id = $2", workspaceID, id); err != nil {
		log.Println(err)
		return nil, err
	}
	return x, nil
}

func (a *repo) FindInvitesByWorkspace(wsid string) ([]*Invite, error) {
	x := []*Invite{}
	if err := a.tx.Select(&x, "SELECT * FROM invites WHERE workspace_id = $1", wsid); err != nil {
		log.Println(err)
		return nil, err
	}
	return x, nil
}

// Projects

func (a *repo) GetProject(workspaceID string, projectID string) (*Project, error) {
	x := &Project{}
	if err := a.tx.Get(x, "SELECT * FROM projects WHERE workspace_id = $1 AND id = $2", workspaceID, projectID); err != nil {
		return nil, errors.Wrap(err, "project not found")
	}
	return x, nil
}

func (a *repo) GetProjectByExternalLink(link string) (*Project, error) {
	x := &Project{}
	if err := a.tx.Get(x, "SELECT * FROM projects WHERE external_link = $1", link); err != nil {
		return nil, errors.Wrap(err, "project not found")
	}
	return x, nil
}

func (a *repo) FindProjectsByWorkspace(workspaceID string) ([]*Project, error) {
	x := []*Project{}
	err := a.tx.Select(&x, "SELECT * FROM projects WHERE workspace_id = $1", workspaceID)
	if err != nil {
		return nil, errors.Wrap(err, "no projects found")
	}
	return x, nil
}

func (a *repo) StoreProject(x *Project) {
	a.tx.MustExec("INSERT INTO projects (workspace_id, id, title, created_at,created_by_name, description, last_modified, last_modified_by_name, external_link) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT (workspace_id, id) DO UPDATE SET title = $3, description = $6, last_modified = $7, last_modified_by_name = $8, external_link = $9", x.WorkspaceID, x.ID, x.Title, x.CreatedAt, x.CreatedByName, x.Description, x.LastModified, x.LastModifiedByName, x.ExternalLink)
}

func (a *repo) DeleteProject(workspaceID string, projectID string) {
	a.tx.MustExec("DELETE FROM projects WHERE workspace_id=$1 AND id=$2", workspaceID, projectID)
}

// Milestones

func (a *repo) GetMilestone(workspaceID string, milestoneID string) (*Milestone, error) {
	x := &Milestone{}
	if err := a.tx.Get(x, "SELECT * FROM milestones WHERE workspace_id = $1 AND id = $2", workspaceID, milestoneID); err != nil {
		return nil, errors.Wrap(err, "milestone not found")
	}
	return x, nil
}

func (a *repo) FindMilestonesByProject(workspaceID string, projectID string) ([]*Milestone, error) {
	x := []*Milestone{}
	err := a.tx.Select(&x, "SELECT * FROM milestones WHERE workspace_id = $1 AND project_id = $2 ORDER by rank", workspaceID, projectID)
	if err != nil {
		return nil, err
	}
	return x, nil
}

func (a *repo) StoreMilestone(x *Milestone) {
	a.tx.MustExec("INSERT INTO milestones (workspace_id, project_id, id, rank, title, created_at,created_by_name, description, last_modified, last_modified_by_name,status, color, annotations) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10, $11, $12,$13) ON CONFLICT (workspace_id, id) DO UPDATE SET rank = $4, title = $5, description = $8, last_modified = $9, last_modified_by_name = $10, status = $11,color = $12, annotations = $13", x.WorkspaceID, x.ProjectID, x.ID, x.Rank, x.Title, x.CreatedAt, x.CreatedByName, x.Description, x.LastModified, x.LastModifiedByName, x.Status, x.Color, x.Annotations)
}

func (a *repo) DeleteMilestone(workspaceID string, milestoneID string) {
	a.tx.MustExec("DELETE FROM milestones WHERE workspace_id=$1 AND id=$2", workspaceID, milestoneID)
}

// Workflows
func (a *repo) GetWorkflow(workspaceID string, workflowID string) (*Workflow, error) {
	x := &Workflow{}
	if err := a.tx.Get(x, "SELECT * FROM workflows WHERE workspace_id = $1 AND id = $2", workspaceID, workflowID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindWorkflowsByProject(workspaceID string, projectID string) ([]*Workflow, error) {
	x := []*Workflow{}
	err := a.tx.Select(&x, "SELECT * FROM workflows WHERE workspace_id = $1 and project_id = $2 order by rank", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "none found")
	}
	return x, nil
}

func (a *repo) StoreWorkflow(x *Workflow) {
	a.tx.MustExec("INSERT INTO workflows (workspace_id, project_id, id, rank, title, created_at, created_by_name, description,last_modified,last_modified_by_name,color,status,annotations) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) ON CONFLICT (workspace_id, id) DO UPDATE SET rank = $4, title = $5, description = $8, last_modified = $9, last_modified_by_name = $10, color = $11, status = $12, annotations = $13", x.WorkspaceID, x.ProjectID, x.ID, x.Rank, x.Title, x.CreatedAt, x.CreatedByName, x.Description, x.LastModified, x.LastModifiedByName, x.Color, x.Status, x.Annotations)
}

func (a *repo) DeleteWorkflow(workspaceID string, workflowID string) {
	a.tx.MustExec("DELETE FROM workflows WHERE workspace_id=$1 AND id=$2", workspaceID, workflowID)
}

// SubWorkflows
func (a *repo) GetSubWorkflow(workspaceID string, subWorkflowID string) (*SubWorkflow, error) {
	x := &SubWorkflow{}
	if err := a.tx.Get(x, "SELECT * FROM subworkflows WHERE workspace_id = $1 AND id = $2", workspaceID, subWorkflowID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindSubWorkflowsByProject(workspaceID string, projectID string) ([]*SubWorkflow, error) {
	x := []*SubWorkflow{}
	err := a.tx.Select(&x, "SELECT * FROM subworkflows s WHERE s.workspace_id = $1 AND s.workflow_id in (select w.id from workflows w where w.workspace_id = $1 and w.project_id = $2)", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) FindSubWorkflowsByWorkflow(workspaceID string, workflowID string) ([]*SubWorkflow, error) {
	x := []*SubWorkflow{}
	err := a.tx.Select(&x, "SELECT * FROM subworkflows s WHERE s.workspace_id = $1 AND s.workflow_id = $2 ORDER BY s.rank", workspaceID, workflowID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil

}

func (a *repo) StoreSubWorkflow(x *SubWorkflow) {
	a.tx.MustExec("INSERT INTO subworkflows (workspace_id, workflow_id, id, rank, title, created_at,created_by_name, description, last_modified,last_modified_by_name,color,status, annotations) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) ON CONFLICT (workspace_id, id) DO UPDATE SET workflow_id = $2,rank = $4, title = $5, description = $8, last_modified = $9, last_modified_by_name = $10, color = $11, status = $12, annotations = $13", x.WorkspaceID, x.WorkflowID, x.ID, x.Rank, x.Title, x.CreatedAt, x.CreatedByName, x.Description, x.LastModified, x.LastModifiedByName, x.Color, x.Status, x.Annotations)
}

func (a *repo) DeleteSubWorkflow(workspaceID string, subWorkflowID string) {
	a.tx.MustExec("DELETE FROM subworkflows WHERE workspace_id=$1 AND id=$2", workspaceID, subWorkflowID)
}

// Features

func (a *repo) GetFeature(workspaceID string, featureID string) (*Feature, error) {
	x := &Feature{}
	if err := a.tx.Get(x, "SELECT * FROM features WHERE workspace_id = $1 AND id = $2", workspaceID, featureID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindFeaturesByProject(workspaceID string, projectID string) ([]*Feature, error) {
	x := []*Feature{}
	err := a.tx.Select(&x, "SELECT * FROM features f WHERE f.workspace_id = $1 AND f.milestone_id IN (select m.id from milestones m where m.workspace_id = $1 and m.project_id = $2) ", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) FindFeaturesByMilestoneAndSubWorkflow(workspaceID string, mid string, swid string) ([]*Feature, error) {
	x := []*Feature{}
	err := a.tx.Select(&x, "SELECT * FROM features f WHERE f.workspace_id = $1 AND f.milestone_id = $2 AND f.subworkflow_id = $3 ORDER BY f.rank", workspaceID, mid, swid)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) StoreFeature(x *Feature) {
	a.tx.MustExec("INSERT INTO features (workspace_id, subworkflow_id, milestone_id, id, rank, title, created_at, description, created_by_name, last_modified,last_modified_by_name, status, color, annotations, estimate) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15) ON CONFLICT (workspace_id, id) DO UPDATE SET subworkflow_id = $2, milestone_id = $3,rank = $5, title = $6,  description = $8, last_modified = $10, last_modified_by_name = $11, status = $12, color = $13,  annotations = $14, estimate = $15",
		x.WorkspaceID, x.SubWorkflowID, x.MilestoneID, x.ID, x.Rank, x.Title, x.CreatedAt, x.Description, x.CreatedByName, x.LastModified, x.LastModifiedByName, x.Status, x.Color, x.Annotations, x.Estimate)
}

func (a *repo) DeleteFeature(workspaceID string, featureID string) {
	a.tx.MustExec("DELETE FROM features WHERE workspace_id=$1 AND id=$2", workspaceID, featureID)
}

// Feature comments

func (a *repo) GetFeatureComment(workspaceID string, ID string) (*FeatureComment, error) {
	x := &FeatureComment{}
	if err := a.tx.Get(x, "SELECT * FROM feature_comments WHERE workspace_id = $1 AND id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindFeatureCommentsByProject(workspaceID string, projectID string) ([]*FeatureComment, error) {
	x := []*FeatureComment{}
	err := a.tx.Select(&x, "SELECT * FROM feature_comments f WHERE f.workspace_id = $1 AND f.project_id = $2", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) FindFeatureCommentsByFeature(workspaceID string, ID string) (*FeatureComment, error) {
	x := &FeatureComment{}
	if err := a.tx.Get(x, "SELECT * FROM feature_comments WHERE workspace_id = $1 AND feature_id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) StoreFeatureComment(x *FeatureComment) {
	a.tx.MustExec("INSERT INTO feature_comments (workspace_id, id, project_id, feature_id, post, created_at, created_by_name, last_modified) VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT (workspace_id, id) DO UPDATE SET post = $5, created_by_name = $7, last_modified = $8",
		x.WorkspaceID, x.ID, x.ProjectID, x.FeatureID, x.Post, x.CreatedAt, x.CreatedByName, x.LastModified)
}

func (a *repo) DeleteFeatureComment(workspaceID string, commentID string) {
	a.tx.MustExec("DELETE FROM feature_comments WHERE workspace_id=$1 AND id=$2", workspaceID, commentID)
}

// Feature comment owners

func (a *repo) GetFeatureCommentOwner(workspaceID string, ID string) (*FeatureCommentOwner, error) {
	x := &FeatureCommentOwner{}
	if err := a.tx.Get(x, "SELECT * FROM feature_comment_owners WHERE workspace_id = $1 AND id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) GetFeatureCommentOwnerByFeatureComment(workspaceID string, ID string) (*FeatureCommentOwner, error) {
	x := &FeatureCommentOwner{}
	if err := a.tx.Get(x, "SELECT * FROM feature_comment_owners WHERE workspace_id = $1 AND feature_comment_id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindFeatureCommentOwnersByProject(workspaceID string, projectID string) ([]*FeatureCommentOwner, error) {
	x := []*FeatureCommentOwner{}
	err := a.tx.Select(&x, "SELECT * FROM feature_comment_owners f WHERE f.workspace_id = $1 AND f.project_id = $2", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) StoreFeatureCommentOwner(x *FeatureCommentOwner) {
	a.tx.MustExec("INSERT INTO feature_comment_owners (workspace_id, id, feature_comment_id, member_id, project_id) VALUES ($1,$2,$3,$4,$5)",
		x.WorkspaceID, x.ID, x.FeatureCommentID, x.MemberID, x.ProjectID)
}

// Personas

func (a *repo) GetPersona(workspaceID string, ID string) (*Persona, error) {
	x := &Persona{}
	if err := a.tx.Get(x, "SELECT * FROM personas WHERE workspace_id = $1 AND id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil

}

func (a *repo) FindPersonasByProject(workspaceID string, projectID string) ([]*Persona, error) {
	x := []*Persona{}
	err := a.tx.Select(&x, "SELECT * FROM personas f WHERE f.workspace_id = $1 AND f.project_id = $2", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}

func (a *repo) StorePersona(x *Persona) {
	a.tx.MustExec("INSERT INTO personas (workspace_id, project_id, id, name, role, avatar, description, created_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT (workspace_id, id) DO UPDATE SET name = $4, role = $5, avatar = $6, description = $7 ",
		x.WorkspaceID, x.ProjectID, x.ID, x.Name, x.Role, x.Avatar, x.Description, x.CreatedAt)
}

func (a *repo) DeletePersona(workspaceID string, id string) {
	a.tx.MustExec("DELETE FROM personas WHERE workspace_id=$1 AND id=$2", workspaceID, id)
}

// Workflow Personas

func (a *repo) GetWorkflowPersona(workspaceID string, ID string) (*WorkflowPersona, error) {
	x := &WorkflowPersona{}
	if err := a.tx.Get(x, "SELECT * FROM workflow_personas WHERE workspace_id = $1 AND id = $2", workspaceID, ID); err != nil {
		return nil, errors.Wrap(err, "not found")
	}
	return x, nil
}

func (a *repo) FindWorkflowPersonasByProject(workspaceID string, projectID string) ([]*WorkflowPersona, error) {
	x := []*WorkflowPersona{}
	err := a.tx.Select(&x, "SELECT * FROM workflow_personas f WHERE f.workspace_id = $1 AND f.project_id = $2", workspaceID, projectID)
	if err != nil {
		return nil, errors.Wrap(err, "no found")
	}
	return x, nil
}
func (a *repo) StoreWorkflowPersona(x *WorkflowPersona) {
	a.tx.MustExec("INSERT INTO workflow_personas (workspace_id, project_id, workflow_id, id, persona_id) VALUES ($1,$2,$3,$4,$5)",
		x.WorkspaceID, x.ProjectID, x.WorkflowID, x.ID, x.PersonaID)
}

func (a *repo) DeleteWorkflowPersona(workspaceID string, id string) {
	a.tx.MustExec("DELETE FROM workflow_personas WHERE workspace_id=$1 AND id=$2", workspaceID, id)
}