-- 006_app_modules.sql
-- Dynamic Navigation Phase 1 (docs/2026-07-14-Dynamic-Navigation.md)
-- 1. Module enablement table — read per-request by the app, written manually
--    for now, by the Hub sync job in future.
-- 2. Seed new permission modules (opportunities, wayleave, strategy) as
--    'write' for all existing roles so current behaviour is preserved.

CREATE TABLE IF NOT EXISTS public.app_modules (
    module_key   varchar(50) PRIMARY KEY,
    display_name varchar(100) NOT NULL,
    enabled      boolean NOT NULL DEFAULT true,
    updated_at   timestamptz NOT NULL DEFAULT now()
);

INSERT INTO public.app_modules (module_key, display_name, enabled) VALUES
    ('projects',      'Projects',            true),
    ('accounts',      'Accounts',            true),
    ('stocklists',    'Stocklists',          true),
    ('opportunities', 'Opportunity Builder', true),
    ('wayleave',      'Wayleave',            true),
    ('strategy',      'Strategy & Insights', true),
    ('companalysis',  'Competitor Analysis', true)
ON CONFLICT (module_key) DO NOTHING;

-- New permission modules: no row = no access, so without this seed every
-- existing role would silently lose access to these areas on deploy.
INSERT INTO users.role_permissions (role_id, module, permission_level)
SELECT r.role_id, m.module, 'write'
FROM users.roles r
CROSS JOIN (VALUES ('opportunities'), ('wayleave'), ('strategy')) AS m(module)
ON CONFLICT (role_id, module) DO NOTHING;
