-- =====================================================================
-- Tenant isolation: Row Level Security (defence in depth)
--
-- The application always filters by tenant_id in code. In addition every
-- table that has a tenant_id column gets an RLS policy bound to the
-- transaction-local setting app.tenant_id. The API sets it with
--   SET LOCAL app.tenant_id = '<uuid>'
-- at the start of each tenant-scoped transaction (see DbService).
--
-- Platform-level code (super admin, cross-tenant jobs) sets
--   SET LOCAL app.bypass_rls = 'on'
--
-- RLS only applies to roles that are not superuser / table owner. The
-- application must therefore connect as the dedicated role wms_app
-- (created below, NOBYPASSRLS). Migrations run as the owner role.
-- =====================================================================

-- 1. Application role --------------------------------------------------
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'wms_app') THEN
    -- Password must be changed per environment: ALTER ROLE wms_app PASSWORD '...';
    CREATE ROLE wms_app LOGIN NOBYPASSRLS PASSWORD 'wms_app_dev_password';
  END IF;
END
$$;

GRANT USAGE ON SCHEMA public TO wms_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO wms_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO wms_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO wms_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO wms_app;

-- 2. Helper: current tenant from session/transaction setting --------------
CREATE OR REPLACE FUNCTION app_current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid
$$;

CREATE OR REPLACE FUNCTION app_bypass_rls() RETURNS boolean
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(current_setting('app.bypass_rls', true), 'off') = 'on'
$$;

-- 3. Apply RLS to every table with a tenant_id column ----------------------
-- Re-runnable: call SELECT app_apply_rls(); after adding tables.
CREATE OR REPLACE FUNCTION app_apply_rls() RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
  r record;
BEGIN
  FOR r IN
    SELECT c.table_name
    FROM information_schema.columns c
    JOIN information_schema.tables t ON t.table_name = c.table_name AND t.table_schema = c.table_schema
    WHERE c.table_schema = 'public' AND c.column_name = 'tenant_id' AND t.table_type = 'BASE TABLE'
  LOOP
    EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', r.table_name);
    EXECUTE format('ALTER TABLE public.%I FORCE ROW LEVEL SECURITY', r.table_name);
    EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON public.%I', r.table_name);
    -- audit_logs.tenant_id is nullable (platform actions): allow NULL rows only in bypass mode
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON public.%I USING (app_bypass_rls() OR tenant_id = app_current_tenant()) WITH CHECK (app_bypass_rls() OR tenant_id = app_current_tenant())',
      r.table_name
    );
  END LOOP;
END
$$;

SELECT app_apply_rls();

-- 4. Tables without tenant_id that are tenant-linked through a parent -------
-- user_roles, user_warehouses, storer_warehouses are join tables keyed by
-- parent ids that are themselves RLS-protected. Enable RLS via EXISTS checks.
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_roles FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON user_roles;
CREATE POLICY tenant_isolation ON user_roles
  USING (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = user_roles.user_id AND u.tenant_id = app_current_tenant()))
  WITH CHECK (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = user_roles.user_id AND u.tenant_id = app_current_tenant()));

ALTER TABLE user_warehouses ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_warehouses FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON user_warehouses;
CREATE POLICY tenant_isolation ON user_warehouses
  USING (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = user_warehouses.user_id AND u.tenant_id = app_current_tenant()))
  WITH CHECK (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = user_warehouses.user_id AND u.tenant_id = app_current_tenant()));

ALTER TABLE storer_warehouses ENABLE ROW LEVEL SECURITY;
ALTER TABLE storer_warehouses FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON storer_warehouses;
CREATE POLICY tenant_isolation ON storer_warehouses
  USING (app_bypass_rls() OR EXISTS (SELECT 1 FROM storers s WHERE s.id = storer_warehouses.storer_id AND s.tenant_id = app_current_tenant()))
  WITH CHECK (app_bypass_rls() OR EXISTS (SELECT 1 FROM storers s WHERE s.id = storer_warehouses.storer_id AND s.tenant_id = app_current_tenant()));

ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON sessions;
CREATE POLICY tenant_isolation ON sessions
  USING (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = sessions.user_id AND u.tenant_id = app_current_tenant()))
  WITH CHECK (app_bypass_rls() OR EXISTS (SELECT 1 FROM users u WHERE u.id = sessions.user_id AND u.tenant_id = app_current_tenant()));

-- tenants itself: a tenant can read only its own row
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON tenants;
CREATE POLICY tenant_isolation ON tenants
  USING (app_bypass_rls() OR id = app_current_tenant())
  WITH CHECK (app_bypass_rls() OR id = app_current_tenant());

-- plans and platform_users are platform-only: readable by tenants (plans) / bypass only (platform_users)
ALTER TABLE platform_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE platform_users FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS platform_only ON platform_users;
CREATE POLICY platform_only ON platform_users USING (app_bypass_rls()) WITH CHECK (app_bypass_rls());

-- 5. Integrity guards --------------------------------------------------------
-- The inventory ledger is append-only.
CREATE OR REPLACE FUNCTION app_forbid_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'inventory_movements is append-only (%, %)', TG_OP, TG_TABLE_NAME;
END
$$;
DROP TRIGGER IF EXISTS inventory_movements_append_only ON inventory_movements;
CREATE TRIGGER inventory_movements_append_only
  BEFORE UPDATE OR DELETE ON inventory_movements
  FOR EACH ROW EXECUTE FUNCTION app_forbid_change();

-- Balances can never go negative or have allocations above on-hand.
ALTER TABLE inventory_balances DROP CONSTRAINT IF EXISTS inventory_balances_non_negative;
ALTER TABLE inventory_balances ADD CONSTRAINT inventory_balances_non_negative
  CHECK (qty_on_hand >= 0 AND qty_allocated >= 0 AND qty_allocated <= qty_on_hand);

-- 6. Useful partial indexes --------------------------------------------------
CREATE INDEX IF NOT EXISTS inv_bal_available_idx
  ON inventory_balances (tenant_id, warehouse_id, storer_id, sku_id, first_received_at, expiry_date)
  WHERE qty_on_hand > qty_allocated;

CREATE INDEX IF NOT EXISTS tasks_open_queue_idx
  ON tasks (tenant_id, warehouse_id, type, priority, sequence, created_at)
  WHERE status IN ('PENDING', 'ASSIGNED');

CREATE INDEX IF NOT EXISTS outbox_events_pending_idx ON outbox_events (created_at) WHERE status = 'PENDING';