-- V020 Enterprise SaaS Multi-Tenant Foundation
CREATE TABLE IF NOT EXISTS tenants_v020 (id varchar(36) PRIMARY KEY, slug varchar(100) UNIQUE NOT NULL, legal_name varchar(220) NOT NULL, trade_name varchar(220) NOT NULL DEFAULT '', tax_id varchar(80) NOT NULL DEFAULT '', status varchar(30) NOT NULL DEFAULT 'provisioning', isolation_mode varchar(30) NOT NULL DEFAULT 'shared_schema', region varchar(60) NOT NULL DEFAULT 'sa-east-1', default_locale varchar(20) NOT NULL DEFAULT 'pt-BR', default_timezone varchar(80) NOT NULL DEFAULT 'America/Sao_Paulo', data_residency varchar(60) NOT NULL DEFAULT 'BR', settings_json jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), activated_at timestamptz NULL);
CREATE INDEX IF NOT EXISTS ix_tenants_v020_status ON tenants_v020(status);
CREATE TABLE IF NOT EXISTS tenant_memberships_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, user_id varchar(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE, tenant_role varchar(40) NOT NULL DEFAULT 'member', status varchar(30) NOT NULL DEFAULT 'active', permissions jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT uq_tenant_user_v020 UNIQUE(tenant_id,user_id));
CREATE TABLE IF NOT EXISTS saas_plans_v020 (id varchar(36) PRIMARY KEY, code varchar(80) UNIQUE NOT NULL, name varchar(180) NOT NULL, currency varchar(10) NOT NULL DEFAULT 'BRL', monthly_price_cents integer NOT NULL DEFAULT 0, annual_price_cents integer NOT NULL DEFAULT 0, limits_json jsonb NOT NULL DEFAULT '{}'::jsonb, enabled boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS saas_subscriptions_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, plan_id varchar(36) NOT NULL REFERENCES saas_plans_v020(id), status varchar(30) NOT NULL DEFAULT 'trialing', billing_cycle varchar(20) NOT NULL DEFAULT 'monthly', seats integer NOT NULL DEFAULT 5, trial_ends_at timestamptz NULL, current_period_start timestamptz NULL, current_period_end timestamptz NULL, external_customer_id varchar(180) NOT NULL DEFAULT '', external_subscription_id varchar(180) NOT NULL DEFAULT '', created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS saas_modules_v020 (id varchar(36) PRIMARY KEY, code varchar(80) UNIQUE NOT NULL, name varchar(180) NOT NULL, description text NOT NULL DEFAULT '', metered boolean NOT NULL DEFAULT false, enabled boolean NOT NULL DEFAULT true);
CREATE TABLE IF NOT EXISTS tenant_entitlements_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, module_id varchar(36) NOT NULL REFERENCES saas_modules_v020(id), enabled boolean NOT NULL DEFAULT true, limits_json jsonb NOT NULL DEFAULT '{}'::jsonb, activated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT uq_tenant_module_v020 UNIQUE(tenant_id,module_id));
CREATE TABLE IF NOT EXISTS tenant_branding_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) UNIQUE NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, logo_url varchar(1000) NOT NULL DEFAULT '', favicon_url varchar(1000) NOT NULL DEFAULT '', primary_color varchar(20) NOT NULL DEFAULT '#16324F', secondary_color varchar(20) NOT NULL DEFAULT '#2E7D32', custom_domain varchar(255) UNIQUE NOT NULL DEFAULT '', product_name varchar(180) NOT NULL DEFAULT 'Enterprise Data Platform', support_email varchar(255) NOT NULL DEFAULT '', updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS tenant_usage_meters_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, metric_code varchar(100) NOT NULL, quantity integer NOT NULL DEFAULT 0, unit varchar(30) NOT NULL DEFAULT 'unit', dimensions jsonb NOT NULL DEFAULT '{}'::jsonb, occurred_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS ix_usage_tenant_metric_v020 ON tenant_usage_meters_v020(tenant_id,metric_code,occurred_at);
CREATE TABLE IF NOT EXISTS tenant_provisioning_runs_v020 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NULL REFERENCES tenants_v020(id) ON DELETE SET NULL, requested_by varchar(36) NOT NULL REFERENCES users(id), status varchar(30) NOT NULL DEFAULT 'running', steps_json jsonb NOT NULL DEFAULT '[]'::jsonb, error text NOT NULL DEFAULT '', created_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz NULL);