-- ALTER TABLE untuk fitur lock dengan toleransi 8 menit
-- Jalankan di phpMyAdmin > SQL tab database infe8543_metasby

-- 1. Tambah kolom is_locked di companies untuk query lebih cepat
ALTER TABLE `companies` 
ADD COLUMN `is_locked` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1=terkunci karena expired, 0=aktif' AFTER `status`,
ADD COLUMN `locked_at` datetime DEFAULT NULL COMMENT 'Waktu kapan terkunci',
ADD COLUMN `locked_reason` varchar(50) DEFAULT NULL COMMENT 'TRIAL_EXPIRED / SUBSCRIPTION_EXPIRED';

-- 2. Tambah index biar cron & cek_trial cepat (penting kalau company sudah 1000+)
ALTER TABLE `companies` 
ADD INDEX `idx_subscription_status` (`subscription_status`),
ADD INDEX `idx_trial_until` (`trial_until`),
ADD INDEX `idx_subscription_until` (`subscription_until`),
ADD INDEX `idx_is_locked` (`is_locked`);

-- 3. Tambah kolom di saas_subscriptions untuk tracking toleransi
ALTER TABLE `saas_subscriptions`
ADD COLUMN `expired_with_tolerance_at` datetime DEFAULT NULL COMMENT 'Waktu expired + 8 menit toleransi';

-- 4. Tambah tabel baru untuk log lock (audit trail, siapa ke-lock kapan)
CREATE TABLE IF NOT EXISTS `company_lock_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `action` enum('LOCK','UNLOCK') NOT NULL,
  `reason` varchar(50) NOT NULL COMMENT 'TRIAL_EXPIRED, SUBSCRIPTION_EXPIRED, MANUAL, PAYMENT',
  `old_status` varchar(20) DEFAULT NULL,
  `new_status` varchar(20) DEFAULT NULL,
  `toleransi_menit` int(3) DEFAULT 8,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Update data existing biar sinkron (yang trial_until sudah lewat, set expired)
UPDATE companies SET subscription_status='expired', is_locked=1, locked_at=NOW(), locked_reason='TRIAL_EXPIRED' 
WHERE subscription_status='trial' AND trial_until < CURDATE() - INTERVAL 1 DAY;

-- 6. View untuk dashboard super admin lebih cepat
CREATE OR REPLACE VIEW `view_companies_expiring` AS
SELECT 
  c.id, c.name, c.email, c.phone, c.paket, c.subscription_status, c.trial_until, c.subscription_until, c.source, c.is_locked,
  DATEDIFF(c.trial_until, CURDATE()) as sisa_hari_trial,
  DATEDIFF(c.subscription_until, CURDATE()) as sisa_hari_langganan,
  p.price,
  CASE 
    WHEN c.subscription_status='trial' AND c.trial_until BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 2 DAY) THEN 'WILL_EXPIRE_2_DAYS'
    WHEN c.subscription_status='expired' THEN 'EXPIRED'
    WHEN c.subscription_status='trial' THEN 'TRIAL'
    ELSE 'ACTIVE'
  END as alert_status
FROM companies c
LEFT JOIN packages p ON p.package_code=c.paket
WHERE c.subscription_status IN ('trial','active','expired');

-- Selesai! Sekarang cron_expired.php bisa diupdate pakai is_locked biar lebih cepat
