-- =====================================================================
--  BUEL Gurme — MySQL / MariaDB sema tanimi (cPanel hosting icin)
--
--  Kullanim:
--    cPanel > MySQL Databases  ile veritabani ve kullanici olusturun,
--    kullaniciya "ALL PRIVILEGES" verin. Ardindan cPanel > phpMyAdmin
--    icinde veritabanini secip SQL sekmesinden once bu dosyayi,
--    sonra mysql/seed.sql dosyasini calistirin.
-- =====================================================================

set names utf8mb4;

-- --------------------------------------------------------------- tablolar

create table if not exists categories (
  id           int unsigned not null auto_increment,
  slug         varchar(120) not null,
  name         varchar(160) not null,
  description  text         null,
  image_url    varchar(500) null,
  sort_order   int          not null default 99,
  is_active    tinyint(1)   not null default 1,
  created_at   timestamp    not null default current_timestamp,
  primary key (id),
  unique key categories_slug_unique (slug),
  key categories_sort_idx (is_active, sort_order)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci;

create table if not exists products (
  id           int unsigned  not null auto_increment,
  category_id  int unsigned  not null,
  name         varchar(200)  not null,
  description  text          null,
  unit         varchar(60)   null,
  price        decimal(10,2) null,
  image_url    varchar(500)  null,
  is_featured  tinyint(1)    not null default 0,
  is_active    tinyint(1)    not null default 1,
  sort_order   int           not null default 99,
  created_at   timestamp     not null default current_timestamp,
  primary key (id),
  key products_category_idx (category_id),
  key products_sort_idx (is_active, sort_order),
  constraint products_category_fk foreign key (category_id)
    references categories (id) on delete cascade
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci;

create table if not exists settings (
  id             varchar(20)  not null default 'site',
  brand_name     varchar(160) not null default 'BUEL Gurme',
  tagline        varchar(160) not null default 'Gurme Elden',
  hero_title     varchar(300) not null default '',
  hero_subtitle  text         not null,
  about_title    varchar(300) not null default '',
  about_body     mediumtext   not null,
  phone          varchar(60)  not null default '',
  whatsapp       varchar(60)  not null default '',
  email          varchar(160) not null default '',
  address        varchar(500) not null default '',
  working_hours  varchar(200) not null default '',
  instagram      varchar(300) not null default '',
  facebook       varchar(300) not null default '',
  map_embed_url  varchar(800) not null default '',
  updated_at     timestamp    not null default current_timestamp on update current_timestamp,
  primary key (id)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci;

create table if not exists messages (
  id          int unsigned not null auto_increment,
  name        varchar(160) not null,
  phone       varchar(60)  not null,
  email       varchar(160) null,
  subject     varchar(300) null,
  body        text         not null,
  is_read     tinyint(1)   not null default 0,
  created_at  timestamp    not null default current_timestamp,
  primary key (id),
  key messages_created_idx (created_at)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci;