-- Thu chi vận hành: danh mục và các khoản chi thực tế.
-- Có thể chạy lại an toàn; không tạo dữ liệu khoản chi mẫu.

CREATE TABLE IF NOT EXISTS `finance_category_tb` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(20) NOT NULL DEFAULT '#2F80ED',
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1: đang dùng, 3: đã xóa',
  `date_created` DATETIME NOT NULL,
  `date_updated` DATETIME NULL DEFAULT NULL,
  `user_created` VARCHAR(255) NULL DEFAULT NULL,
  `user_updated` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_finance_category_name` (`name`),
  KEY `idx_finance_category_status_sort` (`status`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `operating_expense_tb` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `expense_date` DATE NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `vat_rate` DECIMAL(5,2) UNSIGNED NOT NULL DEFAULT 0,
  `gross_amount` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Số tiền đã gồm VAT',
  `has_receipt` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1: đang dùng, 3: đã xóa',
  `date_created` DATETIME NOT NULL,
  `date_updated` DATETIME NULL DEFAULT NULL,
  `user_created` VARCHAR(255) NULL DEFAULT NULL,
  `user_updated` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_operating_expense_month` (`status`, `expense_date`),
  KEY `idx_operating_expense_category` (`category_id`, `status`),
  CONSTRAINT `fk_operating_expense_category`
    FOREIGN KEY (`category_id`) REFERENCES `finance_category_tb` (`id`)
    ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `finance_category_tb`
  (`name`, `color`, `sort_order`, `status`, `date_created`, `user_created`)
SELECT seed.name, seed.color, seed.sort_order, 1, NOW(), 'migration'
FROM (
  SELECT 'Mặt bằng' AS name, '#2F80ED' AS color, 1 AS sort_order
  UNION ALL SELECT 'Marketing', '#EB5757', 2
  UNION ALL SELECT 'Điện nước', '#56CCF2', 3
  UNION ALL SELECT 'Mua sắm', '#9B51E0', 4
  UNION ALL SELECT 'Vận hành', '#F2994A', 5
  UNION ALL SELECT 'Phần mềm', '#27AE60', 6
) AS seed
WHERE NOT EXISTS (SELECT 1 FROM `finance_category_tb` LIMIT 1);