package store

import (
	"database/sql"
	"time"

	"github.com/google/uuid"
)

// Integration methods

func (s *PostgresStore) ListIntegrations(workspaceSlug string) []Integration {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, provider, name, config, status, last_sync_at, created_at, updated_at
		FROM integrations
		WHERE workspace_slug = $1 AND status = 'active'
		ORDER BY created_at DESC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var integrations []Integration
	for rows.Next() {
		var i Integration
		var lastSync sql.NullTime
		if err := rows.Scan(&i.ID, &i.WorkspaceSlug, &i.Provider, &i.Name, &i.Config,
			&i.Status, &lastSync, &i.CreatedAt, &i.UpdatedAt); err != nil {
			continue
		}
		i.LastSyncAt = nullTimeToPtr(lastSync)
		integrations = append(integrations, i)
	}
	return integrations
}

func (s *PostgresStore) GetIntegrationByID(integrationID string) (Integration, error) {
	var i Integration
	var lastSync sql.NullTime
	err := s.db.QueryRow(`
		SELECT id, workspace_slug, provider, name, config, status, last_sync_at, created_at, updated_at
		FROM integrations WHERE id = $1
	`, integrationID).Scan(&i.ID, &i.WorkspaceSlug, &i.Provider, &i.Name, &i.Config,
		&i.Status, &lastSync, &i.CreatedAt, &i.UpdatedAt)
	if err != nil {
		return i, err
	}
	i.LastSyncAt = nullTimeToPtr(lastSync)
	return i, nil
}

func (s *PostgresStore) CreateIntegration(input CreateIntegrationInput) Integration {
	now := time.Now().UTC()
	i := Integration{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Provider:      input.Provider,
		Name:          input.Name,
		Config:        input.Config,
		Status:        "active",
		CreatedAt:     now,
		UpdatedAt:     now,
	}

	s.db.Exec(`
		INSERT INTO integrations (id, workspace_slug, provider, name, config, credentials_ciphertext, status, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, 'active', $7, $8)
	`, i.ID, i.WorkspaceSlug, i.Provider, i.Name, i.Config, input.Credentials, i.CreatedAt, i.UpdatedAt)

	return i
}

func (s *PostgresStore) DeleteIntegration(integrationID string) error {
	_, err := s.db.Exec(`UPDATE integrations SET status = 'deleted', updated_at = $1 WHERE id = $2`, time.Now().UTC(), integrationID)
	return err
}

// Webhook methods

func (s *PostgresStore) ListWebhooks(workspaceSlug string) []Webhook {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, name, url, events, active, last_triggered_at, created_at, updated_at
		FROM webhooks
		WHERE workspace_slug = $1
		ORDER BY created_at DESC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var webhooks []Webhook
	for rows.Next() {
		var w Webhook
		var lastTriggered sql.NullTime
		if err := rows.Scan(&w.ID, &w.WorkspaceSlug, &w.Name, &w.URL, &w.Events,
			&w.Active, &lastTriggered, &w.CreatedAt, &w.UpdatedAt); err != nil {
			continue
		}
		w.LastTriggeredAt = nullTimeToPtr(lastTriggered)
		webhooks = append(webhooks, w)
	}
	return webhooks
}

func (s *PostgresStore) CreateWebhook(input CreateWebhookInput) Webhook {
	now := time.Now().UTC()
	w := Webhook{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Name:          input.Name,
		URL:           input.URL,
		Events:        input.Events,
		Active:        true,
		CreatedAt:     now,
		UpdatedAt:     now,
	}
	if w.Events == "" {
		w.Events = "[]"
	}

	s.db.Exec(`
		INSERT INTO webhooks (id, workspace_slug, name, url, secret, events, active, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
	`, w.ID, w.WorkspaceSlug, w.Name, w.URL, uuid.NewString(), w.Events, w.Active, w.CreatedAt, w.UpdatedAt)

	return w
}

func (s *PostgresStore) DeleteWebhook(webhookID string) error {
	_, err := s.db.Exec(`DELETE FROM webhooks WHERE id = $1`, webhookID)
	return err
}

// Notification methods

