-- Migration: Subscription Plans and Billing System
-- Date: 2025-12-10
-- Purpose: Create tables for subscription plans, packages, and usage tracking

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- Subscription status
CREATE TYPE subscription_status AS ENUM (
  'trial',           -- Free trial period
  'active',          -- Active paid subscription
  'past_due',        -- Payment failed, grace period
  'cancelled',       -- User cancelled
  'expired'          -- Trial or subscription expired
);

-- Billing period type
CREATE TYPE billing_period AS ENUM (
  'trial',           -- Trial period (14 days)
  'monthly',         -- Monthly billing
  'yearly'           -- Yearly billing (future)
);

-- =============================================================================
-- TABLE: subscription_plans
-- Master table for all available plans
-- =============================================================================

CREATE TABLE subscription_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Plan identification
  plan_code TEXT UNIQUE NOT NULL,  -- 'free_trial', 'starter', 'growth', 'scale'
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  is_trial BOOLEAN DEFAULT false,
  is_popular BOOLEAN DEFAULT false,  -- "Most Popular" badge

  -- Pricing (multi-currency: store all currencies)
  price_huf DECIMAL(10,2) NOT NULL DEFAULT 0,
  price_eur DECIMAL(10,2) NOT NULL DEFAULT 0,
  price_usd DECIMAL(10,2) NOT NULL DEFAULT 0,

  -- Overage pricing per minute (multi-currency)
  overage_price_huf DECIMAL(10,2) DEFAULT NULL,  -- NULL means no overage allowed
  overage_price_eur DECIMAL(10,2) DEFAULT NULL,
  overage_price_usd DECIMAL(10,2) DEFAULT NULL,

  -- Limits
  minutes_included INTEGER NOT NULL,  -- Included minutes per period
  trial_days INTEGER DEFAULT NULL,    -- Only for trial plan
  max_products INTEGER DEFAULT NULL,  -- Product limit (NULL = unlimited)

  -- Plan metadata (for display)
  icon_name TEXT DEFAULT NULL,        -- Lucide icon name
  badge_color TEXT DEFAULT NULL,      -- Tailwind color class

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- TABLE: subscription_plan_translations
-- i18n translations for plan names, descriptions, features
-- =============================================================================

CREATE TABLE subscription_plan_translations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID NOT NULL REFERENCES subscription_plans(id) ON DELETE CASCADE,

  -- Language code (en, hu, de)
  language_code TEXT NOT NULL,

  -- Translatable content
  name TEXT NOT NULL,
  description TEXT,
  period_label TEXT,           -- e.g., "/mo" or "/14 days"
  overage_label TEXT,          -- e.g., "160 Ft/min" or "Save 10%!"
  badge_text TEXT,             -- e.g., "Most Popular", "Best Rate"

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(plan_id, language_code)
);

-- =============================================================================
-- TABLE: subscription_plan_features
-- Features list per plan (normalized, with i18n)
-- =============================================================================

CREATE TABLE subscription_plan_features (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID NOT NULL REFERENCES subscription_plans(id) ON DELETE CASCADE,

  -- Feature metadata
  feature_code TEXT NOT NULL,   -- e.g., 'dedicated_number', 'transfer_to_human'
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_highlighted BOOLEAN DEFAULT false,  -- Show as highlighted/premium

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(plan_id, feature_code)
);

-- =============================================================================
-- TABLE: subscription_feature_translations
-- i18n translations for feature labels
-- =============================================================================

CREATE TABLE subscription_feature_translations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  feature_id UUID NOT NULL REFERENCES subscription_plan_features(id) ON DELETE CASCADE,

  language_code TEXT NOT NULL,
  label TEXT NOT NULL,          -- Display text for the feature

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(feature_id, language_code)
);

-- =============================================================================
-- TABLE: additional_packages
-- Minute top-up packages (purchased separately)
-- =============================================================================

