-- supabase-schema.sql
-- Run all statements in the Supabase SQL editor (Database → SQL Editor).
-- Tables are created only if they do not already exist.
-- Order matters: categories must exist before products (foreign key).

-- ── Sequences ─────────────────────────────────────────────────────────────────

CREATE SEQUENCE IF NOT EXISTS admins_id_seq;
CREATE SEQUENCE IF NOT EXISTS categories_id_seq;
CREATE SEQUENCE IF NOT EXISTS filters_id_seq;
CREATE SEQUENCE IF NOT EXISTS products_id_seq;
CREATE SEQUENCE IF NOT EXISTS blog_posts_id_seq;
CREATE SEQUENCE IF NOT EXISTS partners_id_seq;
CREATE SEQUENCE IF NOT EXISTS gallery_images_id_seq;
CREATE SEQUENCE IF NOT EXISTS contact_messages_id_seq;
CREATE SEQUENCE IF NOT EXISTS product_inquiries_id_seq;

-- ── Tables ────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS admins (
  id            integer   NOT NULL DEFAULT nextval('admins_id_seq') PRIMARY KEY,
  username      text      NOT NULL UNIQUE,
  password_hash text      NOT NULL,
  created_at    timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE admins_id_seq OWNED BY admins.id;

CREATE TABLE IF NOT EXISTS categories (
  id              integer   NOT NULL DEFAULT nextval('categories_id_seq') PRIMARY KEY,
  name_bg         text      NOT NULL,
  name_en         text      NOT NULL,
  slug            text      NOT NULL UNIQUE,
  icon            text,
  image_url       text,
  description_bg  text,
  description_en  text,
  created_at      timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE categories_id_seq OWNED BY categories.id;

CREATE TABLE IF NOT EXISTS filters (
  id           integer   NOT NULL DEFAULT nextval('filters_id_seq') PRIMARY KEY,
  category_ids integer[],
  name_bg      text      NOT NULL,
  name_en      text      NOT NULL,
  field_key    text      NOT NULL,
  filter_type  text      NOT NULL,
  options      text,
  unit         text,
  created_at   timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE filters_id_seq OWNED BY filters.id;

-- products depends on categories
CREATE TABLE IF NOT EXISTS products (
  id              integer          NOT NULL DEFAULT nextval('products_id_seq') PRIMARY KEY,
  title_bg        text             NOT NULL,
  title_en        text             NOT NULL,
  description_bg  text             NOT NULL,
  description_en  text             NOT NULL,
  category_id     integer          NOT NULL REFERENCES categories(id),
  price           double precision,
  condition       text             NOT NULL,
  images          text             NOT NULL DEFAULT '[]',
  attributes      text,
  is_published    boolean          NOT NULL DEFAULT true,
  created_at      timestamp        NOT NULL DEFAULT now()
);
ALTER SEQUENCE products_id_seq OWNED BY products.id;

CREATE TABLE IF NOT EXISTS blog_posts (
  id                    integer   NOT NULL DEFAULT nextval('blog_posts_id_seq') PRIMARY KEY,
  slug                  text      NOT NULL UNIQUE,
  title_bg              text      NOT NULL,
  title_en              text      NOT NULL,
  content_bg            text      NOT NULL,
  content_en            text      NOT NULL,
  excerpt_bg            text      NOT NULL,
  excerpt_en            text      NOT NULL,
  cover_image           text,
  meta_title_bg         text,
  meta_title_en         text,
  meta_description_bg   text,
  meta_description_en   text,
  images                text      NOT NULL DEFAULT '[]',
  is_published          boolean   NOT NULL DEFAULT false,
  published_at          timestamp,
  created_at            timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE blog_posts_id_seq OWNED BY blog_posts.id;

CREATE TABLE IF NOT EXISTS partners (
  id          integer   NOT NULL DEFAULT nextval('partners_id_seq') PRIMARY KEY,
  name        text      NOT NULL,
  logo_url    text      NOT NULL,
  website_url text,
  sort_order  integer   NOT NULL DEFAULT 0,
  created_at  timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE partners_id_seq OWNED BY partners.id;

CREATE TABLE IF NOT EXISTS gallery_images (
  id           integer   NOT NULL DEFAULT nextval('gallery_images_id_seq') PRIMARY KEY,
  image_url    text      NOT NULL,
  caption_bg   text,
  caption_en   text,
  sort_order   integer   NOT NULL DEFAULT 0,
  is_published boolean   NOT NULL DEFAULT true,
  tags         text[]    NOT NULL DEFAULT '{}',
  created_at   timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE gallery_images_id_seq OWNED BY gallery_images.id;

CREATE TABLE IF NOT EXISTS contact_messages (
  id           integer   NOT NULL DEFAULT nextval('contact_messages_id_seq') PRIMARY KEY,
  company_name text,
  name         text,
  phone        text,
  email        text,
  subject      text,
  message      text      NOT NULL,
  image_urls   text      NOT NULL DEFAULT '[]',
  is_read      boolean   NOT NULL DEFAULT false,
  created_at   timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE contact_messages_id_seq OWNED BY contact_messages.id;

CREATE TABLE IF NOT EXISTS product_inquiries (
  id           integer   NOT NULL DEFAULT nextval('product_inquiries_id_seq') PRIMARY KEY,
  product_id   integer   NOT NULL,
  product_name text      NOT NULL,
  company_name text,
  name         text,
  phone        text      NOT NULL,
  email        text,
  subject      text,
  message      text      NOT NULL,
  is_read      boolean   NOT NULL DEFAULT false,
  created_at   timestamp NOT NULL DEFAULT now()
);
ALTER SEQUENCE product_inquiries_id_seq OWNED BY product_inquiries.id;

CREATE TABLE IF NOT EXISTS site_settings (
  key        text      PRIMARY KEY,
  value      text,
  updated_at timestamp NOT NULL DEFAULT now()
);