-- Lunch MiniApp MVP MySQL Schema
-- Assumption: MySQL 8.0+
-- Charset/Collation: utf8mb4

set names utf8mb4;

-- 1) users
create table if not exists app_user (
  id char(36) not null,
  provider varchar(32) not null default 'toss',
  provider_user_id varchar(128) not null,
  nick_name varchar(50) not null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  primary key (id),
  unique key uk_app_user_provider (provider, provider_user_id)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-- 2) restaurants
create table if not exists restaurant (
  id char(36) not null,
  external_place_id varchar(128) null,
  name varchar(120) not null,
  category varchar(20) not null,
  address varchar(255) not null,
  phone varchar(30) null,
  opening_hours text null,
  lat double not null,
  lng double not null,
  is_active tinyint(1) not null default 1,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  primary key (id),
  unique key uk_restaurant_external_place_id (external_place_id),
  key idx_restaurant_lat_lng (lat, lng),
  key idx_restaurant_category (category),
  constraint chk_restaurant_category check (category in ('KOREAN','CHINESE','JAPANESE','WESTERN','OTHER'))
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-- 3) reviews (1 user : 1 restaurant)
create table if not exists restaurant_review (
  id char(36) not null,
  restaurant_id char(36) not null,
  user_id char(36) not null,
  rating tinyint not null,
  content varchar(500) not null,
  deleted_at timestamp null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  primary key (id),
  unique key uk_review_restaurant_user (restaurant_id, user_id),
  key idx_review_restaurant_created_at (restaurant_id, created_at),
  key idx_review_user_created_at (user_id, created_at),
  constraint fk_review_restaurant foreign key (restaurant_id) references restaurant(id) on delete cascade,
  constraint fk_review_user foreign key (user_id) references app_user(id) on delete cascade,
  constraint chk_review_rating check (rating between 1 and 5)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-- 4) likes (1 user : 1 restaurant)
create table if not exists restaurant_like (
  restaurant_id char(36) not null,
  user_id char(36) not null,
  created_at timestamp not null default current_timestamp,
  primary key (restaurant_id, user_id),
  key idx_like_restaurant (restaurant_id),
  key idx_like_user (user_id),
  constraint fk_like_restaurant foreign key (restaurant_id) references restaurant(id) on delete cascade,
  constraint fk_like_user foreign key (user_id) references app_user(id) on delete cascade
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-- 5) denormalized stats
create table if not exists restaurant_stat (
  restaurant_id char(36) not null,
  review_count int not null default 0,
  like_count int not null default 0,
  rating_sum int not null default 0,
  average_rating decimal(3,2) not null default 0.00,
  updated_at timestamp not null default current_timestamp on update current_timestamp,
  primary key (restaurant_id),
  constraint fk_stat_restaurant foreign key (restaurant_id) references restaurant(id) on delete cascade
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-- optional seed behavior:
-- insert into restaurant_stat (restaurant_id)
-- select r.id from restaurant r
-- on duplicate key update restaurant_id = values(restaurant_id);

-- 6) stat refresh procedure (call from app service after write)
drop procedure if exists refresh_restaurant_stat;
delimiter $$
create procedure refresh_restaurant_stat(in p_restaurant_id char(36))
begin
  declare v_review_count int default 0;
  declare v_like_count int default 0;
  declare v_rating_sum int default 0;
  declare v_average decimal(3,2) default 0.00;

  select count(*), ifnull(sum(r.rating), 0)
    into v_review_count, v_rating_sum
  from restaurant_review r
  where r.restaurant_id = p_restaurant_id
    and r.deleted_at is null;

  select count(*)
    into v_like_count
  from restaurant_like l
  where l.restaurant_id = p_restaurant_id;

  if v_review_count = 0 then
    set v_average = 0.00;
  else
    set v_average = round(v_rating_sum / v_review_count, 2);
  end if;

  insert into restaurant_stat (
    restaurant_id, review_count, like_count, rating_sum, average_rating, updated_at
  ) values (
    p_restaurant_id, v_review_count, v_like_count, v_rating_sum, v_average, current_timestamp
  )
  on duplicate key update
    review_count = values(review_count),
    like_count = values(like_count),
    rating_sum = values(rating_sum),
    average_rating = values(average_rating),
    updated_at = current_timestamp;
end $$
delimiter ;