CREATE TABLE additional_packages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Package identification
  package_code TEXT UNIQUE NOT NULL,  -- e.g., '50_min', '100_min', '250_min'
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,

  -- Minutes included
  minutes INTEGER NOT NULL,

  -- Pricing (multi-currency)
  price_huf DECIMAL(10,2) NOT NULL,
  price_eur DECIMAL(10,2) NOT NULL,
  price_usd DECIMAL(10,2) NOT NULL,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- TABLE: additional_package_translations
-- i18n translations for package names/descriptions
-- =============================================================================

CREATE TABLE additional_package_translations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  package_id UUID NOT NULL REFERENCES additional_packages(id) ON DELETE CASCADE,

  language_code TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(package_id, language_code)
);

-- =============================================================================
-- TABLE: store_subscriptions
-- Active subscription for each store
-- =============================================================================

CREATE TABLE store_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  plan_id UUID NOT NULL REFERENCES subscription_plans(id),

  -- Subscription status
  status subscription_status NOT NULL DEFAULT 'trial',
  billing_period billing_period NOT NULL DEFAULT 'trial',

  -- Period dates
  period_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  period_end TIMESTAMPTZ NOT NULL,  -- Trial: +14 days, Monthly: +1 month

  -- Usage tracking for current period
  minutes_used DECIMAL(10,2) NOT NULL DEFAULT 0,
  minutes_included INTEGER NOT NULL,  -- Snapshot from plan at subscription time

  -- Auto-purchase settings
  auto_purchase_enabled BOOLEAN DEFAULT false,
  auto_purchase_package_id UUID REFERENCES additional_packages(id),
  auto_purchase_threshold INTEGER DEFAULT 5,  -- Trigger when X minutes remaining

  -- Currency preference (set at first subscription)
  currency TEXT NOT NULL DEFAULT 'HUF',  -- 'HUF', 'EUR', 'USD'

  -- Cancellation tracking
  cancelled_at TIMESTAMPTZ DEFAULT NULL,
  cancellation_reason TEXT DEFAULT NULL,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  -- Only one active subscription per store
  UNIQUE(store_id)
);

-- =============================================================================
-- TABLE: subscription_usage_log
-- Detailed usage tracking (minute consumption)
-- =============================================================================

CREATE TABLE subscription_usage_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subscription_id UUID NOT NULL REFERENCES store_subscriptions(id) ON DELETE CASCADE,
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,

  -- Usage details
  call_log_id UUID REFERENCES call_logs(id),  -- Link to actual call
  minutes_consumed DECIMAL(10,4) NOT NULL,

  -- Source of minutes
  source_type TEXT NOT NULL,  -- 'included', 'overage', 'package'
  source_package_id UUID REFERENCES additional_packages(id),  -- If from package

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- TABLE: package_purchases
-- Record of additional package purchases
-- =============================================================================

CREATE TABLE package_purchases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  subscription_id UUID NOT NULL REFERENCES store_subscriptions(id),
  package_id UUID NOT NULL REFERENCES additional_packages(id),

  -- Purchase details
  minutes_purchased INTEGER NOT NULL,
  minutes_remaining DECIMAL(10,2) NOT NULL,

  -- Price at time of purchase (snapshot)
  currency TEXT NOT NULL,
  price_paid DECIMAL(10,2) NOT NULL,

  -- Auto vs manual
  is_auto_purchase BOOLEAN DEFAULT false,

  -- Expiry (packages expire with billing period)
  expires_at TIMESTAMPTZ NOT NULL,

  -- Timestamps
  purchased_at TIMESTAMPTZ DEFAULT NOW(),

  -- Status
  is_active BOOLEAN DEFAULT true  -- False when fully consumed or expired
);

-- =============================================================================
-- TABLE: billing_history
-- Record of all billing events (for future payment integration)
-- =============================================================================

CREATE TABLE billing_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  subscription_id UUID REFERENCES store_subscriptions(id),

  -- Event type
  event_type TEXT NOT NULL,  -- 'subscription_started', 'subscription_renewed',
                             -- 'package_purchased', 'overage_charged',
                             -- 'subscription_cancelled', 'plan_changed'

  -- Amount details
  currency TEXT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,

  -- Reference to related entities
  plan_id UUID REFERENCES subscription_plans(id),
  package_id UUID REFERENCES additional_packages(id),

  -- Event metadata (JSON for flexibility)
  metadata JSONB DEFAULT '{}',

  -- Description for display
  description TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================================================
