-- sql/wayleave_05_file_uploads_integration.sql
-- Wayleave module — Phase 0 step 5 of 5.
-- Integrate wayleave into the shared public.file_uploads pattern.
--   1) add an agreement_id column to public.file_uploads so 'wayleave' rows can reference their parent
--   2) create wayleave.vw_file_uploads, matching the per-schema view pattern used by
--      accounts.vw_file_uploads, projects.vw_file_uploads, stocklists.vw_file_uploads.
-- Prereq: sql/wayleave_01_core.sql (for wayleave.agreements FK — not a hard FK here, matches the
-- soft-reference style used by other entity_id columns on public.file_uploads).
-- Run: psql -U postgres -d netplanner -f sql/wayleave_05_file_uploads_integration.sql

BEGIN;

-- 1) Add agreement_id column to the shared table (idempotent).
ALTER TABLE public.file_uploads
    ADD COLUMN IF NOT EXISTS agreement_id integer;

CREATE INDEX IF NOT EXISTS idx_file_uploads_agreement_id
    ON public.file_uploads (agreement_id)
    WHERE entity = 'wayleave' AND file_is_deleted = false;

-- 2) Per-schema read view — filters entity='wayleave' and joins users for display.
--    Mirrors accounts.vw_file_uploads (see geolynx_ddl.sql).
DROP VIEW IF EXISTS wayleave.vw_file_uploads;
CREATE VIEW wayleave.vw_file_uploads AS
 SELECT fu.id,
        fu.file_id,
        to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
        fu.file_upload_datetime AS file_upload_datetime_raw,
        u.username,
        fu.file_upload_user,
        fu.file_name,
        fu.file_type,
        fu.agreement_id,
        fu.file_category,
        fu.file_description,
        fu.file_size,
        fu.file_size_raw,
        fu.file_location
   FROM (public.file_uploads fu
     LEFT JOIN users.users u ON ((fu.file_upload_user = u.id)))
  WHERE ((fu.entity)::text = 'wayleave'::text)
    AND fu.file_is_deleted = false
  ORDER BY fu.file_upload_datetime DESC;

ALTER VIEW wayleave.vw_file_uploads OWNER TO postgres;

COMMIT;

-- Verify:
--   \d+ public.file_uploads
-- Should show an agreement_id integer column.
--
--   \dv wayleave.*
-- Should list vw_file_uploads.
--
--   SELECT column_name FROM information_schema.columns
--    WHERE table_schema='wayleave' AND table_name='vw_file_uploads' ORDER BY ordinal_position;
-- Should include agreement_id and the rest of the display columns listed above.
