-- Home page pricing (plans + feature lines). Run once on existing databases.
-- mysql -u USER -p DATABASE < sql/migrate_pricing.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS pricing_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  card_title VARCHAR(255) NOT NULL DEFAULT 'Solar Service Price',
  price_display VARCHAR(120) NOT NULL,
  plan_name VARCHAR(255) NOT NULL,
  cta_url VARCHAR(500) NOT NULL DEFAULT 'contact.php',
  cta_label VARCHAR(120) NOT NULL DEFAULT 'Choose Packages',
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  cta_yellow TINYINT(1) NOT NULL DEFAULT 0,
  cta_in_wrapper TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pricing_features (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id INT UNSIGNED NOT NULL,
  label TEXT NOT NULL,
  icon_style ENUM('check', 'mark') NOT NULL DEFAULT 'check',
  sort_order INT NOT NULL DEFAULT 0,
  KEY idx_plan (plan_id),
  KEY idx_sort (plan_id, sort_order),
  CONSTRAINT fk_pricing_features_plan
    FOREIGN KEY (plan_id) REFERENCES pricing_plans (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO site_settings (setting_key, setting_value) VALUES
('home_pricing_subtitle', 'Pricing Plan'),
('home_pricing_title', 'Our Pricing Plan For you')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO pricing_plans (card_title, price_display, plan_name, cta_url, cta_label, is_featured, cta_yellow, cta_in_wrapper, sort_order, is_active)
SELECT s.card_title, s.price_display, s.plan_name, s.cta_url, s.cta_label, s.is_featured, s.cta_yellow, s.cta_in_wrapper, s.sort_order, s.is_active
FROM (
  SELECT 'Solar Service Price' AS card_title, '$80' AS price_display, 'Repairing Plan' AS plan_name, 'contact.php' AS cta_url, 'Choose Packages' AS cta_label, 0 AS is_featured, 0 AS cta_yellow, 0 AS cta_in_wrapper, 1 AS sort_order, 1 AS is_active
  UNION ALL SELECT 'Solar Service Price', '$110', 'Emergency Plan', 'contact.php', 'Choose Packages', 1, 1, 1, 2, 1
  UNION ALL SELECT 'Solar Service Price', '$180', 'Replacement Plan', 'contact.php', 'Choose Packages', 0, 0, 1, 3, 1
) s
WHERE (SELECT COUNT(*) FROM pricing_plans) = 0;

-- Features for 1st plan (by sort order) when that plan has no rows
INSERT INTO pricing_features (plan_id, label, icon_style, sort_order)
SELECT p.id, x.label, x.icon_style, x.n
FROM (
  SELECT 1 AS n, 'Maintained Equipment' AS label, 'check' AS icon_style UNION ALL
  SELECT 2, '10% Discount on Repairing', 'check' UNION ALL
  SELECT 3, 'Inspection 2 Times Monthly', 'check' UNION ALL
  SELECT 4, 'Unlimited Call In Free', 'check' UNION ALL
  SELECT 5, 'No Charges for checking', 'mark' UNION ALL
  SELECT 6, 'Offer New Coupons', 'mark'
) x
INNER JOIN (
  SELECT id FROM pricing_plans ORDER BY sort_order ASC, id ASC LIMIT 1
) p ON 1=1
WHERE (SELECT COUNT(*) FROM pricing_features WHERE plan_id = p.id) = 0;

INSERT INTO pricing_features (plan_id, label, icon_style, sort_order)
SELECT p.id, x.label, x.icon_style, x.n
FROM (
  SELECT 1 AS n, 'Maintained Equipment' AS label, 'check' AS icon_style UNION ALL
  SELECT 2, '10% Discount on Repairing', 'check' UNION ALL
  SELECT 3, 'Inspection 2 Times Monthly', 'check' UNION ALL
  SELECT 4, 'Unlimited Call In Free', 'check' UNION ALL
  SELECT 5, 'No Charges for checking', 'mark' UNION ALL
  SELECT 6, 'Offer New Coupons', 'mark'
) x
INNER JOIN (
  SELECT id FROM pricing_plans ORDER BY sort_order ASC, id ASC LIMIT 1 OFFSET 1
) p ON 1=1
WHERE (SELECT COUNT(*) FROM pricing_features WHERE plan_id = p.id) = 0;

INSERT INTO pricing_features (plan_id, label, icon_style, sort_order)
SELECT p.id, x.label, x.icon_style, x.n
FROM (
  SELECT 1 AS n, 'Maintained Equipment' AS label, 'check' AS icon_style UNION ALL
  SELECT 2, '10% Discount on Repairing', 'check' UNION ALL
  SELECT 3, 'Inspection 2 Times Monthly', 'check' UNION ALL
  SELECT 4, 'Unlimited Call In Free', 'check' UNION ALL
  SELECT 5, 'No Charges for checking', 'mark' UNION ALL
  SELECT 6, 'Offer New Coupons', 'mark'
) x
INNER JOIN (
  SELECT id FROM pricing_plans ORDER BY sort_order ASC, id ASC LIMIT 1 OFFSET 2
) p ON 1=1
WHERE (SELECT COUNT(*) FROM pricing_features WHERE plan_id = p.id) = 0;