func (s *PostgresStore) ListNotifications(userEmail string, limit int) []Notification {
	if limit <= 0 {
		limit = 50
	}
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, user_email, type, title, body, entity_type, entity_id, read, created_at
		FROM notifications
		WHERE user_email = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, userEmail, limit)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var notifications []Notification
	for rows.Next() {
		var n Notification
		var entityType, entityID sql.NullString
		if err := rows.Scan(&n.ID, &n.WorkspaceSlug, &n.UserEmail, &n.Type, &n.Title,
			&n.Body, &entityType, &entityID, &n.Read, &n.CreatedAt); err != nil {
			continue
		}
		n.EntityType = nullStringToPtr(entityType)
		n.EntityID = nullStringToPtr(entityID)
		notifications = append(notifications, n)
	}
	return notifications
}

func (s *PostgresStore) CreateNotification(input CreateNotificationInput) Notification {
	n := Notification{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		UserEmail:     input.UserEmail,
		Type:          input.Type,
		Title:         input.Title,
		Body:          input.Body,
		EntityType:    input.EntityType,
		EntityID:      input.EntityID,
		Read:          false,
		CreatedAt:     time.Now().UTC(),
	}

	s.db.Exec(`
		INSERT INTO notifications (id, workspace_slug, user_email, type, title, body, entity_type, entity_id, read, created_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, false, $9)
	`, n.ID, n.WorkspaceSlug, n.UserEmail, n.Type, n.Title, n.Body,
		ptrToNullString(n.EntityType), ptrToNullString(n.EntityID), n.CreatedAt)

	return n
}

func (s *PostgresStore) MarkNotificationRead(notificationID string) error {
	_, err := s.db.Exec(`UPDATE notifications SET read = true WHERE id = $1`, notificationID)
	return err
}

func (s *PostgresStore) MarkAllNotificationsRead(userEmail string) error {
	_, err := s.db.Exec(`UPDATE notifications SET read = true WHERE user_email = $1 AND read = false`, userEmail)
	return err
}

func (s *PostgresStore) UnreadNotificationCount(userEmail string) int {
	var count int
	s.db.QueryRow(`SELECT COUNT(*) FROM notifications WHERE user_email = $1 AND read = false`, userEmail).Scan(&count)
	return count
}

// Presence methods

func (s *PostgresStore) UpdatePresence(input UpdatePresenceInput) Presence {
	now := time.Now().UTC()

	// Upsert presence
	s.db.Exec(`
		INSERT INTO presence (id, workspace_slug, user_email, user_name, entity_type, entity_id, last_seen_at, created_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
		ON CONFLICT (workspace_slug, user_email) DO UPDATE SET
			user_name = EXCLUDED.user_name,
			entity_type = EXCLUDED.entity_type,
			entity_id = EXCLUDED.entity_id,
			last_seen_at = EXCLUDED.last_seen_at
	`, uuid.NewString(), input.WorkspaceSlug, input.UserEmail, input.UserName,
		ptrToNullString(input.EntityType), ptrToNullString(input.EntityID), now, now)

	return Presence{
		WorkspaceSlug: input.WorkspaceSlug,
		UserEmail:     input.UserEmail,
		UserName:      input.UserName,
		EntityType:    input.EntityType,
		EntityID:      input.EntityID,
		LastSeenAt:    now,
		CreatedAt:     now,
	}
}

func (s *PostgresStore) ListPresence(workspaceSlug string, entityType, entityID string) []Presence {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, user_email, user_name, entity_type, entity_id, last_seen_at, created_at
		FROM presence
		WHERE workspace_slug = $1 
		  AND last_seen_at > $2
		  AND ($3 = '' OR entity_type = $3)
		  AND ($4 = '' OR entity_id = $4)
		ORDER BY last_seen_at DESC
	`, workspaceSlug, time.Now().Add(-5*time.Minute), entityType, entityID)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var presences []Presence
	for rows.Next() {
		var p Presence
		var entityType, entityID sql.NullString
		if err := rows.Scan(&p.ID, &p.WorkspaceSlug, &p.UserEmail, &p.UserName,
			&entityType, &entityID, &p.LastSeenAt, &p.CreatedAt); err != nil {
			continue
		}
		p.EntityType = nullStringToPtr(entityType)
		p.EntityID = nullStringToPtr(entityID)
		presences = append(presences, p)
	}
	return presences
}

func (s *PostgresStore) ClearPresence(workspaceSlug, userEmail string) error {
	_, err := s.db.Exec(`DELETE FROM presence WHERE workspace_slug = $1 AND user_email = $2`, workspaceSlug, userEmail)
	return err
}