src/internal/storage/postgresql/postgresql.go - master - bullerun/AvitoTestTaskBackend2024

src/internal/storage/postgresql/postgresql.go
871 строка29 KB
bullerun
Init
16 сен 2024, 16:45
16 сен 2024, 16:45c08c052
package postgresql

import (
	"Avito/internal/config"
	"Avito/internal/entity"
	"Avito/internal/storage"
	"context"
	"errors"
	"fmt"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
	"log"
	"strings"
	"time"
)

type Storage struct {
	db *pgxpool.Pool
}

const creatTenderServiceTypeEnum = `
CREATE TYPE tenders_service_type AS ENUM (
			'Construction',
			'Delivery',
			'Manufacture'
			);`
const creatTenderStatusTypeEnum = `
CREATE Type status_type as ENUM (
			'Created',
			'Published',
			'Closed');`
const creatTenderQuery = `
		CREATE TABLE IF NOT EXISTS tenders(
			id             UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
			name           VARCHAR(100) NOT NULL,
			description    VARCHAR(500),
			service_type    tenders_service_type,
			status         status_type,
			organization_id  UUID,
			version        integer          DEFAULT 1,
			creator_username VARCHAR(100),
			created_at     TIMESTAMP        DEFAULT CURRENT_TIMESTAMP);`
const creatTenderHistoryQuery = `
CREATE TABLE tender_history (
id UUID,
name VARCHAR(100),
description VARCHAR(500),
service_type tenders_service_type,
status status_type,
organization_id UUID,
version INT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, version));	`
const creatTenderTriggerQuery = `
CREATE OR REPLACE FUNCTION update_tender_version()
    RETURNS TRIGGER AS
$$
BEGIN
    -- Если ничего не меняется не увеличиваем ничего
    IF OLD.name = NEW.name AND
       OLD.description = NEW.description AND
       OLD.service_type = NEW.service_type AND
       OLD.status = NEW.status AND
       OLD.version = NEW.version THEN
        RETURN NEW;
    END IF;

    INSERT INTO tender_history (id, name, description, service_type, status, organization_id, version, updated_at)
    VALUES (OLD.id, OLD.name, OLD.description, OLD.service_type, OLD.status, OLD.organization_id, OLD.version, NOW());

    -- Увеличиваем версию в основной таблице
    NEW.version := OLD.version + 1;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_tender_version
    BEFORE UPDATE
    ON tenders
    FOR EACH ROW
EXECUTE FUNCTION update_tender_version();`
const authorTypeQuery = `
CREATE TYPE author_type AS ENUM (
    'User',
    'Organization'
);`
const createBidsStatusTypeEnum = `
CREATE Type creat_bids_status_type_enum as ENUM (
			'Created',
			'Published',
			'Canceled');`
