package repository

import (
	"aggios-app/backend/internal/domain"
	"database/sql"
	"fmt"
)

type DocumentRepository struct {
	db *sql.DB
}

func NewDocumentRepository(db *sql.DB) *DocumentRepository {
	return &DocumentRepository{db: db}
}

func (r *DocumentRepository) Create(doc *domain.Document) error {
	query := `
		INSERT INTO documents (id, tenant_id, parent_id, title, content, status, created_by, last_updated_by, version, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $7, 1, NOW(), NOW())
	`
	_, err := r.db.Exec(query, doc.ID, doc.TenantID, doc.ParentID, doc.Title, doc.Content, doc.Status, doc.CreatedBy)
	if err != nil {
		return err
	}
	return r.logActivity(doc.ID.String(), doc.TenantID.String(), doc.CreatedBy.String(), "created", "Criou o documento")
}

func (r *DocumentRepository) GetByTenant(tenantID string) ([]domain.Document, error) {
	query := `
		SELECT id, tenant_id, parent_id, title, content, status, created_by, last_updated_by, version, created_at, updated_at
		FROM documents
		WHERE tenant_id = $1 AND parent_id IS NULL
		ORDER BY updated_at DESC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var docs []domain.Document
	for rows.Next() {
		var doc domain.Document
		if err := rows.Scan(&doc.ID, &doc.TenantID, &doc.ParentID, &doc.Title, &doc.Content, &doc.Status, &doc.CreatedBy, &doc.LastUpdatedBy, &doc.Version, &doc.CreatedAt, &doc.UpdatedAt); err != nil {
			return nil, err
		}
		docs = append(docs, doc)
	}
	return docs, nil
}

func (r *DocumentRepository) GetSubpages(parentID, tenantID string) ([]domain.Document, error) {
	query := `
		SELECT id, tenant_id, parent_id, title, content, status, created_by, last_updated_by, version, created_at, updated_at
		FROM documents
		WHERE parent_id = $1 AND tenant_id = $2
		ORDER BY created_at ASC
	`
	rows, err := r.db.Query(query, parentID, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var docs []domain.Document
	for rows.Next() {
		var doc domain.Document
		if err := rows.Scan(&doc.ID, &doc.TenantID, &doc.ParentID, &doc.Title, &doc.Content, &doc.Status, &doc.CreatedBy, &doc.LastUpdatedBy, &doc.Version, &doc.CreatedAt, &doc.UpdatedAt); err != nil {
			return nil, err
		}
		docs = append(docs, doc)
	}
	return docs, nil
}

func (r *DocumentRepository) GetByID(id, tenantID string) (*domain.Document, error) {
	query := `
		SELECT id, tenant_id, parent_id, title, content, status, created_by, last_updated_by, version, created_at, updated_at
		FROM documents
		WHERE id = $1 AND tenant_id = $2
	`
	var doc domain.Document
	err := r.db.QueryRow(query, id, tenantID).Scan(
		&doc.ID, &doc.TenantID, &doc.ParentID, &doc.Title, &doc.Content, &doc.Status, &doc.CreatedBy, &doc.LastUpdatedBy, &doc.Version, &doc.CreatedAt, &doc.UpdatedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &doc, nil
}

func (r *DocumentRepository) Update(doc *domain.Document) error {
	query := `
		UPDATE documents
		SET title = $1, content = $2, status = $3, last_updated_by = $4, version = version + 1, updated_at = NOW()
		WHERE id = $5 AND tenant_id = $6
	`
	_, err := r.db.Exec(query, doc.Title, doc.Content, doc.Status, doc.LastUpdatedBy, doc.ID, doc.TenantID)
	if err != nil {
		return err
	}
	return r.logActivity(doc.ID.String(), doc.TenantID.String(), doc.LastUpdatedBy.String(), "updated", "Atualizou o conteúdo")
}

func (r *DocumentRepository) Delete(id, tenantID string) error {
	query := "DELETE FROM documents WHERE id = $1 AND tenant_id = $2"
	res, err := r.db.Exec(query, id, tenantID)
	if err != nil {
		return err
	}
	rows, _ := res.RowsAffected()
	if rows == 0 {
		return fmt.Errorf("document not found")
	}
	return nil
}

func (r *DocumentRepository) logActivity(docID, tenantID, userID, action, description string) error {
	query := `
		INSERT INTO document_activities (document_id, tenant_id, user_id, action, description)
		VALUES ($1, $2, $3, $4, $5)
	`
	_, err := r.db.Exec(query, docID, tenantID, userID, action, description)
	return err
}

func (r *DocumentRepository) GetActivities(docID, tenantID string) ([]domain.DocumentActivity, error) {
	query := `
		SELECT a.id, a.document_id, a.tenant_id, a.user_id, COALESCE(u.first_name, 'Usuário Removido') as user_name, a.action, a.description, a.created_at
		FROM document_activities a
		LEFT JOIN users u ON a.user_id = u.id
		WHERE a.document_id = $1 AND a.tenant_id = $2
		ORDER BY a.created_at DESC
		LIMIT 20
	`
	rows, err := r.db.Query(query, docID, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var activities []domain.DocumentActivity
	for rows.Next() {
		var a domain.DocumentActivity
		err := rows.Scan(&a.ID, &a.DocumentID, &a.TenantID, &a.UserID, &a.UserName, &a.Action, &a.Description, &a.CreatedAt)
		if err != nil {
			return nil, err
		}
		activities = append(activities, a)
	}
	return activities, nil
}