package store

import (
	"context"
	"database/sql"
	"time"

	"github.com/google/uuid"
)

// Contact methods

func (s *PostgresStore) ListContacts(workspaceSlug string) []Contact {
	rows, err := s.db.Query(`
		SELECT c.id, c.workspace_slug, c.first_name, c.last_name, c.email, c.phone, 
		       c.company_id, COALESCE(co.name, ''), c.title, c.notes, c.avatar_url, 
		       c.created_at, c.updated_at
		FROM contacts c
		LEFT JOIN companies co ON c.company_id = co.id
		WHERE c.workspace_slug = $1
		ORDER BY c.updated_at DESC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var contacts []Contact
	for rows.Next() {
		var c Contact
		var companyID sql.NullString
		if err := rows.Scan(&c.ID, &c.WorkspaceSlug, &c.FirstName, &c.LastName, &c.Email,
			&c.Phone, &companyID, &c.CompanyName, &c.Title, &c.Notes, &c.AvatarURL,
			&c.CreatedAt, &c.UpdatedAt); err != nil {
			continue
		}
		c.CompanyID = nullStringToPtr(companyID)
		contacts = append(contacts, c)
	}
	return contacts
}

func (s *PostgresStore) GetContactByID(contactID string) (Contact, error) {
	var c Contact
	var companyID sql.NullString
	err := s.db.QueryRow(`
		SELECT c.id, c.workspace_slug, c.first_name, c.last_name, c.email, c.phone,
		       c.company_id, COALESCE(co.name, ''), c.title, c.notes, c.avatar_url,
		       c.created_at, c.updated_at
		FROM contacts c
		LEFT JOIN companies co ON c.company_id = co.id
		WHERE c.id = $1
	`, contactID).Scan(&c.ID, &c.WorkspaceSlug, &c.FirstName, &c.LastName, &c.Email,
		&c.Phone, &companyID, &c.CompanyName, &c.Title, &c.Notes, &c.AvatarURL,
		&c.CreatedAt, &c.UpdatedAt)
	if err != nil {
		return c, err
	}
	c.CompanyID = nullStringToPtr(companyID)
	return c, nil
}

func (s *PostgresStore) CreateContact(input CreateContactInput) Contact {
	now := time.Now().UTC()
	c := Contact{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		FirstName:     input.FirstName,
		LastName:      input.LastName,
		Email:         input.Email,
		Phone:         input.Phone,
		CompanyID:     input.CompanyID,
		Title:         input.Title,
		Notes:         input.Notes,
		CreatedAt:     now,
		UpdatedAt:     now,
	}

	s.db.Exec(`
		INSERT INTO contacts (id, workspace_slug, first_name, last_name, email, phone, 
		                      company_id, title, notes, avatar_url, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, '', $10, $11)
	`, c.ID, c.WorkspaceSlug, c.FirstName, c.LastName, c.Email, c.Phone,
		ptrToNullString(c.CompanyID), c.Title, c.Notes, c.CreatedAt, c.UpdatedAt)

	return c
}

func (s *PostgresStore) UpdateContact(contactID string, input UpdateContactInput) (Contact, error) {
	c, err := s.GetContactByID(contactID)
	if err != nil {
		return c, err
	}

	if input.FirstName != nil {
		c.FirstName = *input.FirstName
	}
	if input.LastName != nil {
		c.LastName = *input.LastName
	}
	if input.Email != nil {
		c.Email = *input.Email
	}
	if input.Phone != nil {
		c.Phone = *input.Phone
	}
	if input.CompanyID != nil {
		c.CompanyID = input.CompanyID
	}
	if input.Title != nil {
		c.Title = *input.Title
	}
	if input.Notes != nil {
		c.Notes = *input.Notes
	}
	c.UpdatedAt = time.Now().UTC()

	s.db.Exec(`
		UPDATE contacts SET first_name = $1, last_name = $2, email = $3, phone = $4,
		                    company_id = $5, title = $6, notes = $7, updated_at = $8
		WHERE id = $9
	`, c.FirstName, c.LastName, c.Email, c.Phone, ptrToNullString(c.CompanyID),
		c.Title, c.Notes, c.UpdatedAt, c.ID)

	return c, nil
}

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

// Company methods

func (s *PostgresStore) ListCompanies(workspaceSlug string) []Company {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, name, domain, website, industry, size, notes, logo_url, created_at, updated_at
		FROM companies
		WHERE workspace_slug = $1
		ORDER BY name ASC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var companies []Company
	for rows.Next() {
		var c Company
		if err := rows.Scan(&c.ID, &c.WorkspaceSlug, &c.Name, &c.Domain, &c.Website,
			&c.Industry, &c.Size, &c.Notes, &c.LogoURL, &c.CreatedAt, &c.UpdatedAt); err != nil {
			continue
		}
		companies = append(companies, c)
	}
	return companies
}

func (s *PostgresStore) GetCompanyByID(companyID string) (Company, error) {
	var c Company
	err := s.db.QueryRow(`
		SELECT id, workspace_slug, name, domain, website, industry, size, notes, logo_url, created_at, updated_at
		FROM companies
		WHERE id = $1
	`, companyID).Scan(&c.ID, &c.WorkspaceSlug, &c.Name, &c.Domain, &c.Website,
		&c.Industry, &c.Size, &c.Notes, &c.LogoURL, &c.CreatedAt, &c.UpdatedAt)
	return c, err
}

func (s *PostgresStore) CreateCompany(input CreateCompanyInput) Company {
	now := time.Now().UTC()
	c := Company{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Name:          input.Name,
		Domain:        input.Domain,
		Website:       input.Website,
		Industry:      input.Industry,
		Size:          input.Size,
		Notes:         input.Notes,
		CreatedAt:     now,
		UpdatedAt:     now,
	}

	s.db.Exec(`
		INSERT INTO companies (id, workspace_slug, name, domain, website, industry, size, notes, logo_url, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, '', $9, $10)
	`, c.ID, c.WorkspaceSlug, c.Name, c.Domain, c.Website, c.Industry, c.Size, c.Notes, c.CreatedAt, c.UpdatedAt)

	return c
}

func (s *PostgresStore) UpdateCompany(companyID string, input UpdateCompanyInput) (Company, error) {
	c, err := s.GetCompanyByID(companyID)
	if err != nil {
		return c, err
	}

	if input.Name != nil {
		c.Name = *input.Name
	}
	if input.Domain != nil {
		c.Domain = *input.Domain
	}
	if input.Website != nil {
		c.Website = *input.Website
	}
	if input.Industry != nil {
		c.Industry = *input.Industry
	}
	if input.Size != nil {
		c.Size = *input.Size
	}
	if input.Notes != nil {
		c.Notes = *input.Notes
	}
	c.UpdatedAt = time.Now().UTC()

	s.db.Exec(`
		UPDATE companies SET name = $1, domain = $2, website = $3, industry = $4, size = $5, notes = $6, updated_at = $7
		WHERE id = $8
	`, c.Name, c.Domain, c.Website, c.Industry, c.Size, c.Notes, c.UpdatedAt, c.ID)

	return c, nil
}

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

// Contact-Task linking

func (s *PostgresStore) LinkContactToTask(contactID, taskID string) error {
	_, err := s.db.Exec(`
		INSERT INTO contact_tasks (id, contact_id, task_id, created_at)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (contact_id, task_id) DO NOTHING
	`, uuid.NewString(), contactID, taskID, time.Now().UTC())
	return err
}

func (s *PostgresStore) UnlinkContactFromTask(contactID, taskID string) error {
	_, err := s.db.Exec(`DELETE FROM contact_tasks WHERE contact_id = $1 AND task_id = $2`, contactID, taskID)
	return err
}

func (s *PostgresStore) ListContactsForTask(taskID string) []Contact {
	rows, err := s.db.Query(`
		SELECT c.id, c.workspace_slug, c.first_name, c.last_name, c.email, c.phone,
		       c.company_id, COALESCE(co.name, ''), c.title, c.notes, c.avatar_url,
		       c.created_at, c.updated_at
		FROM contacts c
		JOIN contact_tasks ct ON c.id = ct.contact_id
		LEFT JOIN companies co ON c.company_id = co.id
		WHERE ct.task_id = $1
	`, taskID)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var contacts []Contact
	for rows.Next() {
		var c Contact
		var companyID sql.NullString
		if err := rows.Scan(&c.ID, &c.WorkspaceSlug, &c.FirstName, &c.LastName, &c.Email,
			&c.Phone, &companyID, &c.CompanyName, &c.Title, &c.Notes, &c.AvatarURL,
			&c.CreatedAt, &c.UpdatedAt); err != nil {
			continue
		}
		c.CompanyID = nullStringToPtr(companyID)
		contacts = append(contacts, c)
	}
	return contacts
}

// Contact-Event linking

func (s *PostgresStore) LinkContactToEvent(contactID, eventID string) error {
	_, err := s.db.Exec(`
		INSERT INTO contact_events (id, contact_id, event_id, created_at)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (contact_id, event_id) DO NOTHING
	`, uuid.NewString(), contactID, eventID, time.Now().UTC())
	return err
}

func (s *PostgresStore) ListContactsForEvent(eventID string) []Contact {
	rows, err := s.db.Query(`
		SELECT c.id, c.workspace_slug, c.first_name, c.last_name, c.email, c.phone,
		       c.company_id, COALESCE(co.name, ''), c.title, c.notes, c.avatar_url,
		       c.created_at, c.updated_at
		FROM contacts c
		JOIN contact_events ce ON c.id = ce.contact_id
		LEFT JOIN companies co ON c.company_id = co.id
		WHERE ce.event_id = $1
	`, eventID)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var contacts []Contact
	for rows.Next() {
		var c Contact
		var companyID sql.NullString
		if err := rows.Scan(&c.ID, &c.WorkspaceSlug, &c.FirstName, &c.LastName, &c.Email,
			&c.Phone, &companyID, &c.CompanyName, &c.Title, &c.Notes, &c.AvatarURL,
			&c.CreatedAt, &c.UpdatedAt); err != nil {
			continue
		}
		c.CompanyID = nullStringToPtr(companyID)
		contacts = append(contacts, c)
	}
	return contacts
}

// Helper functions

func nullStringToPtr(ns sql.NullString) *string {
	if !ns.Valid {
		return nil
	}
	return &ns.String
}

func ptrToNullString(s *string) sql.NullString {
	if s == nil {
		return sql.NullString{Valid: false}
	}
	return sql.NullString{String: *s, Valid: true}
}

// Inbox methods

func (s *PostgresStore) ListInboxItems(workspaceSlug string) []InboxItem {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, content, source, processed, processed_at, 
		       processed_entity_type, processed_entity_id, created_at
		FROM inbox_items
		WHERE workspace_slug = $1 AND processed = false
		ORDER BY created_at DESC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var items []InboxItem
	for rows.Next() {
		var item InboxItem
		var processedAt sql.NullTime
		var processedEntityType, processedEntityID sql.NullString
		if err := rows.Scan(&item.ID, &item.WorkspaceSlug, &item.Content, &item.Source,
			&item.Processed, &processedAt, &processedEntityType, &processedEntityID,
			&item.CreatedAt); err != nil {
			continue
		}
		item.ProcessedAt = nullTimeToPtr(processedAt)
		item.ProcessedEntityType = nullStringToPtr(processedEntityType)
		item.ProcessedEntityID = nullStringToPtr(processedEntityID)
		items = append(items, item)
	}
	return items
}

func (s *PostgresStore) CreateInboxItem(input CreateInboxItemInput) InboxItem {
	now := time.Now().UTC()
	item := InboxItem{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Content:       input.Content,
		Source:        input.Source,
		CreatedAt:     now,
	}
	if item.Source == "" {
		item.Source = "manual"
	}

	s.db.Exec(`
		INSERT INTO inbox_items (id, workspace_slug, content, source, processed, created_at)
		VALUES ($1, $2, $3, $4, false, $5)
	`, item.ID, item.WorkspaceSlug, item.Content, item.Source, item.CreatedAt)

	return item
}

func (s *PostgresStore) ProcessInboxItem(itemID string, entityType, entityID string) error {
	now := time.Now().UTC()
	_, err := s.db.Exec(`
		UPDATE inbox_items 
		SET processed = true, processed_at = $1, processed_entity_type = $2, processed_entity_id = $3
		WHERE id = $4
	`, now, entityType, entityID, itemID)
	return err
}

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

// Time entry methods

func (s *PostgresStore) ListTimeEntries(workspaceSlug string) []TimeEntry {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, task_id, description, started_at, ended_at, duration_seconds, created_at, updated_at
		FROM time_entries
		WHERE workspace_slug = $1
		ORDER BY started_at DESC
	`, workspaceSlug)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var entries []TimeEntry
	for rows.Next() {
		var e TimeEntry
		var taskID sql.NullString
		var endedAt sql.NullTime
		if err := rows.Scan(&e.ID, &e.WorkspaceSlug, &taskID, &e.Description,
			&e.StartedAt, &endedAt, &e.DurationSeconds, &e.CreatedAt, &e.UpdatedAt); err != nil {
			continue
		}
		e.TaskID = nullStringToPtr(taskID)
		e.EndedAt = nullTimeToPtr(endedAt)
		entries = append(entries, e)
	}
	return entries
}

