-- 034_pricing_model.sql
-- CRM-managed pricing model and voucher/coupon codes.
--
-- This stores business configuration only. Customer/order content stays in the
-- normal order/payment tables.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS pricing_config (
  config_id TEXT PRIMARY KEY DEFAULT 'default',
  currency TEXT NOT NULL DEFAULT 'GBP',
  digital_base_pence INTEGER NOT NULL DEFAULT 399,
  physical_base_pence INTEGER NOT NULL DEFAULT 999,
  digital_future_cost_pence INTEGER NOT NULL DEFAULT 80,
  physical_future_cost_pence INTEGER NOT NULL DEFAULT 450,
  fixed_income_pence INTEGER NOT NULL DEFAULT 250,
  annual_inflation_rate NUMERIC(8,5) NOT NULL DEFAULT 0.03500,
  long_horizon_risk_rate NUMERIC(8,5) NOT NULL DEFAULT 0.01000,
  platform_fee_bps INTEGER NOT NULL DEFAULT 290,
  payment_fixed_fee_pence INTEGER NOT NULL DEFAULT 20,
  max_delivery_years INTEGER NOT NULL DEFAULT 50,
  rounding_increment_pence INTEGER NOT NULL DEFAULT 100,
  charm_price BOOLEAN NOT NULL DEFAULT TRUE,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by TEXT,
  CONSTRAINT pricing_config_singleton CHECK (config_id = 'default'),
  CONSTRAINT pricing_config_non_negative CHECK (
    digital_base_pence >= 0
    AND physical_base_pence >= 0
    AND digital_future_cost_pence >= 0
    AND physical_future_cost_pence >= 0
    AND fixed_income_pence >= 0
    AND platform_fee_bps >= 0
    AND payment_fixed_fee_pence >= 0
    AND max_delivery_years BETWEEN 1 AND 80
    AND rounding_increment_pence BETWEEN 1 AND 10000
  )
);

INSERT INTO pricing_config (config_id)
VALUES ('default')
ON CONFLICT (config_id) DO NOTHING;

CREATE TABLE IF NOT EXISTS pricing_vouchers (
  voucher_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT NOT NULL UNIQUE,
  discount_type TEXT NOT NULL CHECK (discount_type IN ('percent', 'fixed')),
  discount_value NUMERIC(10,2) NOT NULL,
  applies_to TEXT NOT NULL DEFAULT 'all' CHECK (applies_to IN ('all', 'digital', 'physical')),
  min_order_pence INTEGER NOT NULL DEFAULT 0,
  max_uses INTEGER,
  used_count INTEGER NOT NULL DEFAULT 0,
  valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  valid_until TIMESTAMPTZ,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  archived_at TIMESTAMPTZ,
  archived_by TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by TEXT,
  CONSTRAINT pricing_vouchers_usage_check CHECK (
    used_count >= 0
    AND (max_uses IS NULL OR max_uses >= 0)
    AND min_order_pence >= 0
    AND discount_value >= 0
  )
);

CREATE INDEX IF NOT EXISTS idx_pricing_vouchers_enabled
  ON pricing_vouchers (enabled, valid_until, applies_to);

CREATE INDEX IF NOT EXISTS idx_pricing_vouchers_code
  ON pricing_vouchers (upper(code));

ALTER TABLE pricing_vouchers
  ADD COLUMN IF NOT EXISTS archived_at TIMESTAMPTZ;

ALTER TABLE pricing_vouchers
  ADD COLUMN IF NOT EXISTS archived_by TEXT;

CREATE INDEX IF NOT EXISTS idx_pricing_vouchers_archived
  ON pricing_vouchers (archived_at);

