-- 3.2 Database Schema Güncellemeleri
CREATE TABLE IF NOT EXISTS generated_routes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  trip_id UUID REFERENCES trips(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id),
  preferences JSONB NOT NULL,
  route_data JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS api_usage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  api_type TEXT NOT NULL,
  endpoint TEXT NOT NULL,
  request_count INTEGER DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_generated_routes_trip_id ON generated_routes(trip_id);
CREATE INDEX IF NOT EXISTS idx_generated_routes_user_id ON generated_routes(user_id);
CREATE INDEX IF NOT EXISTS idx_api_usage_user_id ON api_usage(user_id);
CREATE INDEX IF NOT EXISTS idx_api_usage_created_at ON api_usage(created_at);

-- 7.1 Güvenlik Güncellemesi: Provider Kayıt Fonksiyonu
CREATE OR REPLACE FUNCTION register_provider(
  p_user_id UUID,
  p_business_name TEXT,
  p_business_description TEXT,
  p_destinations TEXT[],
  p_activity_categories TEXT[]
)
RETURNS JSON
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  v_profile_exists BOOLEAN;
  v_service_id UUID;
  v_wallet_id UUID;
BEGIN
  IF p_user_id IS DISTINCT FROM auth.uid() THEN
    RAISE EXCEPTION 'Unauthorized: cannot register provider on behalf of another user';
  END IF;
 
  SELECT EXISTS(SELECT 1 FROM profiles WHERE id = p_user_id) INTO v_profile_exists;
  IF NOT v_profile_exists THEN
    RAISE EXCEPTION 'Profil bulunamadı';
  END IF;
 
  UPDATE profiles
    SET role = 'provider', updated_at = NOW()
  WHERE id = p_user_id;
 
  INSERT INTO provider_services (
    provider_id, business_name, business_description,
    destinations, activity_categories, created_at, updated_at
  ) VALUES (
    p_user_id, p_business_name, p_business_description,
    p_destinations, p_activity_categories, NOW(), NOW()
  )
  ON CONFLICT (provider_id) DO UPDATE SET
    business_name        = EXCLUDED.business_name,
    business_description = EXCLUDED.business_description,
    destinations         = EXCLUDED.destinations,
    activity_categories  = EXCLUDED.activity_categories,
    updated_at           = NOW()
  RETURNING id INTO v_service_id;
 
  INSERT INTO provider_wallets (provider_id, credit_balance, created_at, updated_at)
  VALUES (p_user_id, 0, NOW(), NOW())
  ON CONFLICT (provider_id) DO NOTHING
  RETURNING id INTO v_wallet_id;
 
  RETURN json_build_object(
    'success', true,
    'provider_id', p_user_id,
    'service_id', v_service_id
  );
END;
$$;
 
GRANT EXECUTE ON FUNCTION register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]) TO authenticated;
GRANT EXECUTE ON FUNCTION register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[]) TO service_role;

-- 11.1 Yeni Güvenlik Güncellemesi: Lead PII Maskeleme
CREATE OR REPLACE VIEW leads_for_providers AS
SELECT
  l.id,
  l.trip_id,
  l.destination,
  l.country,
  l.start_date,
  l.end_date,
  l.number_of_travelers,
  l.interests,
  l.planned_activities,
  l.base_price,
  l.calculated_price,
  l.final_price,
  l.override_price,
  l.status,
  l.trigger_source,
  l.created_at,
  CASE
    WHEN EXISTS (
      SELECT 1 FROM lead_purchases lp
      WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
    ) THEN l.email
    ELSE '***@***.***'
  END AS email,
  CASE
    WHEN EXISTS (
      SELECT 1 FROM lead_purchases lp
      WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
    ) THEN l.whatsapp
    ELSE '+90 *** *** ****'
  END AS whatsapp,
  EXISTS (
    SELECT 1 FROM lead_purchases lp
    WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
  ) AS is_purchased
FROM leads l
WHERE l.consent_given = true;

GRANT SELECT ON leads_for_providers TO authenticated;

-- 12.1 Yeni Güvenlik Güncellemesi: Audit Logs ve Rate Limit Logs Insert Policy
DROP POLICY IF EXISTS "Service role can insert audit logs" ON audit_logs;
DROP POLICY IF EXISTS "Only service_role inserts audit logs" ON audit_logs;
CREATE POLICY "Only service_role inserts audit logs"
  ON audit_logs
  FOR INSERT
  WITH CHECK (auth.role() = 'service_role');

DROP POLICY IF EXISTS "Service role can insert rate limit logs" ON rate_limit_logs;
DROP POLICY IF EXISTS "Only service_role inserts rate limit logs" ON rate_limit_logs;
CREATE POLICY "Only service_role inserts rate limit logs"
  ON rate_limit_logs
  FOR INSERT
  WITH CHECK (auth.role() = 'service_role');