func (s *PostgresStore) CreateTimeEntry(input CreateTimeEntryInput) TimeEntry {
	now := time.Now().UTC()
	e := TimeEntry{
		ID:             uuid.NewString(),
		WorkspaceSlug:  input.WorkspaceSlug,
		TaskID:         input.TaskID,
		Description:    input.Description,
		StartedAt:      input.StartedAt,
		EndedAt:        input.EndedAt,
		DurationSeconds: 0,
		CreatedAt:      now,
		UpdatedAt:      now,
	}

	if input.EndedAt != nil {
		e.DurationSeconds = int(input.EndedAt.Sub(input.StartedAt).Seconds())
	}

	s.db.Exec(`
		INSERT INTO time_entries (id, workspace_slug, task_id, description, started_at, ended_at, duration_seconds, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
	`, e.ID, e.WorkspaceSlug, ptrToNullString(e.TaskID), e.Description, e.StartedAt,
		ptrToNullTime(e.EndedAt), e.DurationSeconds, e.CreatedAt, e.UpdatedAt)

	return e
}

func (s *PostgresStore) UpdateTimeEntry(entryID string, input UpdateTimeEntryInput) (TimeEntry, error) {
	var e TimeEntry
	var taskID sql.NullString
	var endedAt sql.NullTime
	err := s.db.QueryRow(`
		SELECT id, workspace_slug, task_id, description, started_at, ended_at, duration_seconds, created_at, updated_at
		FROM time_entries WHERE id = $1
	`, entryID).Scan(&e.ID, &e.WorkspaceSlug, &taskID, &e.Description, &e.StartedAt, &endedAt, &e.DurationSeconds, &e.CreatedAt, &e.UpdatedAt)
	if err != nil {
		return e, err
	}
	e.TaskID = nullStringToPtr(taskID)
	e.EndedAt = nullTimeToPtr(endedAt)

	if input.Description != nil {
		e.Description = *input.Description
	}
	if input.EndedAt != nil {
		e.EndedAt = input.EndedAt
		e.DurationSeconds = int(input.EndedAt.Sub(e.StartedAt).Seconds())
	}
	e.UpdatedAt = time.Now().UTC()

	s.db.Exec(`
		UPDATE time_entries SET description = $1, ended_at = $2, duration_seconds = $3, updated_at = $4
		WHERE id = $5
	`, e.Description, ptrToNullTime(e.EndedAt), e.DurationSeconds, e.UpdatedAt, e.ID)

	return e, nil
}

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

