reread1.sql

create database Reread character set utf8mb4 collate utf8mb4_unicode_ci;
use Reread;

-- 1. table users (ผู้ใช้งาน + ข้อมูลธนาคาร)
create table users (
  user_id int auto_increment primary key,
  username varchar(50) not null unique,
  email varchar(100) not null unique,
  phone_num varchar(11) not null unique,
  password varchar(255) not null,
  user_role enum('buyer','seller') not null default 'buyer',
  default_address varchar(255),
  
  bank_name varchar(50),
  bank_account_number varchar(20),
  bank_account_name varchar(100),
  
  created_at datetime default current_timestamp
);

-- 2. table categories (ชื่อหมวดหมู่หนังสือ)
create table categories (
  category_id int auto_increment primary key,
  name varchar(50) not null unique
);

-- 3. table books
create table books (
  book_id int auto_increment primary key,
  seller_id int not null,
  title varchar(255) not null,
  author varchar(255),
  description text,
  price decimal(10,2) not null,
  `condition` enum('New','Like new','Used','Poor'),
  language enum('Thai','English','Chinese','Japanese','Korean'),
  
  quantity int not null default 1,
  status enum('available','sold out','removed') not null default 'available',
  cover_image varchar(255),
  
  created_at datetime default current_timestamp,
  updated_at datetime on update current_timestamp,
  foreign key (seller_id) references users(user_id)
);

-- 4. table book_categories (check box)
create table book_categories (
  book_id int not null,
  category_id int not null,
  primary key (book_id, category_id),
  foreign key (book_id) references books(book_id) on delete cascade,
  foreign key (category_id) references categories(category_id) on delete cascade
);

-- 5. table images (รูปที่ 2-5)
create table images (
  image_id int auto_increment primary key,
  book_id int not null,
  image_url varchar(255),
  uploaded_at datetime default current_timestamp,
  foreign key (book_id) references books(book_id) on delete cascade
);

-- 6. transaction
create table `transaction` (
  transaction_id int auto_increment primary key,
  buyer_id int not null,
  -- เก็บที่อยู่จริง ณ วันที่ซื้อ
  delivery_address varchar(255) not null, 
  
  payment_method varchar(50) not null default 'bank transfer',
  total_price decimal(10,2),
  transaction_status enum('Pending','Completed','Cancelled') not null default 'Pending',
  created_at datetime default current_timestamp,
  foreign key (buyer_id) references users(user_id)
);

-- 7. table item (รายละเอียดสินค้าในบิล)
create table item (
  item_id int auto_increment primary key,
  transaction_id int not null,
  seller_id int not null,
  book_id int not null,
  
  -- เก็บราคาจริง ณ วันที่ซื้อ snap
  price_per_unit decimal(10,2) not null,
  quantity int not null default 1,
  total_item_price decimal(10,2),
  foreign key (transaction_id) references `transaction`(transaction_id) on delete cascade,
  foreign key (seller_id) references users(user_id),
  foreign key (book_id) references books(book_id)
);


insert into categories (name) values
('Fiction'), 
('Crime'), 
('Fantasy'), 
('Science'), 
('Romance'), 
('History'), 
('Self Development'), 
('Education'), 
('Technology'), 
('Classics'), 
('LGBTQ+'), 
('Coming of age'), 
('Travel'), 
('Others');

-- เพิ่ม Profile user 
alter table users add column profile_picture varchar(255);

-- แยก payment 
create table bank_accounts (
  account_id int auto_increment primary key,
  user_id int not null,
  bank_name varchar(50),
  bank_account_number varchar(20),
  bank_account_name varchar(100),
  foreign key (user_id) references users(user_id)on delete cascade
);

-- 2. ย้าย
insert into  bank_accounts (user_id, bank_name, bank_account_number, bank_account_name)
select user_id, bank_name, bank_account_number, bank_account_name from users
where bank_account_number is not null and bank_account_number != '';

-- 3. ลบคอลัมน์เก่าทิ้งจาก users
alter table users
drop column bank_name,
drop column bank_account_number,
drop column bank_account_name;