-- Licensing & Usage Metrics Tables
-- 029_licensing_tables.sql

-- Licenses table
CREATE TABLE IF NOT EXISTS licenses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID, -- Organization ID (can reference organizations table if exists)
  deployment_type VARCHAR(20) NOT NULL DEFAULT 'on_premises', -- 'cloud' or 'on_premises'
  plan_name VARCHAR(100) NOT NULL, -- 'Enterprise', 'Professional', 'Basic'
  max_users INT NOT NULL DEFAULT 10,
  max_sources INT NOT NULL DEFAULT 5,
  max_storage_gb INT NOT NULL DEFAULT 100,
  valid_from TIMESTAMP NOT NULL DEFAULT NOW(),
  valid_until TIMESTAMP NOT NULL,
  is_active BOOLEAN DEFAULT true,
  auto_renewal BOOLEAN DEFAULT false,
  created_date TIMESTAMP DEFAULT NOW(),
  updated_date TIMESTAMP DEFAULT NOW()
);

-- Usage Metrics table
CREATE TABLE IF NOT EXISTS usage_metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID,
  metric_type VARCHAR(50) NOT NULL, -- 'users', 'sources', 'storage'
  current_value NUMERIC NOT NULL DEFAULT 0,
  max_value NUMERIC NOT NULL DEFAULT 0,
  recorded_at TIMESTAMP DEFAULT NOW(),
  timestamp TIMESTAMP DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_licenses_org_id ON licenses(org_id);
CREATE INDEX IF NOT EXISTS idx_licenses_is_active ON licenses(is_active);
CREATE INDEX IF NOT EXISTS idx_usage_metrics_org_id ON usage_metrics(org_id);
CREATE INDEX IF NOT EXISTS idx_usage_metrics_type ON usage_metrics(metric_type);
CREATE INDEX IF NOT EXISTS idx_usage_metrics_timestamp ON usage_metrics(timestamp DESC);

-- Insert default license (if needed)
INSERT INTO licenses (org_id, deployment_type, plan_name, max_users, max_sources, max_storage_gb, valid_until, auto_renewal)
VALUES (
  NULL, -- Default org
  'on_premises',
  'Enterprise',
  50,
  10,
  500,
  NOW() + INTERVAL '1 year',
  true
)
ON CONFLICT DO NOTHING;

COMMENT ON TABLE licenses IS 'License information for organizations';
COMMENT ON TABLE usage_metrics IS 'Usage tracking for users, sources, and storage';

