-- Sankofa Phoenix Database Schema

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'USER' CHECK (role IN ('ADMIN', 'USER', 'VIEWER')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Sites table
CREATE TABLE IF NOT EXISTS sites (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  region VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE')),
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Resources table
CREATE TABLE IF NOT EXISTS resources (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL CHECK (type IN ('VM', 'CONTAINER', 'STORAGE', 'NETWORK')),
  status VARCHAR(50) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'PROVISIONING', 'RUNNING', 'STOPPED', 'ERROR', 'DELETING')),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- API Keys table for developer API access
CREATE TABLE IF NOT EXISTS api_keys (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  key_prefix VARCHAR(20) NOT NULL,
  key_hash VARCHAR(255) NOT NULL UNIQUE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
  permissions JSONB DEFAULT '["read", "write"]'::jsonb,
  last_used_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE,
  revoked BOOLEAN NOT NULL DEFAULT false,
  revoked_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_resources_site_id ON resources(site_id);
CREATE INDEX IF NOT EXISTS idx_resources_type ON resources(type);
CREATE INDEX IF NOT EXISTS idx_resources_status ON resources(status);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_tenant_id ON api_keys(tenant_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_revoked ON api_keys(revoked) WHERE revoked = false;

-- Update timestamp trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Triggers
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_sites_updated_at BEFORE UPDATE ON sites
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_resources_updated_at BEFORE UPDATE ON resources
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_api_keys_updated_at BEFORE UPDATE ON api_keys
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Resource Inventory table for unified resource tracking across providers
CREATE TABLE IF NOT EXISTS resource_inventory (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  resource_type VARCHAR(100) NOT NULL,
  provider VARCHAR(50) NOT NULL CHECK (provider IN ('PROXMOX', 'KUBERNETES', 'CLOUDFLARE', 'CEPH', 'MINIO', 'AWS', 'AZURE', 'GCP')),
  provider_id VARCHAR(255) NOT NULL,
  provider_resource_id VARCHAR(255),
  name VARCHAR(255) NOT NULL,
  region VARCHAR(255),
  site_id UUID REFERENCES sites(id) ON DELETE SET NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  tags JSONB DEFAULT '[]'::jsonb,
  discovered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  last_synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(provider, provider_id)
);

-- Indexes for resource inventory
CREATE INDEX IF NOT EXISTS idx_resource_inventory_provider ON resource_inventory(provider);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_resource_type ON resource_inventory(resource_type);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_region ON resource_inventory(region);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_site_id ON resource_inventory(site_id);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_provider_id ON resource_inventory(provider, provider_id);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_tags ON resource_inventory USING GIN(tags);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_metadata ON resource_inventory USING GIN(metadata);

-- Resource relationships table for graph queries
CREATE TABLE IF NOT EXISTS resource_relationships (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  source_resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  target_resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  relationship_type VARCHAR(100) NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(source_resource_id, target_resource_id, relationship_type)
);

-- Indexes for resource relationships
CREATE INDEX IF NOT EXISTS idx_resource_relationships_source ON resource_relationships(source_resource_id);
CREATE INDEX IF NOT EXISTS idx_resource_relationships_target ON resource_relationships(target_resource_id);
CREATE INDEX IF NOT EXISTS idx_resource_relationships_type ON resource_relationships(relationship_type);

-- Trigger for resource inventory updated_at
CREATE TRIGGER update_resource_inventory_updated_at BEFORE UPDATE ON resource_inventory
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Policies table for policy management
CREATE TABLE IF NOT EXISTS policies (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  policy_type VARCHAR(100) NOT NULL CHECK (policy_type IN ('TAGGING', 'COMPLIANCE', 'SECURITY', 'COST_OPTIMIZATION')),
  enabled BOOLEAN NOT NULL DEFAULT true,
  severity VARCHAR(50) NOT NULL DEFAULT 'MEDIUM' CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
  rule JSONB NOT NULL,
  scope JSONB DEFAULT '{}'::jsonb,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Policy evaluations table
CREATE TABLE IF NOT EXISTS policy_evaluations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  policy_id UUID NOT NULL REFERENCES policies(id) ON DELETE CASCADE,
  resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  status VARCHAR(50) NOT NULL CHECK (status IN ('COMPLIANT', 'NON_COMPLIANT', 'ERROR')),
  findings JSONB DEFAULT '[]'::jsonb,
  evaluated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(policy_id, resource_id)
);

-- Policy violations table
CREATE TABLE IF NOT EXISTS policy_violations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  policy_id UUID NOT NULL REFERENCES policies(id) ON DELETE CASCADE,
  resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  severity VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  remediation TEXT,
  status VARCHAR(50) NOT NULL DEFAULT 'OPEN' CHECK (status IN ('OPEN', 'ACKNOWLEDGED', 'RESOLVED', 'SUPPRESSED')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  resolved_at TIMESTAMP WITH TIME ZONE,
  resolved_by UUID REFERENCES users(id)
);

-- Indexes for policies
CREATE INDEX IF NOT EXISTS idx_policies_type ON policies(policy_type);
CREATE INDEX IF NOT EXISTS idx_policies_enabled ON policies(enabled);
CREATE INDEX IF NOT EXISTS idx_policy_evaluations_policy ON policy_evaluations(policy_id);
CREATE INDEX IF NOT EXISTS idx_policy_evaluations_resource ON policy_evaluations(resource_id);
CREATE INDEX IF NOT EXISTS idx_policy_evaluations_status ON policy_evaluations(status);
CREATE INDEX IF NOT EXISTS idx_policy_violations_policy ON policy_violations(policy_id);
CREATE INDEX IF NOT EXISTS idx_policy_violations_resource ON policy_violations(resource_id);
CREATE INDEX IF NOT EXISTS idx_policy_violations_status ON policy_violations(status);
CREATE INDEX IF NOT EXISTS idx_policy_violations_severity ON policy_violations(severity);

-- Trigger for policies updated_at
CREATE TRIGGER update_policies_updated_at BEFORE UPDATE ON policies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ML Models table
CREATE TABLE IF NOT EXISTS ml_models (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  version VARCHAR(100) NOT NULL,
  framework VARCHAR(100) NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  artifact_path VARCHAR(500),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(name, version)
);

-- Model versions table
CREATE TABLE IF NOT EXISTS model_versions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  model_id UUID NOT NULL REFERENCES ml_models(id) ON DELETE CASCADE,
  version VARCHAR(100) NOT NULL,
  artifact_path VARCHAR(500),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(model_id, version)
);

-- Model lineage table
CREATE TABLE IF NOT EXISTS model_lineage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  model_id UUID NOT NULL REFERENCES ml_models(id) ON DELETE CASCADE,
  training_job_id VARCHAR(255),
  parent_model_id UUID REFERENCES ml_models(id),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for ML models
CREATE INDEX IF NOT EXISTS idx_ml_models_name ON ml_models(name);
CREATE INDEX IF NOT EXISTS idx_model_versions_model ON model_versions(model_id);
CREATE INDEX IF NOT EXISTS idx_model_lineage_model ON model_lineage(model_id);

-- Storage accounts table
CREATE TABLE IF NOT EXISTS storage_accounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL UNIQUE,
  provider VARCHAR(50) NOT NULL CHECK (provider IN ('MINIO', 'CEPH', 'S3')),
  endpoint VARCHAR(500),
  access_key VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Storage containers table
CREATE TABLE IF NOT EXISTS storage_containers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID NOT NULL REFERENCES storage_accounts(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL CHECK (type IN ('BUCKET', 'CONTAINER', 'VOLUME')),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(account_id, name)
);

-- Indexes for storage
CREATE INDEX IF NOT EXISTS idx_storage_containers_account ON storage_containers(account_id);

-- Metrics table for time-series data
CREATE TABLE IF NOT EXISTS metrics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  metric_type VARCHAR(100) NOT NULL,
  value NUMERIC NOT NULL,
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
  labels JSONB DEFAULT '{}'::jsonb,
  UNIQUE(resource_id, metric_type, timestamp)
);

-- Indexes for metrics
CREATE INDEX IF NOT EXISTS idx_metrics_resource_id ON metrics(resource_id);
CREATE INDEX IF NOT EXISTS idx_metrics_metric_type ON metrics(metric_type);
CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON metrics(timestamp);
CREATE INDEX IF NOT EXISTS idx_metrics_resource_type_time ON metrics(resource_id, metric_type, timestamp);

-- Anomalies table for detected anomalies
CREATE TABLE IF NOT EXISTS anomalies (
  id VARCHAR(255) PRIMARY KEY,
  resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  metric_type VARCHAR(100) NOT NULL,
  severity VARCHAR(50) NOT NULL CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
  anomaly_type VARCHAR(50) NOT NULL CHECK (anomaly_type IN ('SPIKE', 'DROP', 'PATTERN', 'THRESHOLD')),
  value NUMERIC NOT NULL,
  expected_value NUMERIC,
  deviation NUMERIC NOT NULL,
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
  description TEXT NOT NULL,
  recommendation TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for anomalies
CREATE INDEX IF NOT EXISTS idx_anomalies_resource_id ON anomalies(resource_id);
CREATE INDEX IF NOT EXISTS idx_anomalies_metric_type ON anomalies(metric_type);
CREATE INDEX IF NOT EXISTS idx_anomalies_severity ON anomalies(severity);
CREATE INDEX IF NOT EXISTS idx_anomalies_timestamp ON anomalies(timestamp);
CREATE INDEX IF NOT EXISTS idx_anomalies_resource_metric ON anomalies(resource_id, metric_type);

-- Trigger for anomalies updated_at
CREATE TRIGGER update_anomalies_updated_at BEFORE UPDATE ON anomalies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Predictions table for predictive analytics
CREATE TABLE IF NOT EXISTS predictions (
  id VARCHAR(255) PRIMARY KEY,
  resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
  metric_type VARCHAR(100) NOT NULL,
  prediction_type VARCHAR(50) NOT NULL CHECK (prediction_type IN ('USAGE', 'COST', 'CAPACITY', 'FAILURE')),
  current_value NUMERIC NOT NULL,
  predicted_value NUMERIC NOT NULL,
  confidence INTEGER NOT NULL CHECK (confidence >= 0 AND confidence <= 100),
  timeframe VARCHAR(10) NOT NULL CHECK (timeframe IN ('1H', '6H', '24H', '7D', '30D')),
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
  trend VARCHAR(50) NOT NULL CHECK (trend IN ('INCREASING', 'DECREASING', 'STABLE')),
  recommendation TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for predictions
CREATE INDEX IF NOT EXISTS idx_predictions_resource_id ON predictions(resource_id);
CREATE INDEX IF NOT EXISTS idx_predictions_metric_type ON predictions(metric_type);
CREATE INDEX IF NOT EXISTS idx_predictions_prediction_type ON predictions(prediction_type);
CREATE INDEX IF NOT EXISTS idx_predictions_timestamp ON predictions(timestamp);
CREATE INDEX IF NOT EXISTS idx_predictions_resource_metric ON predictions(resource_id, metric_type);

-- Trigger for predictions updated_at
CREATE TRIGGER update_predictions_updated_at BEFORE UPDATE ON predictions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TENANTS AND MULTI-TENANCY (Sovereign, Superior to Azure)
-- ============================================================================

-- Tenants table - more flexible than Azure
CREATE TABLE IF NOT EXISTS tenants (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) UNIQUE NOT NULL,
  domain VARCHAR(255) UNIQUE,
  billing_account_id VARCHAR(255) UNIQUE NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'PENDING_ACTIVATION' 
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED', 'PENDING_ACTIVATION')),
  tier VARCHAR(50) NOT NULL DEFAULT 'STANDARD' 
    CHECK (tier IN ('FREE', 'STANDARD', 'ENTERPRISE', 'SOVEREIGN')),
  metadata JSONB DEFAULT '{}'::jsonb,
  quota_limits JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Tenant users junction table with fine-grained permissions (more flexible than Azure RBAC)
CREATE TABLE IF NOT EXISTS tenant_users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(50) NOT NULL DEFAULT 'TENANT_USER' 
    CHECK (role IN ('TENANT_OWNER', 'TENANT_ADMIN', 'TENANT_USER', 'TENANT_VIEWER', 'TENANT_BILLING_ADMIN')),
  permissions JSONB DEFAULT '{}'::jsonb,
  external_id VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(tenant_id, user_id)
);

-- Add tenant_id to existing tables
ALTER TABLE resources 
  ADD COLUMN IF NOT EXISTS tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL;

ALTER TABLE sites 
  ADD COLUMN IF NOT EXISTS tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL;

ALTER TABLE resource_inventory 
  ADD COLUMN IF NOT EXISTS tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL;

-- Indexes for tenant isolation
CREATE INDEX IF NOT EXISTS idx_resources_tenant_id ON resources(tenant_id);
CREATE INDEX IF NOT EXISTS idx_sites_tenant_id ON sites(tenant_id);
CREATE INDEX IF NOT EXISTS idx_resource_inventory_tenant_id ON resource_inventory(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_users_tenant_id ON tenant_users(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_users_user_id ON tenant_users(user_id);
CREATE INDEX IF NOT EXISTS idx_tenants_domain ON tenants(domain) WHERE domain IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_tenants_billing_account_id ON tenants(billing_account_id);

-- Triggers for tenant tables
CREATE TRIGGER update_tenant_users_updated_at BEFORE UPDATE ON tenant_users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_tenants_updated_at BEFORE UPDATE ON tenants
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- BILLING AND COST TRACKING (Superior to Azure Cost Management)
-- ============================================================================

-- Billing accounts - one per tenant with multiple payment methods
CREATE TABLE IF NOT EXISTS billing_accounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL UNIQUE REFERENCES tenants(id) ON DELETE CASCADE,
  account_name VARCHAR(255) NOT NULL,
  payment_methods JSONB DEFAULT '[]'::jsonb,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  billing_address JSONB,
  tax_id VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Usage records with per-second granularity (vs Azure's hourly)
CREATE TABLE IF NOT EXISTS usage_records (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  resource_id UUID REFERENCES resource_inventory(id) ON DELETE SET NULL,
  resource_type VARCHAR(100) NOT NULL,
  metric_type VARCHAR(100) NOT NULL,
  quantity NUMERIC NOT NULL,
  unit VARCHAR(50) NOT NULL,
  cost NUMERIC NOT NULL DEFAULT 0,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
  labels JSONB DEFAULT '{}'::jsonb,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Cost allocations - more flexible than Azure tags
CREATE TABLE IF NOT EXISTS cost_allocations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  usage_record_id UUID REFERENCES usage_records(id) ON DELETE CASCADE,
  allocation_key VARCHAR(255) NOT NULL,
  allocation_value VARCHAR(255) NOT NULL,
  percentage NUMERIC NOT NULL CHECK (percentage >= 0 AND percentage <= 100),
  cost NUMERIC NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Invoices with line-item details
CREATE TABLE IF NOT EXISTS invoices (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  invoice_number VARCHAR(255) UNIQUE NOT NULL,
  billing_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
  billing_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  subtotal NUMERIC NOT NULL,
  tax NUMERIC NOT NULL DEFAULT 0,
  total NUMERIC NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  status VARCHAR(50) NOT NULL DEFAULT 'DRAFT' 
    CHECK (status IN ('DRAFT', 'PENDING', 'PAID', 'OVERDUE', 'CANCELLED')),
  due_date TIMESTAMP WITH TIME ZONE,
  paid_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Invoice line items
CREATE TABLE IF NOT EXISTS invoice_line_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  description TEXT NOT NULL,
  quantity NUMERIC NOT NULL,
  unit_price NUMERIC NOT NULL,
  total NUMERIC NOT NULL,
  resource_id UUID REFERENCES resource_inventory(id) ON DELETE SET NULL,
  usage_record_ids UUID[],
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Payments with multiple payment methods
CREATE TABLE IF NOT EXISTS payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL,
  amount NUMERIC NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  payment_method VARCHAR(50) NOT NULL,
  payment_method_details JSONB,
  status VARCHAR(50) NOT NULL DEFAULT 'PENDING' 
    CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED')),
  transaction_id VARCHAR(255),
  processed_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Billing alerts - more granular than Azure
CREATE TABLE IF NOT EXISTS billing_alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  alert_type VARCHAR(50) NOT NULL 
    CHECK (alert_type IN ('BUDGET', 'THRESHOLD', 'ANOMALY', 'FORECAST')),
  threshold NUMERIC,
  budget_id UUID,
  condition JSONB NOT NULL,
  notification_channels JSONB DEFAULT '[]'::jsonb,
  enabled BOOLEAN NOT NULL DEFAULT true,
  last_triggered_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Budgets
CREATE TABLE IF NOT EXISTS budgets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  amount NUMERIC NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  period VARCHAR(50) NOT NULL 
    CHECK (period IN ('DAILY', 'WEEKLY', 'MONTHLY', 'QUARTERLY', 'YEARLY')),
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE,
  alert_thresholds JSONB DEFAULT '[]'::jsonb,
  filters JSONB DEFAULT '{}'::jsonb,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Reserved capacity
CREATE TABLE IF NOT EXISTS reserved_capacity (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  resource_type VARCHAR(100) NOT NULL,
  quantity NUMERIC NOT NULL,
  unit VARCHAR(50) NOT NULL,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE NOT NULL,
  upfront_cost NUMERIC,
  monthly_cost NUMERIC NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE' 
    CHECK (status IN ('ACTIVE', 'EXPIRED', 'CANCELLED')),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Discounts - custom discount rules per tenant
CREATE TABLE IF NOT EXISTS discounts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  discount_type VARCHAR(50) NOT NULL 
    CHECK (discount_type IN ('PERCENTAGE', 'FIXED_AMOUNT', 'VOLUME', 'TIER')),
  value NUMERIC NOT NULL,
  currency VARCHAR(10),
  conditions JSONB DEFAULT '{}'::jsonb,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE,
  enabled BOOLEAN NOT NULL DEFAULT true,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for billing tables
CREATE INDEX IF NOT EXISTS idx_billing_accounts_tenant_id ON billing_accounts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_tenant_id ON usage_records(tenant_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_timestamp ON usage_records(timestamp);
CREATE INDEX IF NOT EXISTS idx_usage_records_resource_id ON usage_records(resource_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_tenant_timestamp ON usage_records(tenant_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_cost_allocations_tenant_id ON cost_allocations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_cost_allocations_usage_record_id ON cost_allocations(usage_record_id);
CREATE INDEX IF NOT EXISTS idx_invoices_tenant_id ON invoices(tenant_id);
CREATE INDEX IF NOT EXISTS idx_invoices_status ON invoices(status);
CREATE INDEX IF NOT EXISTS idx_invoices_billing_period ON invoices(billing_period_start, billing_period_end);
CREATE INDEX IF NOT EXISTS idx_invoice_line_items_invoice_id ON invoice_line_items(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payments_tenant_id ON payments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payments_invoice_id ON payments(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_billing_alerts_tenant_id ON billing_alerts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_billing_alerts_enabled ON billing_alerts(enabled) WHERE enabled = true;
CREATE INDEX IF NOT EXISTS idx_budgets_tenant_id ON budgets(tenant_id);
CREATE INDEX IF NOT EXISTS idx_reserved_capacity_tenant_id ON reserved_capacity(tenant_id);
CREATE INDEX IF NOT EXISTS idx_reserved_capacity_status ON reserved_capacity(status);
CREATE INDEX IF NOT EXISTS idx_discounts_tenant_id ON discounts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_discounts_enabled ON discounts(enabled) WHERE enabled = true;

-- Triggers for billing tables
CREATE TRIGGER update_billing_accounts_updated_at BEFORE UPDATE ON billing_accounts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_billing_alerts_updated_at BEFORE UPDATE ON billing_alerts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_budgets_updated_at BEFORE UPDATE ON budgets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_reserved_capacity_updated_at BEFORE UPDATE ON reserved_capacity
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_discounts_updated_at BEFORE UPDATE ON discounts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- TEST ENVIRONMENTS (Developer Sandboxes)
-- ============================================================================

-- Test environments table for developer sandboxes
CREATE TABLE IF NOT EXISTS test_environments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
  region VARCHAR(100) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'STOPPED' 
    CHECK (status IN ('RUNNING', 'STOPPED', 'PROVISIONING', 'ERROR', 'DELETING')),
  resources JSONB DEFAULT '{}'::jsonb,
  expires_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for test environments
CREATE INDEX IF NOT EXISTS idx_test_environments_user_id ON test_environments(user_id);
CREATE INDEX IF NOT EXISTS idx_test_environments_tenant_id ON test_environments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_test_environments_status ON test_environments(status);
CREATE INDEX IF NOT EXISTS idx_test_environments_expires_at ON test_environments(expires_at) WHERE expires_at IS NOT NULL;

-- Trigger for test environments updated_at
CREATE TRIGGER update_test_environments_updated_at BEFORE UPDATE ON test_environments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

