-- 0001: companies, users, memberships, RLS foundation.
-- The app role is the ONLY role the API/tests use for normal operations.
-- It has LOGIN, no BYPASSRLS, owns nothing.

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app') THEN
    BEGIN
      CREATE ROLE app LOGIN PASSWORD 'app';
    EXCEPTION WHEN duplicate_object THEN
      NULL;  -- a concurrent migration created it first
    END;
  END IF;
END $$;

-- Single source of truth for "which tenant is this session".
-- Returns NULL when the setting is absent or empty, so every RLS
-- policy fails closed (zero rows), never open.
CREATE FUNCTION current_company_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.company_id', true), '')::uuid
$$;

CREATE TABLE companies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE user_companies (
  user_id uuid NOT NULL REFERENCES users (id),
  company_id uuid NOT NULL REFERENCES companies (id),
  role text NOT NULL
    CHECK (role IN ('owner', 'bookkeeper', 'reviewer', 'readonly')),
  PRIMARY KEY (user_id, company_id)
);

CREATE INDEX user_companies_by_company ON user_companies (company_id);

ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE companies FORCE ROW LEVEL SECURITY;
CREATE POLICY companies_isolation ON companies
  USING (id = current_company_id());

ALTER TABLE user_companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_companies FORCE ROW LEVEL SECURITY;
-- NOTE: this policy constrains company_id only. RLS cannot validate user_id
-- here (users is global), so membership writes must be validated by the
-- application layer; the app role's INSERT grant is for that layer's use.
CREATE POLICY user_companies_isolation ON user_companies
  USING (company_id = current_company_id());

-- users is global (a login can belong to several companies); not tenant-scoped.
GRANT SELECT ON companies, users, user_companies TO app;
GRANT INSERT, UPDATE ON user_companies TO app;
