-- Additive planning extension, after 1069. Existing case, booking and time IDs are preserved.
ALTER TABLE sag_case_assignees
 ADD COLUMN IF NOT EXISTS estimated_minutes INTEGER CHECK (estimated_minutes >= 0),
 ADD COLUMN IF NOT EXISTS remaining_minutes INTEGER CHECK (remaining_minutes >= 0),
 ADD COLUMN IF NOT EXISTS queue_position NUMERIC NOT NULL DEFAULT 0,
 ADD COLUMN IF NOT EXISTS pinned BOOLEAN NOT NULL DEFAULT FALSE,
 ADD COLUMN IF NOT EXISTS work_status TEXT NOT NULL DEFAULT 'ready'
   CHECK (work_status IN ('ready','working','paused','waiting','done')),
 ADD COLUMN IF NOT EXISTS snoozed_until TIMESTAMPTZ,
 ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE sag_sager ADD COLUMN IF NOT EXISTS start_time TIME;
ALTER TABLE planner_settings ADD COLUMN IF NOT EXISTS appointment_warning_minutes INTEGER NOT NULL DEFAULT 30;
ALTER TABLE planner_settings ADD COLUMN IF NOT EXISTS deadline_warning_days INTEGER NOT NULL DEFAULT 1;
ALTER TABLE planner_settings ADD COLUMN IF NOT EXISTS queue_initialized BOOLEAN NOT NULL DEFAULT FALSE;
CREATE TABLE IF NOT EXISTS planner_queue_versions (
 user_id INTEGER PRIMARY KEY REFERENCES users(user_id), version INTEGER NOT NULL DEFAULT 1
);
DO $$ BEGIN
IF NOT COALESCE((SELECT queue_initialized FROM planner_settings WHERE id=1),FALSE) THEN
INSERT INTO sag_case_assignees(sag_id,user_id)
 SELECT s.id,s.ansvarlig_bruger_id FROM sag_sager s
 JOIN users u ON u.user_id=s.ansvarlig_bruger_id
 WHERE s.deleted_at IS NULL
 AND LOWER(s.status) NOT IN ('afsluttet','lukket','løst','resolved','closed','done','arkiveret','archived','annulleret','cancelled')
 ON CONFLICT DO NOTHING;
INSERT INTO sag_case_assignees(sag_id,user_id)
 SELECT DISTINCT a.sag_id,a.user_id FROM planner_allocations a
 JOIN sag_sager s ON s.id=a.sag_id
 WHERE a.deleted_at IS NULL AND s.deleted_at IS NULL
 AND LOWER(s.status) NOT IN ('afsluttet','lukket','løst','resolved','closed','done','arkiveret','archived','annulleret','cancelled')
 ON CONFLICT DO NOTHING;
WITH ranked AS (
 SELECT a.sag_id,a.user_id,ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY
 CASE s.priority::text WHEN 'urgent' THEN 0 WHEN 'high' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END,
 s.deadline NULLS LAST,s.id)*1024 AS position
 FROM sag_case_assignees a JOIN sag_sager s ON s.id=a.sag_id
)
UPDATE sag_case_assignees a SET queue_position=r.position FROM ranked r
 WHERE a.sag_id=r.sag_id AND a.user_id=r.user_id AND a.queue_position=0;
INSERT INTO planner_settings(id,queue_initialized) VALUES(1,TRUE)
 ON CONFLICT(id) DO UPDATE SET queue_initialized=TRUE;
END IF;
END $$;
CREATE INDEX IF NOT EXISTS planner_queue_order ON sag_case_assignees(user_id,pinned DESC,queue_position,sag_id);
-- Legacy assignment routes also invalidate queue snapshots and append new members.
CREATE OR REPLACE FUNCTION planner_assignee_membership() RETURNS trigger AS $$
DECLARE uid INTEGER;
BEGIN
 uid=CASE WHEN TG_OP='DELETE' THEN OLD.user_id ELSE NEW.user_id END;
 PERFORM id FROM sag_sager WHERE id=CASE WHEN TG_OP='DELETE' THEN OLD.sag_id ELSE NEW.sag_id END FOR KEY SHARE;
 PERFORM pg_advisory_xact_lock(252,uid);
 INSERT INTO planner_queue_versions(user_id,version) VALUES(uid,2)
 ON CONFLICT(user_id) DO UPDATE SET version=planner_queue_versions.version+1;
 IF TG_OP='INSERT' AND NEW.queue_position=0 THEN
  SELECT COALESCE(MAX(queue_position),0)+1024 INTO NEW.queue_position FROM sag_case_assignees WHERE user_id=uid;
 END IF;
 IF TG_OP='DELETE' THEN RETURN OLD; END IF;
 RETURN NEW;
