USE cod_db;

CREATE TABLE IF NOT EXISTS users
(
    id              INT AUTO_INCREMENT PRIMARY KEY,
    full_name       VARCHAR(255)                                                                                   NOT NULL,
    phone           VARCHAR(20)                                                                                    NOT NULL,
    username        VARCHAR(255)                                                                                   NOT NULL UNIQUE,
    email           VARCHAR(255)                                                                                   NOT NULL UNIQUE,
    profile_picture VARCHAR(255),
    address         TEXT                                                                                           NOT NULL,
    password        VARCHAR(255)                                                                                   NOT NULL,
    national_id     VARCHAR(20)                                                                                    NOT NULL UNIQUE,
    role            ENUM ('super_admin', 'admin_agent', 'confirmation_agent', 'stock_manager', 'customer_support') NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (full_name, phone, username, email, profile_picture, address, password, national_id, role)
VALUES
    ('Admin', '0600000000', 'admin', 'admin@example.com', 'profile1.jpg', '-', 'root', 'XXXXXXX', 'super_admin');

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
);

# -- Insert sample categories
# INSERT INTO categories (title, subtitle, link, thumbnail)
# VALUES ('Electronics', 'Find the latest gadgets', 'electronics', ''),
#        ('Books', 'Explore a wide range of books', 'books', ''),
#        ('Clothing', 'Discover the newest fashion trends', 'clothing', ''),
#        ('Home & Kitchen', 'Upgrade your home and kitchen', 'home-kitchen', ''),
#        ('Toys & Games', 'Fun toys and games for kids', 'toys-games', '');


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),
    stock           INT            NOT NULL,
    thumbnail_url   VARCHAR(255)   NOT NULL,
    product_type    VARCHAR(255)   NOT NULL,
    has_options     BOOLEAN        NOT NULL,
    is_archived     BOOLEAN   DEFAULT FALSE,
    is_public       BOOLEAN   DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_categories
(
    product_id  INT,
    category_id INT,
    FOREIGN KEY (product_id) REFERENCES products (id),
    FOREIGN KEY (category_id) REFERENCES categories (id),
    PRIMARY KEY (product_id, category_id)
);

CREATE TABLE product_options
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    title      VARCHAR(255) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE product_variants
(
    id        INT AUTO_INCREMENT PRIMARY KEY,
    option_id INT,
    variant   VARCHAR(255) NOT NULL,
    FOREIGN KEY (option_id) REFERENCES product_options (id)
);

CREATE TABLE product_images
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    image_url  VARCHAR(255) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE variant_combinations
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    price      DECIMAL(10, 2) NOT NULL,
    stock      INT            NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE combination_options
(
    id             INT AUTO_INCREMENT PRIMARY KEY,
    combination_id INT,
    option_title   VARCHAR(255) NOT NULL,
    variant        VARCHAR(255) NOT NULL,
    FOREIGN KEY (combination_id) REFERENCES variant_combinations (id)
);

CREATE TABLE areas
(
    id             INT AUTO_INCREMENT PRIMARY KEY,
    city           VARCHAR(255)   NOT NULL,
    delivery_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders
(
    id             INT(11)        NOT NULL AUTO_INCREMENT,
    name           VARCHAR(100)   NOT NULL,
    phone          VARCHAR(20)    NOT NULL,
    city           VARCHAR(100)   NOT NULL,
    address        TEXT           NOT NULL,
    message        TEXT,
    details        TEXT,
    status         VARCHAR(50) DEFAULT 'pending',
    source         VARCHAR(50) DEFAULT 'shop',
    delivery_price DECIMAL(10, 2) NOT NULL,
    total_price    DECIMAL(10, 2) NOT NULL,
    is_archived    BOOLEAN     DEFAULT FALSE,
    is_exported    BOOLEAN     DEFAULT FALSE,
    created_at     TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE order_items
(
    id                INT(11)        NOT NULL AUTO_INCREMENT,
    order_id          INT(11)        NOT NULL,
    product_id        INT(11)        NOT NULL,
    selected_variants JSON,
    quantity          INT(11)        NOT NULL,
    price             DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (order_id) REFERENCES orders (id),
    FOREIGN KEY (product_id) REFERENCES products (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,
    changed_by  INT         NOT NULL,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders (id),
    FOREIGN KEY (changed_by) REFERENCES users (id)
);

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      BOOLEAN   DEFAULT FALSE,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
);


CREATE TABLE visitors
(
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    timestamp  INT         NOT NULL
);

-- Create the table with an `id` column
CREATE TABLE seo
(
    id              INT 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 '',
    logo_horizontal VARCHAR(255) DEFAULT '',
    thumbnail       VARCHAR(255) DEFAULT '',
    fav_icon        VARCHAR(255) DEFAULT '',
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE website_content
(
    id          INT PRIMARY KEY,
    header_html TEXT      DEFAULT NULL,
    body_html   TEXT      DEFAULT NULL,
    custom_css  TEXT      DEFAULT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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}');

CREATE TABLE pixels
(
    id                  INT PRIMARY KEY DEFAULT 1,
    facebook_pixel      VARCHAR(255),
    snapchat_pixel      VARCHAR(255),
    tiktok_pixel        VARCHAR(255),
    google_analytics_id VARCHAR(255)
);

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
);

