-- 校园闲置物品交易平台 DDL
-- 数据库：campus_idle_item_trading_platform
CREATE DATABASE IF NOT EXISTS campus_idle_item_trading_platform;
USE campus_idle_item_trading_platform;

-- 用户表
CREATE TABLE IF NOT EXISTS `user` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `student_id`    VARCHAR(20)  NOT NULL,
  `name`          VARCHAR(50)  NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `nickname`      VARCHAR(50)  DEFAULT NULL,
  `avatar_url`    VARCHAR(255) DEFAULT NULL,
  `signature`     VARCHAR(200) DEFAULT NULL,
  `campus_card_url` VARCHAR(255) DEFAULT NULL,
  `auth_status`   VARCHAR(20)  NOT NULL DEFAULT 'UNAUTHENTICATED',
  `status`        VARCHAR(20)  NOT NULL DEFAULT 'NORMAL',
  `role`          VARCHAR(20)  NOT NULL DEFAULT 'USER',
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 分类表
CREATE TABLE IF NOT EXISTS `category` (
  `id`         BIGINT      NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(50) NOT NULL,
  `status`     VARCHAR(20) NOT NULL DEFAULT 'ENABLED',
  `sort_order` INT         NOT NULL DEFAULT 0,
  `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 物品表
CREATE TABLE IF NOT EXISTS `item` (
  `id`           BIGINT        NOT NULL AUTO_INCREMENT,
  `seller_id`    BIGINT        NOT NULL,
  `category_id`  BIGINT        DEFAULT NULL,
  `title`        VARCHAR(100)  NOT NULL,
  `description`  TEXT,
  `price`        DECIMAL(10,2) NOT NULL,
  `images`       TEXT,
  `trade_method` VARCHAR(20)   NOT NULL,
  `status`       VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
  `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 订单表
CREATE TABLE IF NOT EXISTS `order` (
  `id`           BIGINT      NOT NULL AUTO_INCREMENT,
  `item_id`      BIGINT      NOT NULL,
  `buyer_id`     BIGINT      NOT NULL,
  `seller_id`    BIGINT      NOT NULL,
  `trade_method` VARCHAR(20) NOT NULL,
  `status`       VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `created_at`   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  KEY `idx_buyer_id` (`buyer_id`),
  KEY `idx_seller_id` (`seller_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 消息表
CREATE TABLE IF NOT EXISTS `message` (
  `id`           BIGINT       NOT NULL AUTO_INCREMENT,
  `from_user_id` BIGINT       NOT NULL,
  `to_user_id`   BIGINT       NOT NULL,
  `item_id`      BIGINT       DEFAULT NULL,
  `content`      TEXT,
  `image_url`    VARCHAR(255) DEFAULT NULL,
  `is_read`      TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_from_user_id` (`from_user_id`),
  KEY `idx_to_user_id` (`to_user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 审核记录表
CREATE TABLE IF NOT EXISTS `review` (
  `id`           BIGINT       NOT NULL AUTO_INCREMENT,
  `admin_id`     BIGINT       NOT NULL,
  `review_type`  VARCHAR(20)  NOT NULL,
  `target_id`    BIGINT       NOT NULL,
  `result`       VARCHAR(20)  NOT NULL,
  `reason`       VARCHAR(255) DEFAULT NULL,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_review_type_target` (`review_type`, `target_id`),
  KEY `idx_admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入默认分类数据
INSERT INTO `category` (`name`, `status`, `sort_order`) VALUES
('教材', 'ENABLED', 1),
('电子产品', 'ENABLED', 2),
('生活用品', 'ENABLED', 3),
('体育用品', 'ENABLED', 4),
('衣物', 'ENABLED', 5),
('其他', 'ENABLED', 6);

-- 插入默认管理员（密码：admin，MD5加密）
INSERT INTO `user` (`student_id`, `name`, `password_hash`, `nickname`, `role`, `auth_status`, `status`)
VALUES ('admin', '管理员', 'e10adc3949ba59abbe56e057f20f883e', '管理员', 'ADMIN', 'AUTHENTICATED', 'NORMAL');