package repository

import (
    "database/sql"
    "fmt"
)

type Collaborator struct {
    ID   int64  `json:"id"`
    Name string `json:"name"`
}

type Event struct {
    ID              int
    Name            string
    Date            string
    PassageValue    string
    HotelValue      string
    GiftValue       string
    TeamValue       string
    SponsorValue    string
    TotalValue      string
    CollaboratorIDs []int64 `json:"-"`
    Collaborators   []Collaborator
}

type EventRepository struct {
    DB *sql.DB
}

func NewEventRepository(db *sql.DB) EventRepository {
    return EventRepository{DB: db}
}

func (r EventRepository) GetEvents() ([]Event, error) {
    rows, err := r.DB.Query(`
        SELECT 
            e.event_id,
            e.event_name,
            e.event_date,
            e.event_passage_value,
            e.event_hotel_value,
            e.event_gift_value,
            e.event_team_value,
            e.event_sponsor_value,
            e.event_total_value,
            c.collaborator_id,
            c.collaborator_name
        FROM events e
        NATURAL JOIN event_collaborators
        NATURAL JOIN collaborator c
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    eventsMap := make(map[int]Event)

    for rows.Next() {
        var id int
        var name, date, passage, hotel, gift, team, sponsor, total string
        var collID int64
        var collName string

        if err := rows.Scan(&id, &name, &date, &passage, &hotel, &gift, &team, &sponsor, &total, &collID, &collName); err != nil {
            return nil, err
        }

        e, exists := eventsMap[id]
        if !exists {
            e = Event{
                ID:              id,
                Name:            name,
                Date:            date,
                PassageValue:    passage,
                HotelValue:      hotel,
                GiftValue:       gift,
                TeamValue:       team,
                SponsorValue:    sponsor,
                TotalValue:      total,
                CollaboratorIDs: []int64{},
                Collaborators:   []Collaborator{},
            }
        }

        e.Collaborators = append(e.Collaborators, Collaborator{ID: collID, Name: collName})
        eventsMap[id] = e
    }

    events := make([]Event, 0, len(eventsMap))
    for _, e := range eventsMap {
        events = append(events, e)
    }

    return events, rows.Err()
}

func (r EventRepository) CreateEvent(event *Event) (int64, error) {
    tx, err := r.DB.Begin()
    if err != nil {
        return 0, fmt.Errorf("failed to begin transaction: %w", err)
    }

    var eventID int64
    err = tx.QueryRow(`
        INSERT INTO events (
            event_name, 
            event_date, 
            event_passage_value, 
            event_hotel_value, 
            event_gift_value, 
            event_team_value, 
            event_sponsor_value, 
            event_total_value
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        RETURNING event_id
    `, 
        event.Name,
        event.Date,
        event.PassageValue,
        event.HotelValue,
        event.GiftValue,
        event.TeamValue,
        event.SponsorValue,
        event.TotalValue,
    ).Scan(&eventID)

    if err != nil {
        tx.Rollback()
        return 0, fmt.Errorf("failed to insert event: %w", err)
    }

    for _, collabID := range event.CollaboratorIDs {
        if collabID <= 0 {
            continue
        }

        var exists bool
        err = tx.QueryRow(
            "SELECT EXISTS(SELECT 1 FROM collaborator WHERE collaborator_id = ?)",
            collabID,
        ).Scan(&exists)

        if err != nil {
            tx.Rollback()
            return 0, fmt.Errorf("failed to verify collaborator: %w", err)
        }

        if !exists {
            tx.Rollback()
            return 0, fmt.Errorf("collaborator with ID %d does not exist", collabID)
        }

        _, err = tx.Exec(
            "INSERT INTO event_collaborators (event_id, collaborator_id) VALUES (?, ?)",
            eventID,
            collabID,
        )
        if err != nil {
            tx.Rollback()
            return 0, fmt.Errorf("failed to associate collaborator %d with event: %w", collabID, err)
        }
    }

    if err := tx.Commit(); err != nil {
        return 0, fmt.Errorf("failed to commit transaction: %w", err)
    }

    return eventID, nil
}