-- V023 SaaS Commercial Operations
CREATE TABLE IF NOT EXISTS saas_onboarding_applications_v023 (id varchar(36) PRIMARY KEY, company_name varchar(220) NOT NULL, tax_id varchar(80) DEFAULT '', admin_name varchar(180) NOT NULL, admin_email varchar(255) NOT NULL, plan_code varchar(80) NOT NULL DEFAULT 'trial', status varchar(30) NOT NULL DEFAULT 'pending_email', activation_token_hash varchar(128) UNIQUE NOT NULL, activation_expires_at timestamptz NOT NULL, tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE SET NULL, metadata_json jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), activated_at timestamptz);
CREATE INDEX IF NOT EXISTS ix_onboarding_email_v023 ON saas_onboarding_applications_v023(admin_email,status);
CREATE TABLE IF NOT EXISTS billing_customers_v023 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) UNIQUE NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, provider varchar(40) NOT NULL DEFAULT 'manual', external_customer_id varchar(180) DEFAULT '', billing_email varchar(255) DEFAULT '', tax_id varchar(80) DEFAULT '', status varchar(30) NOT NULL DEFAULT 'active', provider_metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS billing_invoices_v023 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, subscription_id varchar(36) REFERENCES saas_subscriptions_v020(id) ON DELETE SET NULL, provider varchar(40) NOT NULL DEFAULT 'manual', external_invoice_id varchar(180) DEFAULT '', status varchar(30) NOT NULL DEFAULT 'draft', currency varchar(10) NOT NULL DEFAULT 'BRL', subtotal_cents integer NOT NULL DEFAULT 0, tax_cents integer NOT NULL DEFAULT 0, total_cents integer NOT NULL DEFAULT 0, due_at timestamptz, paid_at timestamptz, line_items jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS ix_invoice_tenant_status_v023 ON billing_invoices_v023(tenant_id,status,due_at);
CREATE TABLE IF NOT EXISTS billing_webhook_events_v023 (id varchar(36) PRIMARY KEY, provider varchar(40) NOT NULL, external_event_id varchar(180) UNIQUE NOT NULL, event_type varchar(120) NOT NULL, signature_valid boolean NOT NULL DEFAULT false, status varchar(30) NOT NULL DEFAULT 'received', payload jsonb NOT NULL DEFAULT '{}'::jsonb, error text NOT NULL DEFAULT '', received_at timestamptz NOT NULL DEFAULT now(), processed_at timestamptz);
CREATE TABLE IF NOT EXISTS tenant_rate_limit_policies_v023 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, scope varchar(100) NOT NULL DEFAULT 'api', requests_per_minute integer NOT NULL DEFAULT 600 CHECK(requests_per_minute > 0), burst integer NOT NULL DEFAULT 100 CHECK(burst >= 0), enabled boolean NOT NULL DEFAULT true, updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE(tenant_id,scope));
CREATE TABLE IF NOT EXISTS usage_reconciliation_runs_v023 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE SET NULL, status varchar(30) NOT NULL DEFAULT 'running', expired_reservations integer NOT NULL DEFAULT 0, released_quantity integer NOT NULL DEFAULT 0, duplicate_meter_events integer NOT NULL DEFAULT 0, discrepancies jsonb NOT NULL DEFAULT '[]'::jsonb, started_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz);
ALTER TABLE billing_customers_v023 ENABLE ROW LEVEL SECURITY; ALTER TABLE billing_customers_v023 FORCE ROW LEVEL SECURITY;
ALTER TABLE billing_invoices_v023 ENABLE ROW LEVEL SECURITY; ALTER TABLE billing_invoices_v023 FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS billing_customers_tenant_v023 ON billing_customers_v023; CREATE POLICY billing_customers_tenant_v023 ON billing_customers_v023 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));
DROP POLICY IF EXISTS billing_invoices_tenant_v023 ON billing_invoices_v023; CREATE POLICY billing_invoices_tenant_v023 ON billing_invoices_v023 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));