END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS planner_assignee_membership ON sag_case_assignees;
CREATE TRIGGER planner_assignee_membership BEFORE INSERT OR DELETE ON sag_case_assignees
 FOR EACH ROW EXECUTE FUNCTION planner_assignee_membership();
CREATE TABLE IF NOT EXISTS planner_events (
 id BIGSERIAL PRIMARY KEY, sag_id INTEGER REFERENCES sag_sager(id),
 title TEXT NOT NULL, description TEXT, kind TEXT NOT NULL DEFAULT 'work',
 starts_at TIMESTAMPTZ NOT NULL, ends_at TIMESTAMPTZ NOT NULL,
 version INTEGER NOT NULL DEFAULT 1, created_by INTEGER REFERENCES users(user_id),
 updated_by INTEGER REFERENCES users(user_id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), deleted_at TIMESTAMPTZ,
 CHECK(ends_at>starts_at)
);
ALTER TABLE planner_allocations ALTER COLUMN sag_id DROP NOT NULL;
ALTER TABLE planner_allocations ADD COLUMN IF NOT EXISTS event_id BIGINT REFERENCES planner_events(id);
-- One event per legacy booking, deliberately no inference of shared meetings.
DO $$ DECLARE a RECORD; eid BIGINT; BEGIN
 FOR a IN SELECT p.*,s.titel FROM planner_allocations p JOIN sag_sager s ON s.id=p.sag_id WHERE event_id IS NULL LOOP
  INSERT INTO planner_events(sag_id,title,description,starts_at,ends_at,created_by,updated_by,deleted_at)
   VALUES(a.sag_id,a.titel,a.note,a.starts_at,a.ends_at,a.created_by,a.updated_by,a.deleted_at) RETURNING id INTO eid;
  UPDATE planner_allocations SET event_id=eid WHERE id=a.id;
 END LOOP;
END $$;
CREATE UNIQUE INDEX IF NOT EXISTS planner_event_participant ON planner_allocations(event_id,user_id) WHERE deleted_at IS NULL;
-- Legacy booking writes stay in sync with the shared event model.
CREATE OR REPLACE FUNCTION planner_booking_event() RETURNS trigger AS $$
DECLARE eid BIGINT; parent planner_events%ROWTYPE;
BEGIN
 IF NEW.deleted_at IS NULL AND NEW.sag_id IS NOT NULL AND (TG_OP='INSERT' OR NEW.ends_at>NOW()) THEN
  INSERT INTO sag_case_assignees(sag_id,user_id,assigned_by_user_id)
   VALUES(NEW.sag_id,NEW.user_id,NEW.updated_by) ON CONFLICT DO NOTHING;
 END IF;
 IF NEW.event_id IS NULL THEN
  INSERT INTO planner_events(sag_id,title,description,starts_at,ends_at,created_by,updated_by,deleted_at)
   VALUES(NEW.sag_id,COALESCE((SELECT titel FROM sag_sager WHERE id=NEW.sag_id),'Aftale'),
     NEW.note,NEW.starts_at,NEW.ends_at,NEW.created_by,NEW.updated_by,NEW.deleted_at) RETURNING id INTO eid;
  UPDATE planner_allocations SET event_id=eid WHERE id=NEW.id;
 ELSE
  SELECT * INTO parent FROM planner_events WHERE id=NEW.event_id FOR UPDATE;
  IF NEW.deleted_at IS NOT NULL THEN
   IF parent.deleted_at IS NULL AND NOT EXISTS(SELECT 1 FROM planner_allocations WHERE event_id=NEW.event_id AND deleted_at IS NULL) THEN
    UPDATE planner_events SET deleted_at=NOW(),version=version+1 WHERE id=NEW.event_id;
   END IF;
   RETURN NEW;
  END IF;
  IF parent.starts_at IS DISTINCT FROM NEW.starts_at OR parent.ends_at IS DISTINCT FROM NEW.ends_at
    OR parent.sag_id IS DISTINCT FROM NEW.sag_id OR parent.description IS DISTINCT FROM NEW.note THEN
   IF (SELECT COUNT(*) FROM planner_allocations WHERE event_id=NEW.event_id AND deleted_at IS NULL)>1 THEN
    RAISE EXCEPTION 'Shared appointments must be moved through the event API';
   END IF;
   UPDATE planner_events SET starts_at=NEW.starts_at,ends_at=NEW.ends_at,sag_id=NEW.sag_id,description=NEW.note,
    version=version+1,updated_by=NEW.updated_by,updated_at=NOW() WHERE id=NEW.event_id;
  END IF;
  IF parent.deleted_at IS NOT NULL THEN
   UPDATE planner_events SET deleted_at=NULL,version=version+1 WHERE id=NEW.event_id;
  END IF;
 END IF;
 RETURN NEW;
