-- ============================================================================
-- IsoCore Production PostgreSQL Multi-Tenant Architecture Blueprint
-- Strategy A: Shared DB + Row-Level Security (RLS) via Session Context
-- ============================================================================

-- 1. Tenants Directory
CREATE TABLE tenants (
  id VARCHAR(64) PRIMARY KEY,
  slug VARCHAR(64) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  tier VARCHAR(32) NOT NULL DEFAULT 'Enterprise',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2. Isolated Ledger Transactions Table
CREATE TABLE transactions (
  id VARCHAR(64) PRIMARY KEY DEFAULT 'tx_' || replace(gen_random_uuid()::text, '-', ''),
  tenant_id VARCHAR(64) NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
  reference_id VARCHAR(128) NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  amount NUMERIC(14, 2) NOT NULL CHECK (amount >= 0),
  platform_fee NUMERIC(14, 2) NOT NULL,
  net_settled NUMERIC(14, 2) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'PROCESSING',
  risk_score INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 3. Composite Tenant B-Tree Indexes
CREATE INDEX idx_transactions_tenant_date ON transactions (tenant_id, created_at DESC);

-- 4. Enable & Force Row-Level Security
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions FORCE ROW LEVEL SECURITY;

-- 5. Zero-Leakage Restrictive Security Policy
CREATE POLICY tenant_isolation_policy ON transactions
  AS RESTRICTIVE
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true));

-- 6. Connection Pool Hook (Run before any tenant query):
-- SET LOCAL app.current_tenant_id = 'ten_acme_892';