-- 13.1 Yeni Güvenlik Güncellemesi: System Settings Keys
INSERT INTO site_settings (key, value) VALUES
  ('maintenance_mode',         'false'),
  ('registration_open',        'true'),
  ('email_notifications',      'true'),
  ('daily_reports',            'true'),
  ('hero_image',               NULL),
  ('session_timeout_minutes',  '30')
ON CONFLICT (key) DO NOTHING;

DROP POLICY IF EXISTS "Admins can insert site settings" ON site_settings;
CREATE POLICY "Admins can insert site settings"
  ON site_settings FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
  );

-- 14.1 Yeni Güvenlik Güncellemesi: Admin Set User Role Fonksiyonu
CREATE OR REPLACE FUNCTION admin_set_user_role(
  p_target_user_id UUID,
  p_new_role TEXT
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_old_role TEXT;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized: admin access required';
  END IF;
 
  IF p_new_role NOT IN ('user', 'provider', 'admin') THEN
    RAISE EXCEPTION 'Invalid role: must be user, provider, or admin';
  END IF;
 
  IF p_target_user_id = auth.uid() AND p_new_role != 'admin' THEN
    RAISE EXCEPTION 'Cannot demote yourself from admin';
  END IF;
 
  SELECT role INTO v_old_role FROM profiles WHERE id = p_target_user_id;
 
  UPDATE profiles
    SET role = p_new_role, updated_at = NOW()
  WHERE id = p_target_user_id;
 
  RETURN json_build_object(
    'success', true,
    'old_role', v_old_role,
    'new_role', p_new_role
  );
END;
$$;
 
GRANT EXECUTE ON FUNCTION admin_set_user_role(UUID, TEXT) TO authenticated;

-- 15.3 Database Schema Güncellemeleri (Adjust Provider Credits)
CREATE OR REPLACE FUNCTION admin_adjust_provider_credits(
  p_provider_id UUID,
  p_amount INTEGER,
  p_description TEXT
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_new_balance INTEGER;
  v_transaction_type TEXT;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized: admin access required';
  END IF;

  IF p_amount > 0 THEN
    v_transaction_type := 'admin_credit';
  ELSE
    v_transaction_type := 'admin_debit';
  END IF;

  UPDATE provider_wallets
    SET credit_balance = credit_balance + p_amount,
        updated_at = NOW()
  WHERE provider_id = p_provider_id
  RETURNING credit_balance INTO v_new_balance;

  INSERT INTO provider_wallet_transactions (
    provider_id,
    transaction_type,
    amount,
    description,
    balance_after,
    created_at
  ) VALUES (
    p_provider_id,
    v_transaction_type,
    p_amount,
    p_description,
    v_new_balance,
    NOW()
  );

  RETURN json_build_object(
    'success', true,
    'new_balance', v_new_balance
  );
END;
$$;

GRANT EXECUTE ON FUNCTION admin_adjust_provider_credits(UUID, INTEGER, TEXT) TO authenticated;

-- 16.2 Database Schema Güncellemeleri (Pricing Rules)
CREATE TABLE IF NOT EXISTS pricing_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rule_name TEXT NOT NULL UNIQUE,
  rule_value NUMERIC NOT NULL,
  description TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
INSERT INTO pricing_rules (rule_name, rule_value, description) VALUES
  ('base_price',            20,   'Default base price in credits'),
  ('ai_multiplier',         1.75, 'AI-recommended lead price multiplier'),
  ('ai_min_price',          35,   'Minimum price for AI leads in credits'),
  ('balloon_multiplier',    2.0,  'Hot air balloon activity multiplier'),
  ('atv_multiplier',        1.5,  'ATV/horse riding activity multiplier'),
  ('guided_tour_multiplier',1.4,  'Guided tour activity multiplier')
ON CONFLICT (rule_name) DO NOTHING;
 
ALTER TABLE pricing_rules ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone can read pricing rules" ON pricing_rules;
CREATE POLICY "Anyone can read pricing rules"
  ON pricing_rules FOR SELECT TO public USING (true);

DROP POLICY IF EXISTS "Admins can update pricing rules" ON pricing_rules;
CREATE POLICY "Admins can update pricing rules"
  ON pricing_rules FOR UPDATE TO authenticated
  USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));
 
CREATE OR REPLACE FUNCTION calculate_lead_price(
  p_base_price INTEGER,
  p_planned_activities JSONB,
  p_trigger_source TEXT DEFAULT NULL
)
RETURNS INTEGER AS $$
DECLARE
  v_multiplier NUMERIC := 1.0;
  v_activity JSONB;
  v_activity_type TEXT;
  v_activity_name TEXT;
  v_final_price INTEGER;
  v_base            NUMERIC;
  v_ai_mult         NUMERIC;
  v_ai_min          NUMERIC;
  v_balloon_mult    NUMERIC;
  v_atv_mult       NUMERIC;
  v_tour_mult       NUMERIC;
