-- 卡片管理：充电卡业务表、一级菜单、查询权限和超级管理员授权。
-- 请使用具备 charging_pile_account、charging_pile_other 库权限的账号执行。

USE charging_pile_account;

CREATE TABLE IF NOT EXISTS `t_charging_card` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `card_no` varchar(64) NOT NULL COMMENT '卡号',
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '卡余额',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1启用，2禁用',
  `del_flag` tinyint NOT NULL DEFAULT '0' COMMENT '删除状态：0正常，2删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `create_by` bigint DEFAULT NULL COMMENT '添加人ID',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_by` bigint DEFAULT NULL COMMENT '修改人ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_card_no` (`card_no`),
  KEY `idx_status_del_flag` (`status`, `del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='充电卡';

USE charging_pile_other;

INSERT INTO sys_menu
  (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache,
   menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '卡片管理', 0, 10, '/card', 'card', NULL, 1, 0,
       'C', '0', 0, '/chargingCard/list', 'credit-card', 'admin', NOW(), '卡片管理一级菜单'
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu WHERE parent_id = 0 AND path = '/card'
);

SET @charging_card_menu_id := (
  SELECT menu_id
  FROM sys_menu
  WHERE parent_id = 0 AND path = '/card'
  ORDER BY menu_id
  LIMIT 1
);

UPDATE sys_menu
SET menu_name = '卡片管理',
    parent_id = 0,
    order_num = 10,
    component = 'card',
    menu_type = 'C',
    visible = '0',
    status = 0,
    perms = '/chargingCard/list',
    icon = 'credit-card',
    update_time = NOW(),
    remark = '卡片管理一级菜单'
WHERE menu_id = @charging_card_menu_id;

INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT 1, @charging_card_menu_id
WHERE @charging_card_menu_id IS NOT NULL;