// Saved view methods

func (s *PostgresStore) ListSavedViews(workspaceSlug, entityType string) []SavedView {
	rows, err := s.db.Query(`
		SELECT id, workspace_slug, name, entity_type, filter_json, sort_json, is_default, created_at, updated_at
		FROM saved_views
		WHERE workspace_slug = $1 AND entity_type = $2
		ORDER BY name ASC
	`, workspaceSlug, entityType)
	if err != nil {
		return nil
	}
	defer rows.Close()

	var views []SavedView
	for rows.Next() {
		var v SavedView
		if err := rows.Scan(&v.ID, &v.WorkspaceSlug, &v.Name, &v.EntityType,
			&v.FilterJSON, &v.SortJSON, &v.IsDefault, &v.CreatedAt, &v.UpdatedAt); err != nil {
			continue
		}
		views = append(views, v)
	}
	return views
}

func (s *PostgresStore) CreateSavedView(input CreateSavedViewInput) SavedView {
	now := time.Now().UTC()
	v := SavedView{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Name:          input.Name,
		EntityType:    input.EntityType,
		FilterJSON:    input.FilterJSON,
		SortJSON:      input.SortJSON,
		IsDefault:     input.IsDefault,
		CreatedAt:     now,
		UpdatedAt:     now,
	}

	s.db.Exec(`
		INSERT INTO saved_views (id, workspace_slug, name, entity_type, filter_json, sort_json, is_default, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
	`, v.ID, v.WorkspaceSlug, v.Name, v.EntityType, v.FilterJSON, v.SortJSON, v.IsDefault, v.CreatedAt, v.UpdatedAt)

	return v
}

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

func nullTimeToPtr(nt sql.NullTime) *time.Time {
	if !nt.Valid {
		return nil
	}
	return &nt.Time
}

func ptrToNullTime(t *time.Time) sql.NullTime {
	if t == nil {
		return sql.NullTime{Valid: false}
	}
	return sql.NullTime{Time: *t, Valid: true}
}

// Ensure PostgresStore implements Store interface for new methods
var _ Store = (*PostgresStore)(nil)

// Add interface methods to store.go
// These will be added to the Store interface in store.go