SET NAMES utf8mb4;
SET time_zone = "+07:00";

CREATE TABLE IF NOT EXISTS `locksystem_devices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `device_uid` VARCHAR(64) NOT NULL,
  `device_name` VARCHAR(128) DEFAULT NULL,
  `api_key` VARCHAR(128) NOT NULL,
  `last_seen` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_device_uid` (`device_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- enabled=1: đang bán | enabled=0: không bán (đã bán hoặc admin tắt)
CREATE TABLE IF NOT EXISTS `locksystem_slots` (
  `slot_id` INT NOT NULL,
  `device_uid` VARCHAR(64) NOT NULL,
  `relay_no` TINYINT NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `price` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `image_url` TEXT DEFAULT NULL,
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`slot_id`),
  KEY `idx_device` (`device_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `locksystem_orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `slot_id` INT NOT NULL,
  `total` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `payment_status` ENUM('Unpaid','Paid','Cancelled','Refunded') NOT NULL DEFAULT 'Unpaid',
  `note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `opened_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_slot` (`slot_id`),
  KEY `idx_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- order_id NULL: lệnh mở thủ công (admin)
CREATE TABLE IF NOT EXISTS `locksystem_commands` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NULL DEFAULT NULL,
  `device_uid` VARCHAR(64) NOT NULL,
  `slot_id` INT NOT NULL,
  `relay_no` TINYINT NOT NULL,
  `action` ENUM('OPEN') NOT NULL DEFAULT 'OPEN',
  `pulse_ms` INT NOT NULL DEFAULT 900,
  `status` ENUM('PENDING','DONE','FAILED','EXPIRED') NOT NULL DEFAULT 'PENDING',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `executed_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 10 MINUTE),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order` (`order_id`),
  KEY `idx_device_status` (`device_uid`,`status`),
  KEY `idx_slot` (`slot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `locksystem_transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `gateway` VARCHAR(100) NOT NULL,
  `transaction_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `account_number` VARCHAR(100) DEFAULT NULL,
  `sub_account` VARCHAR(250) DEFAULT NULL,
  `amount_in` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `amount_out` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `accumulated` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `code` VARCHAR(250) DEFAULT NULL,
  `transaction_content` TEXT,
  `reference_number` VARCHAR(255) DEFAULT NULL,
  `body` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_txn_date` (`transaction_date`),
  KEY `idx_account` (`account_number`),
  KEY `idx_ref` (`reference_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================
-- Dữ liệu mẫu: 1 ESP32 + 8 ô
-- ==========================
INSERT INTO `locksystem_devices` (`device_uid`, `device_name`, `api_key`)
VALUES ('ESP32_01', 'ESP32 - Tủ 1 (8 ô)', 'CHANGE_ME_ESP32_01_KEY')
ON DUPLICATE KEY UPDATE device_name=VALUES(device_name);

INSERT INTO `locksystem_slots` (`slot_id`, `device_uid`, `relay_no`, `title`, `price`, `image_url`, `enabled`)
VALUES
(1,'ESP32_01',1,'Ô số 1',3000,'',1),
(2,'ESP32_01',2,'Ô số 2',3000,'',1),
(3,'ESP32_01',3,'Ô số 3',3000,'',1),
(4,'ESP32_01',4,'Ô số 4',3000,'',1),
(5,'ESP32_01',5,'Ô số 5',3000,'',1),
(6,'ESP32_01',6,'Ô số 6',3000,'',1),
(7,'ESP32_01',7,'Ô số 7',3000,'',1),
(8,'ESP32_01',8,'Ô số 8',3000,'',1)
ON DUPLICATE KEY UPDATE
device_uid=VALUES(device_uid),
relay_no=VALUES(relay_no),
title=VALUES(title),
price=VALUES(price),
enabled=VALUES(enabled);






-- DEPLOY HOSTING:

SET NAMES utf8mb4;
SET time_zone = "+07:00";

CREATE TABLE IF NOT EXISTS `locksystem_devices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `device_uid` VARCHAR(64) NOT NULL,
  `device_name` VARCHAR(128) DEFAULT NULL,
  `api_key` VARCHAR(128) NOT NULL,
  `last_seen` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_device_uid` (`device_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- enabled=1: đang bán | enabled=0: không bán (đã bán hoặc admin tắt)
CREATE TABLE IF NOT EXISTS `locksystem_slots` (
  `slot_id` INT NOT NULL,
  `device_uid` VARCHAR(64) NOT NULL,
  `relay_no` TINYINT NOT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `price` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `image_url` TEXT DEFAULT NULL,
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`slot_id`),
  KEY `idx_device` (`device_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `locksystem_orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `slot_id` INT NOT NULL,
  `total` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `payment_status` ENUM('Unpaid','Paid','Cancelled','Refunded') NOT NULL DEFAULT 'Unpaid',
  `note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `opened_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_slot` (`slot_id`),
  KEY `idx_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `locksystem_commands` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_id` INT NULL DEFAULT NULL,
  `device_uid` VARCHAR(64) NOT NULL,
  `slot_id` INT NOT NULL,
  `relay_no` TINYINT NOT NULL,
  `action` ENUM('OPEN') NOT NULL DEFAULT 'OPEN',
  `pulse_ms` INT NOT NULL DEFAULT 900,
  `status` ENUM('PENDING','DONE','FAILED','EXPIRED') NOT NULL DEFAULT 'PENDING',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `executed_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order` (`order_id`),
  KEY `idx_device_status` (`device_uid`,`status`),
  KEY `idx_slot` (`slot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `locksystem_transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `gateway` VARCHAR(100) NOT NULL,
  `transaction_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `account_number` VARCHAR(100) DEFAULT NULL,
  `sub_account` VARCHAR(250) DEFAULT NULL,
  `amount_in` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `amount_out` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `accumulated` DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `code` VARCHAR(250) DEFAULT NULL,
  `transaction_content` TEXT,
  `reference_number` VARCHAR(255) DEFAULT NULL,
  `body` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_txn_date` (`transaction_date`),
  KEY `idx_account` (`account_number`),
  KEY `idx_ref` (`reference_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================
-- Dữ liệu mẫu: 1 ESP32 + 8 ô
-- ==========================
INSERT INTO `locksystem_devices` (`device_uid`, `device_name`, `api_key`)
VALUES ('ESP32_01', 'ESP32 - Tủ 1 (8 ô)', 'CHANGE_ME_ESP32_01_KEY')
ON DUPLICATE KEY UPDATE device_name=VALUES(device_name);

INSERT INTO `locksystem_slots` (`slot_id`, `device_uid`, `relay_no`, `title`, `price`, `image_url`, `enabled`)
VALUES
(1,'ESP32_01',1,'Ô số 1',3000,'',1),
(2,'ESP32_01',2,'Ô số 2',3000,'',1),
(3,'ESP32_01',3,'Ô số 3',3000,'',1),
(4,'ESP32_01',4,'Ô số 4',3000,'',1),
(5,'ESP32_01',5,'Ô số 5',3000,'',1),
(6,'ESP32_01',6,'Ô số 6',3000,'',1),
(7,'ESP32_01',7,'Ô số 7',3000,'',1),
(8,'ESP32_01',8,'Ô số 8',3000,'',1)
ON DUPLICATE KEY UPDATE
device_uid=VALUES(device_uid),
relay_no=VALUES(relay_no),
title=VALUES(title),
price=VALUES(price),
enabled=VALUES(enabled);