BEGIN
  SELECT rule_value INTO v_base           FROM pricing_rules WHERE rule_name='base_price';
  SELECT rule_value INTO v_ai_mult        FROM pricing_rules WHERE rule_name='ai_multiplier';
  SELECT rule_value INTO v_ai_min         FROM pricing_rules WHERE rule_name='ai_min_price';
  SELECT rule_value INTO v_balloon_mult   FROM pricing_rules WHERE rule_name='balloon_multiplier';
  SELECT rule_value INTO v_atv_mult       FROM pricing_rules WHERE rule_name='atv_multiplier';
  SELECT rule_value INTO v_tour_mult      FROM pricing_rules WHERE rule_name='guided_tour_multiplier';
 
  v_base       := COALESCE(v_base, 20);
  v_ai_mult    := COALESCE(v_ai_mult, 1.75);
  v_ai_min     := COALESCE(v_ai_min, 35);
  v_balloon_mult := COALESCE(v_balloon_mult, 2.0);
  v_atv_mult   := COALESCE(v_atv_mult, 1.5);
  v_tour_mult  := COALESCE(v_tour_mult, 1.4);
 
  IF p_base_price IS NULL OR p_base_price <= 0 THEN
    p_base_price := v_base::INTEGER;
  END IF;
 
  IF p_trigger_source = 'ai_route_recommendation' THEN
    v_multiplier := v_multiplier * v_ai_mult;
  END IF;
 
  IF p_planned_activities IS NOT NULL AND jsonb_array_length(p_planned_activities) > 0 THEN
    FOR v_activity IN SELECT * FROM jsonb_array_elements(p_planned_activities) LOOP
      v_activity_type := LOWER(COALESCE(v_activity->>'type', ''));
      v_activity_name := LOWER(COALESCE(v_activity->>'name', ''));
      IF v_activity_type LIKE '%balloon%' OR v_activity_name LIKE '%balloon%'
         OR v_activity_type LIKE '%hot air%' OR v_activity_name LIKE '%hot air%' THEN
        v_multiplier := v_multiplier * v_balloon_mult;
      END IF;
      IF v_activity_type LIKE '%atv%' OR v_activity_name LIKE '%atv%'
         OR v_activity_type LIKE '%horse%' OR v_activity_name LIKE '%horse%'
         OR v_activity_type LIKE '%riding%' OR v_activity_name LIKE '%riding%' THEN
        v_multiplier := v_multiplier * v_atv_mult;
      END IF;
      IF v_activity_type LIKE '%guided%' OR v_activity_name LIKE '%guided%'
         OR v_activity_type LIKE '%tour%' OR v_activity_name LIKE '%tour%' THEN
        v_multiplier := v_multiplier * v_tour_mult;
      END IF;
    END LOOP;
  END IF;
 
  v_final_price := ROUND(p_base_price * v_multiplier);
 
  IF p_trigger_source = 'ai_route_recommendation'
     AND v_final_price < v_ai_min::INTEGER THEN
    v_final_price := v_ai_min::INTEGER;
  END IF;
 
  RETURN v_final_price;
END;
$$ LANGUAGE plpgsql STABLE;

-- 18.1 Yeni Güvenlik Güncellemesi: Admin Rate Limit Access
ALTER TABLE rate_limit_rules ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read rate limit rules" ON rate_limit_rules;
DROP POLICY IF EXISTS "Admins can update rate limit rules" ON rate_limit_rules;
DROP POLICY IF EXISTS "Public can read rate limit rules" ON rate_limit_rules;

CREATE POLICY "Public can read rate limit rules"
  ON rate_limit_rules FOR SELECT
  TO public USING (true);

CREATE POLICY "Admins can update rate limit rules"
  ON rate_limit_rules FOR UPDATE
  TO authenticated
  USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));

ALTER TABLE rate_limit_logs ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read rate limit logs" ON rate_limit_logs;

CREATE POLICY "Admins can read rate limit logs"
  ON rate_limit_logs FOR SELECT
  TO authenticated
  USING (EXISTS (SELECT 1 FROM profiles WHERE id=auth.uid() AND role='admin'));

CREATE OR REPLACE FUNCTION admin_clear_rate_limit(p_user_id UUID)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_deleted INTEGER;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  DELETE FROM rate_limit_logs WHERE user_id = p_user_id;
  GET DIAGNOSTICS v_deleted = ROW_COUNT;
  RETURN json_build_object('success', true, 'deleted', v_deleted);
END;
$$;

GRANT EXECUTE ON FUNCTION admin_clear_rate_limit(UUID) TO authenticated;
