-- Indes.Net Billing: perbaikan Point Reward untuk hosting yang belum menjalankan migrasi V15/V16
-- Backup database sebelum menjalankan. Pilih database indes_billing terlebih dahulu.

CREATE TABLE IF NOT EXISTS point_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id INT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NULL,
 points INT NOT NULL,
 type ENUM('earned','redeemed','adjustment') NOT NULL,
 description VARCHAR(255) NOT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_points_invoice (invoice_id),
 INDEX idx_points_customer (customer_id)
) ENGINE=InnoDB;

DELIMITER $$
DROP PROCEDURE IF EXISTS fix_indes_point_schema$$
CREATE PROCEDURE fix_indes_point_schema()
BEGIN
  DECLARE col_exists INT DEFAULT 0;
  DECLARE idx_exists INT DEFAULT 0;
  DECLARE type_exists INT DEFAULT 0;

  SELECT COUNT(*) INTO col_exists FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND COLUMN_NAME='invoice_id';
  IF col_exists=0 THEN
    ALTER TABLE point_transactions ADD COLUMN invoice_id BIGINT UNSIGNED NULL AFTER customer_id;
  END IF;

  SELECT COUNT(*) INTO col_exists FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND COLUMN_NAME='created_by';
  IF col_exists=0 THEN
    ALTER TABLE point_transactions ADD COLUMN created_by INT UNSIGNED NULL;
  END IF;

  SELECT COUNT(*) INTO col_exists FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND COLUMN_NAME='created_at';
  IF col_exists=0 THEN
    ALTER TABLE point_transactions ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
  END IF;

  SELECT COUNT(*) INTO col_exists FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND COLUMN_NAME='type';
  IF col_exists=0 THEN
    ALTER TABLE point_transactions ADD COLUMN type ENUM('earned','redeemed','adjustment') NOT NULL DEFAULT 'adjustment';
  END IF;

  SELECT COUNT(*) INTO idx_exists FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND INDEX_NAME='uq_points_invoice';
  IF idx_exists=0 THEN
    -- MySQL mengizinkan banyak NULL pada UNIQUE, sehingga penyesuaian/redeem manual tetap dapat dicatat.
    ALTER TABLE point_transactions ADD UNIQUE KEY uq_points_invoice (invoice_id);
  END IF;

  SELECT COUNT(*) INTO idx_exists FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='point_transactions' AND INDEX_NAME='idx_points_customer';
  IF idx_exists=0 THEN
    ALTER TABLE point_transactions ADD INDEX idx_points_customer (customer_id);
  END IF;
END$$
CALL fix_indes_point_schema()$$
DROP PROCEDURE fix_indes_point_schema$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS point_rewards (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 points INT UNSIGNED NOT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings(setting_key,setting_value)
VALUES ('points_per_on_time_payment','10')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

-- Isi katalog hadiah standar jika katalog masih kosong.
INSERT INTO point_rewards(name,points)
SELECT s.name,s.points FROM (
 SELECT 'Rinso 1 kg' name,50 points UNION ALL
 SELECT 'Minyak goreng 2 liter',120 UNION ALL
 SELECT 'Beras 2,5 kg',150 UNION ALL
 SELECT 'Indomie 1 dus',200 UNION ALL
 SELECT 'Beras 5 kg',300 UNION ALL
 SELECT 'Magic com',350 UNION ALL
 SELECT 'Kompor gas',650
) s WHERE NOT EXISTS (SELECT 1 FROM point_rewards);
