CREATE DATABASE IF NOT EXISTS toyshop DEFAULT CHARSET utf8mb4;
USE toyshop;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  email VARCHAR(100),
  avatar VARCHAR(255),
  role VARCHAR(20) NOT NULL,
  enabled TINYINT(1) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  category_id BIGINT,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  sales INT NOT NULL,
  age_range VARCHAR(50),
  safety_info VARCHAR(255),
  on_sale TINYINT(1) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE IF NOT EXISTS product_images (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT NOT NULL,
  url VARCHAR(255) NOT NULL,
  sort_order INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS carousels (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  image_url VARCHAR(255) NOT NULL,
  link_url VARCHAR(255),
  sort_order INT NOT NULL
);

CREATE TABLE IF NOT EXISTS notices (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  content TEXT,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS addresses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  receiver_name VARCHAR(50) NOT NULL,
  receiver_phone VARCHAR(20) NOT NULL,
  detail VARCHAR(255) NOT NULL,
  is_default TINYINT(1) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS cart_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  status VARCHAR(30) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  receiver_name VARCHAR(50) NOT NULL,
  receiver_phone VARCHAR(20) NOT NULL,
  receiver_address VARCHAR(255) NOT NULL,
  logistics_no VARCHAR(50),
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO categories (name, description) VALUES
('益智玩具', '启发思维与逻辑'),
('拼装积木', '动手能力训练'),
('毛绒玩具', '柔软安全');

INSERT INTO products (category_id, name, description, price, stock, sales, age_range, safety_info, on_sale, created_at, updated_at) VALUES
(1, '多功能益智盒', '多模块益智训练', 129.00, 100, 10, '3-6岁', 'CCC认证', 1, NOW(), NOW()),
(2, '创意拼装积木', '提升动手与空间感', 89.00, 200, 25, '4-8岁', '环保材质', 1, NOW(), NOW()),
(3, '可爱毛绒熊', '亲子陪伴', 59.00, 150, 30, '2-6岁', '亲肤材质', 1, NOW(), NOW());

INSERT INTO product_images (product_id, url, sort_order) VALUES
(1, 'https://picsum.photos/seed/toy1/600/400', 1),
(2, 'https://picsum.photos/seed/toy2/600/400', 1),
(3, 'https://picsum.photos/seed/toy3/600/400', 1);

INSERT INTO carousels (image_url, link_url, sort_order) VALUES
('https://picsum.photos/seed/banner1/1200/400', '', 1),
('https://picsum.photos/seed/banner2/1200/400', '', 2);

INSERT INTO notices (title, content, created_at) VALUES
('欢迎来到童飞玩具商城', '全场新品上架，欢迎选购。', NOW());