-- INDEXES
-- =============================================================================

CREATE INDEX idx_store_subscriptions_store_id ON store_subscriptions(store_id);
CREATE INDEX idx_store_subscriptions_status ON store_subscriptions(status);
CREATE INDEX idx_store_subscriptions_period_end ON store_subscriptions(period_end);
CREATE INDEX idx_subscription_usage_log_subscription ON subscription_usage_log(subscription_id);
CREATE INDEX idx_subscription_usage_log_store ON subscription_usage_log(store_id);
CREATE INDEX idx_package_purchases_store ON package_purchases(store_id);
CREATE INDEX idx_package_purchases_active ON package_purchases(store_id, is_active) WHERE is_active = true;
CREATE INDEX idx_billing_history_store ON billing_history(store_id);
CREATE INDEX idx_plan_translations_plan_lang ON subscription_plan_translations(plan_id, language_code);
CREATE INDEX idx_feature_translations_feature_lang ON subscription_feature_translations(feature_id, language_code);

-- =============================================================================
-- ROW LEVEL SECURITY
-- =============================================================================

ALTER TABLE subscription_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscription_plan_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscription_plan_features ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscription_feature_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE additional_packages ENABLE ROW LEVEL SECURITY;
ALTER TABLE additional_package_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE store_subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscription_usage_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE package_purchases ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing_history ENABLE ROW LEVEL SECURITY;

-- Plans and packages: readable by all authenticated users
CREATE POLICY "Plans are readable by authenticated users"
  ON subscription_plans FOR SELECT TO authenticated USING (is_active = true);

CREATE POLICY "Plan translations are readable by authenticated users"
  ON subscription_plan_translations FOR SELECT TO authenticated USING (true);

CREATE POLICY "Plan features are readable by authenticated users"
  ON subscription_plan_features FOR SELECT TO authenticated USING (true);

CREATE POLICY "Feature translations are readable by authenticated users"
  ON subscription_feature_translations FOR SELECT TO authenticated USING (true);

CREATE POLICY "Packages are readable by authenticated users"
  ON additional_packages FOR SELECT TO authenticated USING (is_active = true);

CREATE POLICY "Package translations are readable by authenticated users"
  ON additional_package_translations FOR SELECT TO authenticated USING (true);