const creatBidsQuery = `
CREATE TABLE IF NOT EXISTS bids (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),   
    name VARCHAR(100) NOT NULL,                       
    description VARCHAR(500) NOT NULL,                
    status creat_bids_status_type_enum,                     
    tender_id UUID NOT NULL,                          
    author_type author_type NOT NULL,                 
    author_id UUID NOT NULL,                          
    version INT NOT NULL default 1,        
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
const creatBidsHistoryQuery = `
CREATE TABLE IF NOT EXISTS bids_history
(
    id          UUID,
    name        VARCHAR(100),
    description VARCHAR(500),
    status      creat_bids_status_type_enum,
    tender_id   UUID,
    author_type author_type,
    author_id   UUID NOT NULL,
    version     INT,
    update_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`
const creatBidsTriggerQuery = `
CREATE OR REPLACE FUNCTION update_bid_version()
    RETURNS TRIGGER AS
$$
BEGIN
    -- Если ничего не меняется не увеличиваем ничего
    IF OLD.name = NEW.name AND
       OLD.description = NEW.description AND
       OLD.status = NEW.status AND
       OLD.version = NEW.version THEN
        RETURN NEW;
    END IF;

    -- Сохраняем старую версию в таблицу bids_history
    INSERT INTO bids_history (id, name, description, status, tender_id, author_type, author_id, version, update_at)
    VALUES (OLD.id, OLD.name, OLD.description, OLD.status, OLD.tender_id, OLD.author_type, OLD.author_id, OLD.version,
            NOW());

    -- Увеличиваем версию в основной таблице
    NEW.version := OLD.version + 1;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_bid_version
    BEFORE UPDATE
    ON bids
    FOR EACH ROW
EXECUTE FUNCTION update_bid_version();
`
const createReviewTable = `CREATE TABLE IF NOT EXISTS review
(
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bid_Feedback VARCHAR(1000),
    authorId     UUID NOT NULL REFERENCES employee (id),
    bidId        UUID NOT NULL REFERENCES bids (id),
--     creator_username VARCHAR(100),
    created_at   TIMESTAMP        DEFAULT CURRENT_TIMESTAMP
);`
const createDecisionType = `CREATE Type decision_type as ENUM (
    'Approved',
    'Rejected');`
const createDecisionTable = `CREATE TABLE IF NOT EXISTS decision
(
    id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    decision decision_type NOT NULL,
    authorId UUID          NOT NULL REFERENCES employee (id),
    bidId    UUID          NOT NULL REFERENCES bids (id)
);`

func InitPool(cfg config.Postgres) *Storage {
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()
	connURL := fmt.Sprintf("postgresql://%s:%s@%s:%s/%s", cfg.User, cfg.Password, cfg.Host, cfg.Port, cfg.Database)
	log.Printf("connecting to database: %s", connURL)

	pool, err := pgxpool.New(ctx, connURL)
	if err != nil {
		log.Fatalf("error connecting to Postgres: %s", err)
	}
	if err = pool.Ping(ctx); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "28P01" {
			log.Fatalf("authentication error: incorrect username or password: %s", err)
		}
		log.Fatalf("database doesn't respond: %s", err)
	}

	s := &Storage{pool}

	if err := s.createSQLTables(ctx); err != nil {
		log.Fatalf("error creating tables %s", err)
	}
	return s
}
func (s *Storage) Close() {
	s.db.Close()
}
func (s *Storage) Ping(ctx context.Context) error {
	return s.db.Ping(ctx)
}
func (s *Storage) createSQLTables(ctx context.Context) error {
	var query = []string{creatTenderServiceTypeEnum, creatTenderStatusTypeEnum, creatTenderQuery,
		creatTenderHistoryQuery, creatTenderTriggerQuery, authorTypeQuery, createBidsStatusTypeEnum, creatBidsQuery, creatBidsHistoryQuery,
		creatBidsTriggerQuery, createReviewTable, createDecisionType, createDecisionTable}
	for _, q := range query {
		_, err := s.db.Exec(ctx, q)
		if err != nil {
			var pgErr *pgconn.PgError
			if errors.As(err, &pgErr) && (pgErr.Code == "42710" || pgErr.Code == "42P07") {
				continue
			}
			return fmt.Errorf("error executing table creation query: %w", err)
		}
	}
	return nil
}
func (s *Storage) CreateBind(ctx context.Context, name, description string, tenderUUID uuid.UUID, authorType string, authorId uuid.UUID) (uuid.UUID, time.Time, error) {
	var id uuid.UUID
	var createdAt time.Time
	if authorType == "User" {
		if err := s.isEmployeeExistById(ctx, authorId); err != nil {
			return id, createdAt, err
		}
	} else if authorType == "Organization" {
		if err := s.isOrganisationExist(ctx, authorId); err != nil {
			return id, createdAt, err
		}
	}
	if err := s.isTenderExist(ctx, tenderUUID); err != nil {
		return id, createdAt, err
	}
	// проверка на то что мы создаем предложение на тендер который является Published иначе видеть его не должны
	isPublished, err := s.isTenderStatusPublic(ctx, tenderUUID)
	if err != nil {
		return id, createdAt, err
	}
	if !isPublished {
		return id, createdAt, storage.ErrTenderIsNotPublished
	}
	query := `INSERT INTO bids (name, description, status, tender_id, author_type, author_id) VALUES ($1, $2, $3, $4, $5, $6)RETURNING id, created_at;`
	err = s.db.QueryRow(ctx, query, name, description, "Created", tenderUUID, authorType, authorId).Scan(&id, &createdAt)
	if err != nil {
		return id, createdAt, fmt.Errorf("error inserting bid record: %w", err)
	}
	return id, createdAt, nil
}
func (s *Storage) CreateTender(ctx context.Context, name, description, serviceType string, organizationId uuid.UUID, creatorUsername string) (uuid.UUID, time.Time, error) {

	var id uuid.UUID
	var createdAt time.Time
	if err := s.isEmployeeExistByUsername(ctx, creatorUsername); err != nil {
		return id, createdAt, err
	}
	ok, err := s.checkUserOrganization(ctx, creatorUsername, organizationId)
	if err != nil {
		return id, createdAt, err
	}
	if !ok {
		return id, createdAt, storage.ErrNoSuchEmployeeInOrganization
	}
	query := `
		INSERT INTO tenders (name, description, service_type, status, organization_id, creator_username)
		 Values ($1, $2, $3, $4, $5, $6)
		RETURNING id, created_at;
	`

	if err := s.db.QueryRow(ctx, query, name, description, serviceType, "Created", organizationId, creatorUsername).Scan(&id, &createdAt); err != nil {
		return id, createdAt, fmt.Errorf("error inserting new tender: %w", err)
	}
	return id, createdAt, nil
}
func (s *Storage) checkUserOrganization(ctx context.Context, username string, organizationId uuid.UUID) (bool, error) {
	query := `SELECT EXISTS (SELECT 1 FROM employee e
    JOIN organization_responsible org ON e.id = org.user_id
    JOIN organization o ON org.organization_id = o.id
    WHERE e.username = $1
    AND o.id = $2
);`
	var exists bool
	err := s.db.QueryRow(ctx, query, username, organizationId).Scan(&exists)
	if err != nil {
		return false, fmt.Errorf("failed to check user organization: %w", err)
	}
	return exists, nil
}
func (s *Storage) GetTenders(ctx context.Context, limit, offset int, serviceType []string) ([]entity.TenderResponse, error) {
	var query string
	if serviceType == nil {
		query = "SELECT (id, name, description, service_type, status, version, created_at) FROM tenders Where status='Published' ORDER BY name LIMIT $1 OFFSET $2;"
	} else {
		query = fmt.Sprintf("SELECT (id, name, description, service_type, status, version, created_at) FROM tenders Where status='Published' and service_type in('%s') ORDER BY name LIMIT $1 OFFSET $2;", strings.Join(serviceType, "', '"))
	}
	rows, err := s.db.Query(ctx, query, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("error querying tendersSlice: %v", err)
	}
	var tendersSlice []entity.TenderResponse
	for rows.Next() {
		var tender entity.TenderResponse
		if err := rows.Scan(&tender); err != nil {
			return nil, fmt.Errorf("error scanning tendersSlice: %v", err)
		}
		tendersSlice = append(tendersSlice, tender)
	}
	return tendersSlice, nil
}
func (s *Storage) GetListBidsForTender(ctx context.Context, limit, offset int, tenderUUID uuid.UUID, username string) ([]entity.BidsResponse, error) {

	if err := s.checkTenderByUser(ctx, tenderUUID, username); err != nil {
		return nil, err
	}
	query := `SELECT (id, name, status,author_type,author_id, version, created_at) FROM bids WHERE tender_id = $1 Limit $2 OFFSET $3;`
	rows, err := s.db.Query(ctx, query, tenderUUID, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("error querying bid records: %v", err)
	}
	var bidsSlice []entity.BidsResponse
	for rows.Next() {
		var bid entity.BidsResponse
		if err := rows.Scan(&bid); err != nil {
			return nil, fmt.Errorf("error scanning bids: %v", err)
		}
		bidsSlice = append(bidsSlice, bid)
	}
	return bidsSlice, nil
}
func (s *Storage) GetMyTenders(ctx context.Context, limit, offset int, username string) ([]entity.TenderResponse, error) {

	err := s.isEmployeeExistByUsername(ctx, username)
	if err != nil {
		return nil, err
	}

	query := `SELECT (id, name, description, service_type, status, version, created_at) FROM tenders Where creator_username = $3 ORDER BY name LIMIT $1 OFFSET $2;`
	rows, err := s.db.Query(ctx, query, limit, offset, username)
	if err != nil {
		return nil, fmt.Errorf("error querying tenders: %v", err)
	}

	var tenders []entity.TenderResponse
	for rows.Next() {
		var tender entity.TenderResponse
		if err := rows.Scan(&tender); err != nil {
			return nil, fmt.Errorf("error scanning tenders: %v", err)
		}
		tenders = append(tenders, tender)
	}
	return tenders, nil
}
func (s *Storage) GetMyBids(ctx context.Context, limit, offset int, username string) ([]entity.BidsResponse, error) {
	if err := s.isEmployeeExistByUsername(ctx, username); err != nil {
		return nil, err
	}
	query := `SELECT  (id, name, status,author_type,author_id, version, created_at) FROM bids WHERE author_type = 'User' and author_id = (SELECT id FROM employee WHERE username = $1) ORDER BY name LIMIT $2 OFFSET $3;`
	rows, err := s.db.Query(ctx, query, username, limit, offset)
	defer rows.Close()
	if err != nil {
		return nil, fmt.Errorf("error querying bids: %v", err)
	}
	var bidsSlice []entity.BidsResponse
	for rows.Next() {
		var bid entity.BidsResponse
		if err := rows.Scan(&bid); err != nil {
			return nil, fmt.Errorf("error scanning bids: %v", err)
		}
		bidsSlice = append(bidsSlice, bid)
	}
	return bidsSlice, nil
}
func (s *Storage) GetTenderStatus(ctx context.Context, tenderUUID uuid.UUID, username string) (string, error) {
	if username != "" {
		err := s.isEmployeeExistByUsername(ctx, username)
		if err != nil {
			return "", err
		}
	}
	query := `SELECT status, organization_id FROM tenders WHERE id = $1;`

	var status string
	var organizationId uuid.UUID

	if err := s.db.QueryRow(ctx, query, tenderUUID).Scan(&status, &organizationId); err != nil {
		if err.Error() == "no rows in result set" {
			return "", storage.ErrTenderDoesntExist
		}
		return "", fmt.Errorf("error querying status: %v", err)
	}

	if status == "Published" {
		return status, nil
	}
	if username != "" {
		ok, err := s.checkUserOrganization(ctx, username, organizationId)
		if err != nil {
			return "", err
		}
		if !ok {
			return "", storage.ErrNoSuchEmployeeInOrganization
		}
	}

	return status, nil
}
func (s *Storage) EditTenderStatus(ctx context.Context, tenderUUID uuid.UUID, username, newStatus string) (*entity.TenderResponse, error) {
	if err := s.checkTenderByUser(ctx, tenderUUID, username); err != nil {
		return nil, err
	}
	tender, err := s.getTenderById(ctx, tenderUUID)
	if err != nil {
		return nil, err
	}
	if tender.Status == newStatus {
		return nil, storage.ErrNoChanges
	}
	updateQuery := `UPDATE tenders set status = $1 WHERE id=$2;`
	_, err = s.db.Exec(ctx, updateQuery, newStatus, tenderUUID)
	if err != nil {
		return nil, fmt.Errorf("error querying status: %v", err)
	}
	tender.Status = newStatus
	return tender, nil
}
func (s *Storage) EditTender(ctx context.Context, tenderUUID uuid.UUID, username string, changes []string, params []interface{}) (*entity.TenderResponse, error) {
	if err := s.checkTenderByUser(ctx, tenderUUID, username); err != nil {
		return nil, err
	}
	oldTender, err := s.getTenderById(ctx, tenderUUID)
	if err != nil {
		return nil, err
	}
	query := fmt.Sprintf("UPDATE tenders SET %s WHERE id = $1", strings.Join(changes, ", "))
	_, err = s.db.Exec(ctx, query, params...)
	newTender, err := s.getTenderById(ctx, tenderUUID)
	if err != nil {
		return nil, err
	}
	if !(oldTender.Version != newTender.Version) {
		return nil, storage.ErrNoChanges
	}
	return s.getTenderById(ctx, tenderUUID)
}
func (s *Storage) RollBackTender(ctx context.Context, tenderUUID uuid.UUID, username string, version int) (*entity.TenderResponse, error) {
	if err := s.checkTenderByUser(ctx, tenderUUID, username); err != nil {
		return nil, err
	}
	if err := s.isTenderVersionExist(ctx, tenderUUID, version); err != nil {
		return nil, err
	}
	query := `UPDATE tenders AS t
				SET name = h.name,
					description = h.description,
					service_type = h.service_type,
					status = h.status,
					version = h.version
				FROM tender_history AS h
				WHERE t.id = h.id
				  AND t.id = $1
				  AND h.version = $2;`
	_, err := s.db.Exec(ctx, query, tenderUUID, version)
	if err != nil {
		return nil, fmt.Errorf("error rolling back: %v", err)
	}
	return s.getTenderById(ctx, tenderUUID)

}
func (s *Storage) checkTenderByUser(ctx context.Context, tenderUUID uuid.UUID, username string) error {
	//Проверка на то, что пользователь есть
	var organizationId uuid.UUID
	tenderQuery := `SELECT organization_id FROM tenders WHERE id = $1;`
	if err := s.db.QueryRow(ctx, tenderQuery, tenderUUID).Scan(&organizationId); err != nil {
		if err.Error() == "no rows in result set" {
			return storage.ErrTenderDoesntExist
		}
		return fmt.Errorf("error querying status: %v", err)
	}
	ok, err := s.checkUserOrganization(ctx, username, organizationId)
	if err != nil {
		return err
	}
	if !ok {
		return storage.ErrNoSuchEmployeeInOrganization
	}
	return nil
}
func (s *Storage) isEmployeeExistByUsername(ctx context.Context, username string) error {
	var exists bool
	query := "SELECT EXISTS (SELECT 1 FROM employee WHERE username = $1)"
	err := s.db.QueryRow(ctx, query, username).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying employee: %v", err)
	}
	if !exists {
		return storage.ErrEmployeeDoesntExist
	}
	return nil
}
func (s *Storage) isEmployeeExistById(ctx context.Context, id uuid.UUID) error {
	var exists bool
	query := "SELECT EXISTS (SELECT 1 FROM employee WHERE id = $1)"
	err := s.db.QueryRow(ctx, query, id).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying employee: %v", err)
	}
	if !exists {
		return storage.ErrEmployeeDoesntExist
	}
	return nil
}
func (s *Storage) isOrganisationExist(ctx context.Context, id uuid.UUID) error {
	var exists bool
	query := "SELECT EXISTS (SELECT 1 FROM organization WHERE id = $1)"
	err := s.db.QueryRow(ctx, query, id).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying organization: %v", err)
	}
	if !exists {
		return storage.ErrOrganizationDoesntExist
	}
	return nil
}
func (s *Storage) getTenderById(ctx context.Context, tenderUUID uuid.UUID) (*entity.TenderResponse, error) {
	query := "SELECT (id, name, description, service_type, status, version, created_at) FROM tenders Where id=$1"
	var tender entity.TenderResponse
	err := s.db.QueryRow(ctx, query, tenderUUID).Scan(&tender)
	if err != nil {
		return &tender, fmt.Errorf("error querying tender: %v", err)
	}
	return &tender, nil
}
func (s *Storage) isTenderVersionExist(ctx context.Context, tenderUUID uuid.UUID, version int) error {
	query := `SELECT EXISTS (SELECT 1 FROM tender_history WHERE id = $1 AND version = $2);`
	var exists bool
	err := s.db.QueryRow(ctx, query, tenderUUID, version).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying tender_history: %v", err)
	}
	if !exists {
		return storage.ErrTenderWithThisVersionDoesntExist
	}
	return nil
}
func (s *Storage) isTenderExist(ctx context.Context, tenderUUID uuid.UUID) error {
	var exists bool
	query := `SELECT EXISTS (SELECT 1 FROM tenders WHERE id = $1);`
	err := s.db.QueryRow(ctx, query, tenderUUID).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying tender: %v", err)
	}
	if !exists {
		return storage.ErrTenderDoesntExist
	}
	return nil
}
func (s *Storage) isTenderStatusPublic(ctx context.Context, tenderUUID uuid.UUID) (bool, error) {
	query := `SELECT EXISTS (SELECT 1 FROM tenders WHERE id = $1 and status = 'Published');`
	var isPublished bool
	err := s.db.QueryRow(ctx, query, tenderUUID).Scan(&isPublished)
	if err != nil {
		return false, fmt.Errorf("error querying tender: %v", err)
	}
	return isPublished, nil
}
func (s *Storage) GetBidStatus(ctx context.Context, bidUUID uuid.UUID, username string) (string, error) {
	employeeId, err := s.getEmployeeId(ctx, username)
	if err != nil {
		return "", err
	}

	if err := s.isBidExist(ctx, bidUUID); err != nil {
		return "", err
	}

	var authorId uuid.UUID
	var authorType string
	var status string
	query := `SElECT author_id, author_type,status FROM bids WHERE id = $1;`
	if err := s.db.QueryRow(ctx, query, bidUUID).Scan(&authorId, &authorType, &status); err != nil {
		return "", fmt.Errorf("error querying author_id: %v", err)
	}

	switch authorType {
	case "User":
		if authorId == employeeId {
			return status, nil
		}
	case "Organization":
		ok, err := s.checkUserOrganization(ctx, username, authorId)
		if err != nil {
			return "", err
		}
		if ok {
			return status, nil
		}
	}
	return "", storage.ErrNotEnoughRights
}
func (s *Storage) isBidExist(ctx context.Context, bidUUID uuid.UUID) error {
	query := `SELECT EXISTS (SELECT 1 FROM bids WHERE id = $1);`
	var exists bool
	err := s.db.QueryRow(ctx, query, bidUUID).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying bid: %v", err)
	}
	if !exists {
		return storage.ErrBidDoesntExist
	}
	return nil
}
func (s *Storage) getEmployeeId(ctx context.Context, username string) (uuid.UUID, error) {
	query := `SELECT id FROM employee WHERE username = $1;`
	var employeeId uuid.UUID
	err := s.db.QueryRow(ctx, query, username).Scan(&employeeId)
	if err != nil {
		if err.Error() == "no rows in result set" {
			return employeeId, storage.ErrEmployeeDoesntExist
		}
		return employeeId, fmt.Errorf("error querying employee: %v", err)
	}
	return employeeId, nil
}
func (s *Storage) EditBitStatus(ctx context.Context, bid uuid.UUID, username string, newStatus string) (*entity.BidsResponse, error) {
	if err := s.checkBidByUser(ctx, bid, username); err != nil {
		return nil, err
	}
	oldBid, err := s.getBid(ctx, bid)
	if err != nil {
		return nil, err
	}
	if oldBid.Status == newStatus {
		return nil, storage.ErrNoChanges
	}
	updateQuery := `UPDATE bids set status = $1 WHERE id = $2;`
	_, err = s.db.Exec(ctx, updateQuery, newStatus, bid)
	if err != nil {
		return nil, fmt.Errorf("error querying status: %v", err)
	}
	oldBid.Status = newStatus
	return oldBid, nil
}
func (s *Storage) checkBidByUser(ctx context.Context, bidUUID uuid.UUID, username string) error {
	if err := s.isBidExist(ctx, bidUUID); err != nil {
		return err
	}
	employeeId, err := s.getEmployeeId(ctx, username)
	if err != nil {
		return err
	}

	var authorId uuid.UUID
	var authorType string

	query := `SElECT author_id, author_type FROM bids WHERE id = $1;`
	if err := s.db.QueryRow(ctx, query, bidUUID).Scan(&authorId, &authorType); err != nil {
		return fmt.Errorf("error querying author_id: %v", err)
	}

	switch authorType {
	case "User":
		if authorId == employeeId {
			return nil
		}
	case "Organization":
		ok, err := s.checkUserOrganization(ctx, username, authorId)
		if err != nil {
			return err
		}
		if !ok {
			return storage.ErrNoSuchEmployeeInOrganization
		}
		return nil
	}
	return storage.ErrNotEnoughRights
}
func (s *Storage) getBid(ctx context.Context, bidUUID uuid.UUID) (*entity.BidsResponse, error) {
	query := "SELECT  (id, name, status,author_type,author_id, version, created_at) FROM bids Where id=$1"
	var bid entity.BidsResponse
	err := s.db.QueryRow(ctx, query, bidUUID).Scan(&bid)
	if err != nil {

		return &bid, fmt.Errorf("error querying tender: %v", err)
	}
	return &bid, nil
}
func (s *Storage) RollBackBid(ctx context.Context, bidUUID uuid.UUID, username string, version int) (*entity.BidsResponse, error) {
	if err := s.checkBidByUser(ctx, bidUUID, username); err != nil {
		return nil, err
	}
	if err := s.isBidVersionExist(ctx, bidUUID, version); err != nil {
		return nil, err
	}
	query := `UPDATE bids AS b
				SET name = h.name,
					description = h.description,
					version = h.version
				FROM bids_history AS h
				WHERE b.id = h.id
				  AND b.id = $1
				  AND h.version = $2;`
	_, err := s.db.Exec(ctx, query, bidUUID, version)
	if err != nil {
		return nil, fmt.Errorf("error rolling back: %v", err)
	}
	return s.getBid(ctx, bidUUID)
}
func (s *Storage) isBidVersionExist(ctx context.Context, bidUUID uuid.UUID, version int) error {
	query := `SELECT EXISTS (SELECT 1 FROM bids_history WHERE id = $1 AND version = $2);`
	var exists bool
	err := s.db.QueryRow(ctx, query, bidUUID, version).Scan(&exists)
	if err != nil {
		return fmt.Errorf("error querying bids_history: %v", err)
	}
	if !exists {
		return storage.ErrBidWithThisVersionDoesntExist
	}
	return nil
}
func (s *Storage) EditBid(ctx context.Context, bidUUID uuid.UUID, username string, changes []string, params []interface{}) (*entity.BidsResponse, error) {
	if err := s.checkBidByUser(ctx, bidUUID, username); err != nil {
		return nil, err
	}
	oldBid, err := s.getBid(ctx, bidUUID)
	if err != nil {
		return nil, err
	}
	query := fmt.Sprintf("UPDATE bids SET %s WHERE id = $1", strings.Join(changes, ", "))
	_, err = s.db.Exec(ctx, query, params...)
	newBid, err := s.getBid(ctx, bidUUID)
	if err != nil {
		return nil, err
	}
	if !(oldBid.Version != newBid.Version) {
		return nil, storage.ErrNoChanges
	}
	return newBid, nil
}
func (s *Storage) SendFeedback(ctx context.Context, bidUUID uuid.UUID, username string, feedback string) (*entity.BidsResponse, error) {
	if err := s.isBidExist(ctx, bidUUID); err != nil {
		return nil, err
	}
	employeeId, err := s.getEmployeeId(ctx, username)
	if err != nil {
		return nil, err
	}
	bid, err := s.getAllBidField(ctx, bidUUID)
	if err != nil {
		return nil, err
	}
	if bid.Status != "Published" {
		return nil, storage.ErrBidNotPublished
	}
	if err := s.checkTenderByUser(ctx, bid.TenderId, username); err != nil {
		return nil, err
	}
	query := `INSERT INTO review (bid_feedback, authorId, bidId) VALUES ($1, $2, $3);`
	_, err = s.db.Exec(ctx, query, feedback, employeeId, bidUUID)
	if err != nil {
		return nil, fmt.Errorf("error inserting feedback: %v", err)
	}
	return s.getBid(ctx, bidUUID)
}
func (s *Storage) getAllBidField(ctx context.Context, bidUUID uuid.UUID) (*entity.Bids, error) {
	query := `SELECT (id, name, description, status, tender_id, author_type, author_id, version, created_at) FROM bids WHERE id = $1;`
	var bid entity.Bids
	err := s.db.QueryRow(ctx, query, bidUUID).Scan(&bid)
	if err != nil {
		if err.Error() == "no rows in result set" {
			return nil, storage.ErrBidDoesntExist
		}
		return nil, fmt.Errorf("error querying bid: %v", err)
	}
	return &bid, nil
}
func (s *Storage) Review(ctx context.Context, tenderUUID uuid.UUID, authorUsername, requestUsername string, limit, offset int) ([]entity.Review, error) {
	if err := s.isEmployeeExistByUsername(ctx, requestUsername); err != nil {
		return nil, err
	}
	if err := s.checkTenderByUser(ctx, tenderUUID, requestUsername); err != nil {
		return nil, err
	}
	authorId, err := s.getEmployeeId(ctx, authorUsername)
	if err != nil {
		return nil, err
	}
	query := `SELECT (review.id, review.bid_feedback, review.created_at)
FROM review
         JOIN bids on review.bidId = bids.id
         JOIN tenders on bids.tender_id = tenders.id
WHERE bids.author_id = $1 and tenders.id = $2  ORDER BY review.created_at DESC LIMIT $3 offset $4;`
	rows, err := s.db.Query(ctx, query, authorId, tenderUUID, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("error querying review: %v", err)
	}
	defer rows.Close()
	var reviews []entity.Review
	var review entity.Review
	for rows.Next() {
		if err := rows.Scan(&review); err != nil {
			return nil, fmt.Errorf("error scanning reviews: %v", err)
		}
		reviews = append(reviews, review)
	}
	return reviews, nil
}
func (s *Storage) Decision(ctx context.Context, bidUUID uuid.UUID, username, decision string) (*entity.BidsResponse, error) {
	employId, err := s.getEmployeeId(ctx, username)
	if err != nil {
		return nil, err
	}
	if err := s.isBidExist(ctx, bidUUID); err != nil {
		return nil, err
	}
	tenderId, err := s.getTenderIdFromBid(ctx, bidUUID)
	if err != nil {
		return nil, err
	}
	isPublished, err := s.isTenderStatusPublic(ctx, tenderId)
	if !isPublished {
		return nil, storage.ErrTenderIsNotPublished
	}
	isPublished, err = s.isBidStatusPublic(ctx, bidUUID)
	if !isPublished {
		return nil, storage.ErrBidNotPublished
	}
	if err := s.checkTenderByUser(ctx, tenderId, username); err != nil {
		return nil, err
	}
	query := `INSERT INTO decision (decision, authorid, bidid) values ($1, $2,$3)`
	s.db.Exec(ctx, query, decision, employId, bidUUID)
	if decision == "Rejected" {
		query = `UPDATE bids SET status = 'Canceled'`
		_, err := s.db.Exec(ctx, query)
		return nil, err
	}
	var disCount int
	var employeeCount int
	query = `SELECT COUNT(DISTINCT authorId) FROM decision WHERE bidId = $1 AND decision = 'Approved';`
	if err := s.db.QueryRow(ctx, query, bidUUID).Scan(&disCount); err != nil {
		return nil, err
	}

	query = `SELECT COUNT (*) FROM organization_responsible WHERE organization_id = (select tenders.organization_id from tenders where id = $1) `
	if err := s.db.QueryRow(ctx, query, tenderId).Scan(&employeeCount); err != nil {
		return nil, err
	}
	employeeCount = min(3, employeeCount)
	if employeeCount <= disCount {
		query = `UPDATE tenders SET status = 'Closed'`

		if _, err := s.db.Exec(ctx, query); err != nil {
			return nil, fmt.Errorf("error updating tenders: %v", err)
		}
	}
	return s.getBid(ctx, bidUUID)
}
func (s *Storage) getTenderIdFromBid(ctx context.Context, bidUUID uuid.UUID) (uuid.UUID, error) {
	var tenderUUID uuid.UUID
	query := `SELECT tender_id FROM bids WHERE id = $1;`
	err := s.db.QueryRow(ctx, query, bidUUID).Scan(&tenderUUID)
	if err != nil {
		return tenderUUID, fmt.Errorf("error querying tender: %v", err)
	}
	return tenderUUID, nil
}
func (s *Storage) isBidStatusPublic(ctx context.Context, bidUUID uuid.UUID) (bool, error) {
	query := `SELECT status FROM bids WHERE id = $1;`
	var status string
	if err := s.db.QueryRow(ctx, query, bidUUID).Scan(&status); err != nil {
		return false, fmt.Errorf("error querying bid: %v", err)
	}
	return status == "Published", nil
}