-- Add index column for task ordering in the kanban view
ALTER TABLE public.tasks
  ADD COLUMN IF NOT EXISTS "index" integer;

-- Backfill index per status using due date as a stable baseline
WITH ordered AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY status
      ORDER BY due_date NULLS LAST, created_at NULLS LAST, id
    ) - 1 AS new_index
  FROM public.tasks
)
UPDATE public.tasks t
SET "index" = ordered.new_index
FROM ordered
WHERE t.id = ordered.id
  AND t."index" IS NULL;

CREATE INDEX IF NOT EXISTS idx_tasks_status_index ON public.tasks(status, "index");

-- Recreate tasks_summary view to include index
DROP VIEW IF EXISTS public.tasks_summary;
CREATE VIEW public.tasks_summary AS
SELECT
  t.id,
  t.contact_id,
  t.company_id,
  t.deal_id,
  t.type,
  t.text,
  t.due_date,
  t.done_date,
  t.sales_id,
  t.priority,
  t.assigned_to,
  t.status,
  t."index",
  t.created_at,
  t.updated_at,
  t.archived,
  t.archived_at,
  -- Contact information (when task is linked to a contact)
  c.first_name as contact_first_name,
  c.last_name as contact_last_name,
  c.email_jsonb->0->>'email' as contact_email,
  -- Company information
  -- Priority: task.company_id (direct link) > contact.company_id (through contact)
  COALESCE(comp_direct.id, comp_via_contact.id) as company_id_computed,
  COALESCE(comp_direct.name, comp_via_contact.name) as company_name,
  -- Deal information (when task is linked to a deal)
  d.name as deal_name,
  -- Assigned sales person
  s_assigned.first_name as assigned_first_name,
  s_assigned.last_name as assigned_last_name,
  -- Creator sales person
  s_creator.first_name as creator_first_name,
  s_creator.last_name as creator_last_name,
  -- Task note count
  count(DISTINCT tn.id) as nb_notes,
  -- Most recent note
  max(tn.date) as last_note_date
FROM public.tasks t
-- Join with contact if task.contact_id is set
LEFT JOIN public.contacts c ON t.contact_id = c.id
-- Join with company via contact's company_id
LEFT JOIN public.companies comp_via_contact ON c.company_id = comp_via_contact.id
-- Join directly with company if task.company_id is set
LEFT JOIN public.companies comp_direct ON t.company_id = comp_direct.id
-- Join with deal if task.deal_id is set
LEFT JOIN public.deals d ON t.deal_id = d.id
-- Join with sales
LEFT JOIN public.sales s_assigned ON t.assigned_to = s_assigned.id
LEFT JOIN public.sales s_creator ON t.sales_id = s_creator.id
LEFT JOIN public."taskNotes" tn ON t.id = tn.task_id
GROUP BY
  t.id,
  c.first_name, c.last_name, c.email_jsonb,
  comp_direct.id, comp_direct.name,
  comp_via_contact.id, comp_via_contact.name,
  d.name,
  s_assigned.first_name, s_assigned.last_name,
  s_creator.first_name, s_creator.last_name;

GRANT SELECT ON public.tasks_summary TO authenticated;