-- Store-specific data: only owner can access
CREATE POLICY "Users can view own store subscriptions"
  ON store_subscriptions FOR SELECT TO authenticated
  USING (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

CREATE POLICY "Users can update own store subscriptions"
  ON store_subscriptions FOR UPDATE TO authenticated
  USING (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()))
  WITH CHECK (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

CREATE POLICY "Users can view own usage logs"
  ON subscription_usage_log FOR SELECT TO authenticated
  USING (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

CREATE POLICY "Users can view own package purchases"
  ON package_purchases FOR SELECT TO authenticated
  USING (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

CREATE POLICY "Users can insert own package purchases"
  ON package_purchases FOR INSERT TO authenticated
  WITH CHECK (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

CREATE POLICY "Users can view own billing history"
  ON billing_history FOR SELECT TO authenticated
  USING (store_id IN (SELECT id FROM stores WHERE user_id = auth.uid()));

-- Service role can manage all
CREATE POLICY "Service role full access to subscriptions"
  ON store_subscriptions FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to usage logs"
  ON subscription_usage_log FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to purchases"
  ON package_purchases FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to billing"
  ON billing_history FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to plans"
  ON subscription_plans FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to plan translations"
  ON subscription_plan_translations FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to plan features"
  ON subscription_plan_features FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to feature translations"
  ON subscription_feature_translations FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to packages"
  ON additional_packages FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access to package translations"
  ON additional_package_translations FOR ALL TO service_role USING (true) WITH CHECK (true);

-- =============================================================================
-- ENABLE REALTIME for store_subscriptions
-- =============================================================================

ALTER PUBLICATION supabase_realtime ADD TABLE store_subscriptions;

-- =============================================================================
-- HELPER FUNCTIONS
-- =============================================================================

-- Function to auto-assign free trial when store is created
CREATE OR REPLACE FUNCTION auto_assign_free_trial()
RETURNS TRIGGER AS $$
DECLARE
  trial_plan_id UUID;
  trial_minutes INTEGER;
  trial_days INTEGER;
BEGIN
  -- Get the free trial plan
  SELECT id, minutes_included, subscription_plans.trial_days
  INTO trial_plan_id, trial_minutes, trial_days
  FROM subscription_plans
  WHERE plan_code = 'free_trial' AND is_active = true
  LIMIT 1;

  IF trial_plan_id IS NOT NULL THEN
    -- Create subscription for the new store
    INSERT INTO store_subscriptions (
      store_id,
      plan_id,
      status,
      billing_period,
      period_start,
      period_end,
      minutes_included,
      currency
    ) VALUES (
      NEW.id,
      trial_plan_id,
      'trial',
      'trial',
      NOW(),
      NOW() + (COALESCE(trial_days, 14) || ' days')::INTERVAL,
      trial_minutes,
      'HUF'  -- Default currency
    );

    -- Log billing event
    INSERT INTO billing_history (
      store_id,
      event_type,
      currency,
      amount,
      plan_id,
      description
    ) VALUES (
      NEW.id,
      'subscription_started',
      'HUF',
      0,
      trial_plan_id,
      'Free trial started'
    );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to auto-assign trial on store creation
CREATE TRIGGER trigger_auto_assign_free_trial
  AFTER INSERT ON stores
  FOR EACH ROW
  EXECUTE FUNCTION auto_assign_free_trial();

-- Function to get current subscription status for a store
CREATE OR REPLACE FUNCTION get_store_subscription_status(p_store_id UUID)
RETURNS TABLE (
  subscription_id UUID,
  plan_code TEXT,
  plan_name TEXT,
  status subscription_status,
  minutes_used DECIMAL,
  minutes_included INTEGER,
  minutes_remaining DECIMAL,
  package_minutes_remaining DECIMAL,
  total_minutes_remaining DECIMAL,
  period_start TIMESTAMPTZ,
  period_end TIMESTAMPTZ,
  is_expired BOOLEAN,
  days_remaining INTEGER
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    ss.id as subscription_id,
    sp.plan_code,
    spt.name as plan_name,
    ss.status,
    ss.minutes_used,
    ss.minutes_included,
    GREATEST(ss.minutes_included - ss.minutes_used, 0)::DECIMAL as minutes_remaining,
    COALESCE(
      (SELECT SUM(pp.minutes_remaining) FROM package_purchases pp
       WHERE pp.subscription_id = ss.id AND pp.is_active = true),
      0
    )::DECIMAL as package_minutes_remaining,
    (GREATEST(ss.minutes_included - ss.minutes_used, 0) + COALESCE(
      (SELECT SUM(pp.minutes_remaining) FROM package_purchases pp
       WHERE pp.subscription_id = ss.id AND pp.is_active = true),
      0
    ))::DECIMAL as total_minutes_remaining,
    ss.period_start,
    ss.period_end,
    ss.period_end < NOW() as is_expired,
    GREATEST(EXTRACT(DAY FROM ss.period_end - NOW())::INTEGER, 0) as days_remaining
  FROM store_subscriptions ss
  JOIN subscription_plans sp ON ss.plan_id = sp.id
  LEFT JOIN subscription_plan_translations spt ON sp.id = spt.plan_id AND spt.language_code = 'en'
  WHERE ss.store_id = p_store_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to update subscription timestamps
CREATE OR REPLACE FUNCTION update_store_subscription_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_store_subscription_timestamp
  BEFORE UPDATE ON store_subscriptions
  FOR EACH ROW
  EXECUTE FUNCTION update_store_subscription_timestamp();

-- =============================================================================
-- SEED DATA: Initial Plans
-- =============================================================================

-- Insert the 4 plans
INSERT INTO subscription_plans (
  plan_code, sort_order, is_active, is_trial, is_popular,
  price_huf, price_eur, price_usd,
  overage_price_huf, overage_price_eur, overage_price_usd,
  minutes_included, trial_days, max_products,
  icon_name, badge_color
) VALUES
  ('free_trial', 1, true, true, false, 0, 0, 0, NULL, NULL, NULL, 10, 14, NULL, 'Zap', NULL),
  ('starter', 2, true, false, false, 8990, 25, 29, 160, 0.45, 0.50, 40, NULL, 2500, 'Star', NULL),
  ('growth', 3, true, false, true, 24900, 70, 79, 150, 0.42, 0.47, 130, NULL, 5000, 'TrendingUp', 'cyan'),
  ('scale', 4, true, false, false, 59900, 170, 189, 140, 0.40, 0.44, 350, NULL, 15000, 'Crown', NULL);

-- =============================================================================
-- SEED DATA: English Translations
-- =============================================================================

INSERT INTO subscription_plan_translations (plan_id, language_code, name, description, period_label, overage_label, badge_text)
SELECT id, 'en',
  CASE plan_code
    WHEN 'free_trial' THEN '14-Day Free Trial (Sandbox)'
    WHEN 'starter' THEN 'Starter'
    WHEN 'growth' THEN 'Growth'
    WHEN 'scale' THEN 'Scale'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN 'Test the AI risk-free. 10 Minutes of AI Talk Time included.'
    WHEN 'starter' THEN 'Small shops, <5 calls/day'
    WHEN 'growth' THEN 'Growing brands, daily calls'
    WHEN 'scale' THEN 'High volume / Seasonal'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN '/ 14 days'
    WHEN 'starter' THEN '/mo'
    WHEN 'growth' THEN '/mo'
    WHEN 'scale' THEN '/mo'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN NULL
    WHEN 'starter' THEN 'Overage: 160 Ft / min'
    WHEN 'growth' THEN 'Overage: 150 Ft / min (Save 10!)'
    WHEN 'scale' THEN 'Overage: 140 Ft / min (Best Rate)'
  END,
  CASE plan_code
    WHEN 'growth' THEN 'MOST POPULAR'
    ELSE NULL
  END
FROM subscription_plans;

-- =============================================================================
-- SEED DATA: Hungarian Translations
-- =============================================================================

INSERT INTO subscription_plan_translations (plan_id, language_code, name, description, period_label, overage_label, badge_text)
SELECT id, 'hu',
  CASE plan_code
    WHEN 'free_trial' THEN '14 napos ingyenes próba (Sandbox)'
    WHEN 'starter' THEN 'Starter'
    WHEN 'growth' THEN 'Growth'
    WHEN 'scale' THEN 'Scale'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN 'Teszteld az AI-t kockázatmentesen. 10 perc AI beszélgetési idő.'
    WHEN 'starter' THEN 'Kis boltok, <5 hívás/nap'
    WHEN 'growth' THEN 'Növekvő márkák, napi hívások'
    WHEN 'scale' THEN 'Nagy forgalom / Szezonális'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN '/ 14 nap'
    WHEN 'starter' THEN '/hó'
    WHEN 'growth' THEN '/hó'
    WHEN 'scale' THEN '/hó'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN NULL
    WHEN 'starter' THEN 'Túlhasználat: 160 Ft / perc'
    WHEN 'growth' THEN 'Túlhasználat: 150 Ft / perc (10 Ft megtakarítás!)'
    WHEN 'scale' THEN 'Túlhasználat: 140 Ft / perc (Legjobb ár)'
  END,
  CASE plan_code
    WHEN 'growth' THEN 'LEGNÉPSZERŰBB'
    ELSE NULL
  END
FROM subscription_plans;

-- =============================================================================
-- SEED DATA: German Translations
-- =============================================================================

INSERT INTO subscription_plan_translations (plan_id, language_code, name, description, period_label, overage_label, badge_text)
SELECT id, 'de',
  CASE plan_code
    WHEN 'free_trial' THEN '14-Tage kostenlose Testversion (Sandbox)'
    WHEN 'starter' THEN 'Starter'
    WHEN 'growth' THEN 'Growth'
    WHEN 'scale' THEN 'Scale'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN 'Testen Sie die KI risikofrei. 10 Minuten KI-Gesprächszeit inklusive.'
    WHEN 'starter' THEN 'Kleine Shops, <5 Anrufe/Tag'
    WHEN 'growth' THEN 'Wachsende Marken, tägliche Anrufe'
    WHEN 'scale' THEN 'Hohes Volumen / Saisonal'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN '/ 14 Tage'
    WHEN 'starter' THEN '/Monat'
    WHEN 'growth' THEN '/Monat'
    WHEN 'scale' THEN '/Monat'
  END,
  CASE plan_code
    WHEN 'free_trial' THEN NULL
    WHEN 'starter' THEN 'Überschreitung: 0,45 € / Min'
    WHEN 'growth' THEN 'Überschreitung: 0,42 € / Min (10% sparen!)'
    WHEN 'scale' THEN 'Überschreitung: 0,40 € / Min (Bester Preis)'
  END,
  CASE plan_code
    WHEN 'growth' THEN 'AM BELIEBTESTEN'
    ELSE NULL
  END
FROM subscription_plans;

-- =============================================================================
-- SEED DATA: Plan Features
-- =============================================================================

-- Free Trial features
INSERT INTO subscription_plan_features (plan_id, feature_code, sort_order, is_highlighted)
SELECT id, 'ai_minutes_10', 1, true
FROM subscription_plans WHERE plan_code = 'free_trial';

-- Starter features
INSERT INTO subscription_plan_features (plan_id, feature_code, sort_order, is_highlighted)
SELECT sp.id, f.feature_code, f.sort_order, f.is_highlighted
FROM subscription_plans sp
CROSS JOIN (VALUES
  ('minutes_40', 1, true),
  ('dedicated_number', 2, false),
  ('transfer_to_human', 3, false),
  ('max_2500_products', 4, false),
  ('availability_24_7', 5, false),
  ('email_summaries', 6, false)
) AS f(feature_code, sort_order, is_highlighted)
WHERE sp.plan_code = 'starter';

-- Growth features
INSERT INTO subscription_plan_features (plan_id, feature_code, sort_order, is_highlighted)
SELECT sp.id, f.feature_code, f.sort_order, f.is_highlighted
FROM subscription_plans sp
CROSS JOIN (VALUES
  ('minutes_130', 1, true),
  ('dedicated_number', 2, false),
  ('transfer_to_human', 3, false),
  ('working_hours', 4, false),
  ('max_5000_products', 5, false),
  ('order_status_lookup', 6, false),
  ('call_analytics', 7, false),
  ('custom_voice', 8, false)
) AS f(feature_code, sort_order, is_highlighted)
WHERE sp.plan_code = 'growth';

-- Scale features
INSERT INTO subscription_plan_features (plan_id, feature_code, sort_order, is_highlighted)
SELECT sp.id, f.feature_code, f.sort_order, f.is_highlighted
FROM subscription_plans sp
CROSS JOIN (VALUES
  ('minutes_350', 1, true),
  ('transfer_to_human', 2, false),
  ('working_hours', 3, false),
  ('max_15000_products', 4, false),
  ('order_status_lookup', 5, false),
  ('call_analytics', 6, false),
  ('priority_support', 7, false),
  ('dedicated_account_manager', 8, false)
) AS f(feature_code, sort_order, is_highlighted)
WHERE sp.plan_code = 'scale';

-- =============================================================================
-- SEED DATA: Feature Translations - English
-- =============================================================================

INSERT INTO subscription_feature_translations (feature_id, language_code, label)
SELECT spf.id, 'en',
  CASE spf.feature_code
    WHEN 'ai_minutes_10' THEN '10 Minutes of AI Talk Time'
    WHEN 'minutes_40' THEN '40 Mins (approx 20 calls)'
    WHEN 'minutes_130' THEN '130 Mins (approx 65 calls)'
    WHEN 'minutes_350' THEN '350 Mins (approx 175 calls)'
    WHEN 'dedicated_number' THEN 'Dedicated Number'
    WHEN 'transfer_to_human' THEN 'Transfer call to human'
    WHEN 'max_2500_products' THEN 'Max. 2500 products'
    WHEN 'max_5000_products' THEN 'Max. 5000 products'
    WHEN 'max_15000_products' THEN 'Max. 15000 products'
    WHEN 'availability_24_7' THEN '24/7 Availability'
    WHEN 'email_summaries' THEN 'Email Summaries'
    WHEN 'working_hours' THEN 'Working hours setup'
    WHEN 'order_status_lookup' THEN 'Order Status Lookup'
    WHEN 'call_analytics' THEN 'Call Analytics Dashboard'
    WHEN 'custom_voice' THEN 'Custom Voice Personality'
    WHEN 'priority_support' THEN 'Priority support'
    WHEN 'dedicated_account_manager' THEN 'Dedicated Account Manager'
    ELSE spf.feature_code
  END
FROM subscription_plan_features spf;

-- =============================================================================
-- SEED DATA: Feature Translations - Hungarian
-- =============================================================================

INSERT INTO subscription_feature_translations (feature_id, language_code, label)
SELECT spf.id, 'hu',
  CASE spf.feature_code
    WHEN 'ai_minutes_10' THEN '10 perc AI beszélgetési idő'
    WHEN 'minutes_40' THEN '40 perc (kb. 20 hívás)'
    WHEN 'minutes_130' THEN '130 perc (kb. 65 hívás)'
    WHEN 'minutes_350' THEN '350 perc (kb. 175 hívás)'
    WHEN 'dedicated_number' THEN 'Dedikált telefonszám'
    WHEN 'transfer_to_human' THEN 'Hívás átirányítása emberhez'
    WHEN 'max_2500_products' THEN 'Max. 2500 termék'
    WHEN 'max_5000_products' THEN 'Max. 5000 termék'
    WHEN 'max_15000_products' THEN 'Max. 15000 termék'
    WHEN 'availability_24_7' THEN '0-24 elérhetőség'
    WHEN 'email_summaries' THEN 'Email összefoglalók'
    WHEN 'working_hours' THEN 'Munkaidő beállítás'
    WHEN 'order_status_lookup' THEN 'Rendelés állapot lekérdezés'
    WHEN 'call_analytics' THEN 'Hívás analitika irányítópult'
    WHEN 'custom_voice' THEN 'Egyedi hang személyiség'
    WHEN 'priority_support' THEN 'Elsőbbségi támogatás'
    WHEN 'dedicated_account_manager' THEN 'Dedikált fiókmenedzser'
    ELSE spf.feature_code
  END
FROM subscription_plan_features spf;

-- =============================================================================
-- SEED DATA: Feature Translations - German
-- =============================================================================

INSERT INTO subscription_feature_translations (feature_id, language_code, label)
SELECT spf.id, 'de',
  CASE spf.feature_code
    WHEN 'ai_minutes_10' THEN '10 Minuten KI-Gesprächszeit'
    WHEN 'minutes_40' THEN '40 Min (ca. 20 Anrufe)'
    WHEN 'minutes_130' THEN '130 Min (ca. 65 Anrufe)'
    WHEN 'minutes_350' THEN '350 Min (ca. 175 Anrufe)'
    WHEN 'dedicated_number' THEN 'Dedizierte Nummer'
    WHEN 'transfer_to_human' THEN 'Weiterleitung an Menschen'
    WHEN 'max_2500_products' THEN 'Max. 2500 Produkte'
    WHEN 'max_5000_products' THEN 'Max. 5000 Produkte'
    WHEN 'max_15000_products' THEN 'Max. 15000 Produkte'
    WHEN 'availability_24_7' THEN '24/7 Verfügbarkeit'
    WHEN 'email_summaries' THEN 'E-Mail-Zusammenfassungen'
    WHEN 'working_hours' THEN 'Arbeitszeiten-Einrichtung'
    WHEN 'order_status_lookup' THEN 'Bestellstatus-Abfrage'
    WHEN 'call_analytics' THEN 'Anruf-Analyse-Dashboard'
    WHEN 'custom_voice' THEN 'Personalisierte Stimme'
    WHEN 'priority_support' THEN 'Prioritäts-Support'
    WHEN 'dedicated_account_manager' THEN 'Dedizierter Account Manager'
    ELSE spf.feature_code
  END
FROM subscription_plan_features spf;

-- =============================================================================
-- SEED DATA: Additional Packages
-- =============================================================================

INSERT INTO additional_packages (package_code, sort_order, minutes, price_huf, price_eur, price_usd)
VALUES
  ('50_min', 1, 50, 7500, 21, 24),
  ('100_min', 2, 100, 14000, 39, 45),
  ('250_min', 3, 250, 32500, 91, 105);

-- Package translations - English
INSERT INTO additional_package_translations (package_id, language_code, name, description)
SELECT id, 'en',
  CASE package_code
    WHEN '50_min' THEN '50 Minutes'
    WHEN '100_min' THEN '100 Minutes'
    WHEN '250_min' THEN '250 Minutes'
  END,
  CASE package_code
    WHEN '50_min' THEN 'Add 50 extra minutes to your account'
    WHEN '100_min' THEN 'Add 100 extra minutes to your account'
    WHEN '250_min' THEN 'Best value - Add 250 extra minutes'
  END
FROM additional_packages;

-- Package translations - Hungarian
INSERT INTO additional_package_translations (package_id, language_code, name, description)
SELECT id, 'hu',
  CASE package_code
    WHEN '50_min' THEN '50 perc'
    WHEN '100_min' THEN '100 perc'
    WHEN '250_min' THEN '250 perc'
  END,
  CASE package_code
    WHEN '50_min' THEN 'Adj hozzá 50 extra percet a fiókodhoz'
    WHEN '100_min' THEN 'Adj hozzá 100 extra percet a fiókodhoz'
    WHEN '250_min' THEN 'Legjobb érték - Adj hozzá 250 extra percet'
  END
FROM additional_packages;

-- Package translations - German
INSERT INTO additional_package_translations (package_id, language_code, name, description)
SELECT id, 'de',
  CASE package_code
    WHEN '50_min' THEN '50 Minuten'
    WHEN '100_min' THEN '100 Minuten'
    WHEN '250_min' THEN '250 Minuten'
  END,
  CASE package_code
    WHEN '50_min' THEN 'Fügen Sie 50 zusätzliche Minuten hinzu'
    WHEN '100_min' THEN 'Fügen Sie 100 zusätzliche Minuten hinzu'
    WHEN '250_min' THEN 'Bester Wert - 250 zusätzliche Minuten'
  END
FROM additional_packages;

-- =============================================================================
-- ASSIGN FREE TRIAL TO EXISTING STORES
-- =============================================================================

-- For existing stores that don't have a subscription yet
INSERT INTO store_subscriptions (
  store_id,
  plan_id,
  status,
  billing_period,
  period_start,
  period_end,
  minutes_included,
  currency
)
SELECT
  s.id,
  sp.id,
  'trial'::subscription_status,
  'trial'::billing_period,
  COALESCE(s.connected_at, s.created_at, NOW()),
  COALESCE(s.connected_at, s.created_at, NOW()) + INTERVAL '14 days',
  sp.minutes_included,
  'HUF'
FROM stores s
CROSS JOIN subscription_plans sp
WHERE sp.plan_code = 'free_trial'
  AND sp.is_active = true
  AND NOT EXISTS (
    SELECT 1 FROM store_subscriptions ss WHERE ss.store_id = s.id
  );

-- =============================================================================
-- Migration Complete Notice
-- =============================================================================

DO $$
BEGIN
  RAISE NOTICE 'Subscription plans migration completed successfully';
  RAISE NOTICE 'Created tables: subscription_plans, store_subscriptions, additional_packages, etc.';
  RAISE NOTICE 'Seeded 4 plans with translations in en, hu, de';
  RAISE NOTICE 'Auto-assign trigger will assign free trial to new stores';
  RAISE NOTICE 'Existing stores have been assigned free trial';
END $$;
