-- Data test khớp demo MYGSM + LocalApiUrl https://gsmbk.cqdplus.com
-- Import vào DB trong config/database.php (cqdplusc_gsmbk)

CREATE TABLE IF NOT EXISTS devices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sim VARCHAR(32) NOT NULL DEFAULT '',
  phone VARCHAR(20) NOT NULL DEFAULT '',
  imei VARCHAR(20) NOT NULL DEFAULT '',
  status VARCHAR(64) NOT NULL DEFAULT 'NEW SIM',
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_sim_phone (sim, phone),
  KEY idx_imei (imei)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sim_imei_history (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sim VARCHAR(32) NOT NULL,
  imei VARCHAR(20) NOT NULL,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sim_imei (sim, imei)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO devices (sim, phone, imei, status, first_seen, last_seen, created_at, updated_at)
SELECT '89840420123456789012', '0981234567', '356938035643809', 'KNOWN SIM', NOW(), NOW(), NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM devices WHERE phone = '0981234567' OR sim = '89840420123456789012');

-- IMEI trong DB khác IMEI demo COM7 → IMEI CHANGED
INSERT INTO devices (sim, phone, imei, status, first_seen, last_seen, created_at, updated_at)
SELECT '89840420987654321098', '0912345678', '490154203237000', 'KNOWN SIM', NOW(), NOW(), NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM devices WHERE phone = '0912345678' OR sim = '89840420987654321098');

INSERT INTO devices (sim, phone, imei, status, first_seen, last_seen, created_at, updated_at)
SELECT '89840420000000000001', '0901234567', '353456789012345', 'KNOWN SIM', NOW(), NOW(), NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM devices WHERE phone = '0901234567');

INSERT INTO devices (sim, phone, imei, status, first_seen, last_seen, created_at, updated_at)
SELECT '89840420991122334455', '0978123456', '358240051111222', 'KNOWN SIM', NOW(), NOW(), NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM devices WHERE phone = '0978123456');

INSERT INTO sim_imei_history (sim, imei, phone, first_seen, last_seen)
VALUES ('89840420987654321098', '490154203237000', '0912345678', NOW(), NOW())
ON DUPLICATE KEY UPDATE last_seen = VALUES(last_seen), phone = VALUES(phone);