create table areas
(
    id             int auto_increment
        primary key,
    city           varchar(255)   not null,
    delivery_price decimal(10, 2) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table categories
(
    id         int auto_increment
        primary key,
    title      varchar(255)                          not null,
    subtitle   varchar(255)                          not null,
    link       varchar(255)                          not null,
    thumbnail  varchar(255)                          not null,
    created_at timestamp default current_timestamp() not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table metadata
(
    id          int auto_increment
        primary key,
    store_name  varchar(255)                          not null,
    domain_name varchar(255)                          not null,
    title       varchar(255)                          not null,
    description text                                  not null,
    keywords    text                                  not null,
    created_at  timestamp default current_timestamp() not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table orders
(
    id             int auto_increment
        primary key,
    name           varchar(100)                            not null,
    phone          varchar(20)                             not null,
    city           varchar(100)                            not null,
    address        text                                    not null,
    message        text                                    null,
    status         varchar(50) default 'pending'           null,
    source         varchar(50) default 'shop'              null,
    created_at     timestamp   default current_timestamp() not null,
    is_archived    tinyint(1)  default 0                   null,
    delivery_price decimal(10, 2)                          not null,
    total_price    decimal(10, 2)                          not null,
    details        text                                    null,
    is_exported    tinyint(1)  default 0                   null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table pixels
(
    id                  int default 1 not null
        primary key,
    facebook_pixel      varchar(255)  null,
    snapchat_pixel      varchar(255)  null,
    tiktok_pixel        varchar(255)  null,
    google_analytics_id varchar(255)  null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table products
(
    id              int auto_increment
        primary key,
    title           varchar(255)                           not null,
    subtitle        varchar(255)                           not null,
    description     text                                   not null,
    price           decimal(10, 2)                         not null,
    comparing_price decimal(10, 2)                         null,
    stock           int                                    not null,
    thumbnail_url   varchar(255)                           not null,
    product_type    varchar(255)                           not null,
    has_options     tinyint(1)                             not null,
    created_at      timestamp  default current_timestamp() not null,
    is_archived     tinyint(1) default 0                   null,
    is_public       tinyint(1) default 1                   null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table order_items
(
    id                int auto_increment
        primary key,
    order_id          int                          not null,
    product_id        int                          not null,
    selected_variants longtext collate utf8mb4_bin null,
    quantity          int                          not null,
    price             decimal(10, 2)               not null,
    constraint order_items_ibfk_1
        foreign key (order_id) references orders (id),
    constraint order_items_ibfk_2
        foreign key (product_id) references products (id),
    constraint selected_variants
        check (json_valid(`selected_variants`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index order_id
    on order_items (order_id);

create index product_id
    on order_items (product_id);

create table product_categories
(
    product_id  int not null,
    category_id int not null,
    primary key (product_id, category_id),
    constraint product_categories_ibfk_1
        foreign key (product_id) references products (id),
    constraint product_categories_ibfk_2
        foreign key (category_id) references categories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index category_id
    on product_categories (category_id);

create table product_images
(
    id         int auto_increment
        primary key,
    product_id int          null,
    image_url  varchar(255) not null,
    constraint product_images_ibfk_1
        foreign key (product_id) references products (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index product_id
    on product_images (product_id);

create table product_options
(
    id         int auto_increment
        primary key,
    product_id int          null,
    title      varchar(255) not null,
    constraint product_options_ibfk_1
        foreign key (product_id) references products (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index product_id
    on product_options (product_id);

create table product_variants
(
    id        int auto_increment
        primary key,
    option_id int          null,
    variant   varchar(255) not null,
    constraint product_variants_ibfk_1
        foreign key (option_id) references product_options (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index option_id
    on product_variants (option_id);

create table seo
(
    id              int                                      not null
        primary key,
    store_name      varchar(255)                             not null,
    domain_name     varchar(255)                             not null,
    title           varchar(255)                             not null,
    description     text                                     not null,
    keywords        text                                     not null,
    logo_square     varchar(255) default ''                  null,
    logo_horizontal varchar(255) default ''                  null,
    thumbnail       varchar(255) default ''                  null,
    fav_icon        varchar(255) default ''                  null,
    created_at      timestamp    default current_timestamp() not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table notifications
(
    id           int auto_increment
        primary key,
    user_id      int                                    not null,
    message      varchar(255)                           not null,
    type         varchar(50)                            not null,
    reference_id int                                    not null,
    is_read      tinyint(1) default 0                   null,
    created_at   timestamp  default current_timestamp() not null,
    constraint notifications_ibfk_1
        foreign key (user_id) references users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index user_id
    on notifications (user_id);

create table order_status_changes
(
    id          int auto_increment
        primary key,
    order_id    int                                   not null,
    new_status  varchar(50)                           not null,
    comment     text                                  null,
    changed_by  int                                   not null,
    change_date timestamp default current_timestamp() not null,
    constraint order_status_changes_ibfk_1
        foreign key (order_id) references orders (id),
    constraint order_status_changes_ibfk_2
        foreign key (changed_by) references users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index changed_by
    on order_status_changes (changed_by);

create index order_id
    on order_status_changes (order_id);

create table variant_combinations
(
    id         int auto_increment
        primary key,
    product_id int            null,
    price      decimal(10, 2) not null,
    stock      int            not null,
    constraint variant_combinations_ibfk_1
        foreign key (product_id) references products (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table combination_options
(
    id             int auto_increment
        primary key,
    combination_id int          null,
    option_title   varchar(255) not null,
    variant        varchar(255) not null,
    constraint combination_options_ibfk_1
        foreign key (combination_id) references variant_combinations (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create index combination_id
    on combination_options (combination_id);

create index product_id
    on variant_combinations (product_id);

create table visitors
(
    id         int auto_increment
        primary key,
    ip_address varchar(45) not null,
    timestamp  int         not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

create table website_content
(
    id          int                                   not null
        primary key,
    header_html text                                  null,
    body_html   text                                  null,
    custom_css  text                                  null,
    created_at  timestamp default current_timestamp() not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO seo (id, store_name, domain_name, title, description, keywords, logo_square, logo_horizontal, thumbnail,
                  fav_icon)
VALUES (1, 'متجر على الإنترنت', 'example.com', 'مرحبا بكم في متجرنا', 'هذا هو وصف متجرنا الإلكتروني.',
        'تسوق, متجر, إلكتروني, عروض, منتجات', '', '', '', '');

INSERT INTO website_content (id, header_html, body_html, custom_css)
VALUES (1,
        '<script>\n  console.log("custom script working !");\n</script>',
        '<!-- This is a comment in the Body HTML -->',
        '* {\n    margin: 0;\n    padding: 0;\n    box-sizing: border-box;\n}');

insert into pixels(id, facebook_pixel, snapchat_pixel, tiktok_pixel, google_analytics_id)
    VALUE (1, null, null, null, null);