END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS planner_booking_event ON planner_allocations;
CREATE TRIGGER planner_booking_event AFTER INSERT OR UPDATE ON planner_allocations
 FOR EACH ROW EXECUTE FUNCTION planner_booking_event();
ALTER TABLE planner_allocation_history ALTER COLUMN allocation_id DROP NOT NULL;
ALTER TABLE planner_allocation_history ALTER COLUMN sag_id DROP NOT NULL;
ALTER TABLE planner_allocation_history ADD COLUMN IF NOT EXISTS entity_type TEXT DEFAULT 'allocation';
ALTER TABLE planner_allocation_history ADD COLUMN IF NOT EXISTS entity_id BIGINT;
CREATE TABLE IF NOT EXISTS planner_activity_types (
 code TEXT PRIMARY KEY, label TEXT NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE,
 is_absence BOOLEAN NOT NULL DEFAULT TRUE
);
INSERT INTO planner_activity_types(code,label) VALUES
 ('vacation','Ferie'),('sickness','Sygdom'),('child_sickness','Barns sygedag'),
 ('time_off','Afspadsering'),('course','Kursus'),('absence','Andet fravær') ON CONFLICT DO NOTHING;
INSERT INTO planner_activity_types(code,label,is_absence) VALUES
 ('customer_work','Kundearbejde',FALSE),('internal_work','Internt arbejde',FALSE),
 ('internal_meeting','Internt møde',FALSE),('customer_meeting','Kundemøde',FALSE) ON CONFLICT DO NOTHING;
INSERT INTO planner_activity_types(code,label)
 SELECT DISTINCT kind,kind FROM planner_absences WHERE kind IS NOT NULL ON CONFLICT DO NOTHING;
ALTER TABLE planner_absences ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE planner_absences ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE planner_absences ADD COLUMN IF NOT EXISTS created_by INTEGER REFERENCES users(user_id);
ALTER TABLE tmodule_times ADD COLUMN IF NOT EXISTS planner_absence_id BIGINT REFERENCES planner_absences(id);
ALTER TABLE tmodule_times ADD COLUMN IF NOT EXISTS planner_activity_code TEXT REFERENCES planner_activity_types(code);
CREATE UNIQUE INDEX IF NOT EXISTS planner_actual_absence_day ON tmodule_times(planner_absence_id,worked_date) WHERE planner_absence_id IS NOT NULL;
DROP INDEX IF EXISTS planner_actual_absence;
DO $$ BEGIN
 IF NOT EXISTS(SELECT 1 FROM pg_constraint WHERE conname='planner_absence_nonbillable' AND conrelid='tmodule_times'::regclass) THEN
  ALTER TABLE tmodule_times ADD CONSTRAINT planner_absence_nonbillable
   CHECK(planner_absence_id IS NULL OR (billable IS FALSE AND COALESCE(fakturerbar_tid_min,0)=0));
 END IF;
END $$;
CREATE INDEX IF NOT EXISTS planner_time_employee_case ON tmodule_times(sag_id,medarbejder_id) WHERE status<>'rejected';
CREATE INDEX IF NOT EXISTS planner_live_absence_period ON planner_absences(user_id,starts_at,ends_at) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS planner_reminder_key ON sag_reminders((trigger_config->>'planner_key'))
 WHERE trigger_config ? 'planner_key';
INSERT INTO permissions(code,description,category) VALUES
 ('planner.view_team','Se tilladte kollegers planer','planner'),
 ('planner.allocate','Allokere medarbejdere','planner'),
 ('planner.absence_own','Registrere eget fravær','planner'),
 ('planner.absence_team','Registrere andres fravær','planner'),
 ('planner.absence_reason','Se fraværsårsager','planner') ON CONFLICT DO NOTHING;
INSERT INTO group_permissions(group_id,permission_id)
 SELECT g.id,p.id FROM groups g CROSS JOIN permissions p
 WHERE g.name='Administrators' AND p.category='planner' ON CONFLICT DO NOTHING;
INSERT INTO group_permissions(group_id,permission_id)
 SELECT g.id,p.id FROM groups g CROSS JOIN permissions p
 WHERE (g.name='Managers' AND p.code IN ('planner.view_team','planner.allocate','planner.absence_own','planner.absence_team'))
 OR (g.name='Technicians' AND p.code='planner.absence_own') ON CONFLICT DO NOTHING;