-- ══════════════════════════════════════════════════════════
-- Phase 5: Customers CRM + Self-Service + Reminders + Roles
-- ══════════════════════════════════════════════════════════

-- ── customers (CRM) ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customers` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint UNSIGNED NOT NULL DEFAULT 1,
  `name` varchar(150) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customers_company_phone` (`company_id`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Link subscriptions to customers
ALTER TABLE `subscriptions`
  ADD COLUMN IF NOT EXISTS `customer_id` BIGINT UNSIGNED DEFAULT NULL AFTER `id`;

-- Backfill customers from existing subscriptions
INSERT IGNORE INTO `customers` (`company_id`,`name`,`phone`,`created_at`,`updated_at`)
SELECT DISTINCT company_id, customer_name, customer_phone, NOW(), NOW()
FROM `subscriptions`;

UPDATE `subscriptions` s
JOIN `customers` c ON s.company_id = c.company_id AND s.customer_phone = c.phone
SET s.customer_id = c.id
WHERE s.customer_id IS NULL;

-- ── Expiry reminder tracking ─────────────────────────────────
ALTER TABLE `subscriptions`
  ADD COLUMN IF NOT EXISTS `reminder_sent` TINYINT(1) DEFAULT 0 AFTER `status`;

-- ── Multi-admin roles: allow 'cashier' role ───────────────────
ALTER TABLE `admin_users`
  MODIFY COLUMN `role` VARCHAR(20) DEFAULT 'admin';