CREATE TABLE brands (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    brand_name VARCHAR(50) NOT NULL,
    brand_img VARCHAR(300) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL,
    category_img VARCHAR(300) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    short_des VARCHAR(500) NOT NULL,
    price VARCHAR(50) NOT NULL,
    points INT(50) NOT NULL,
    discount BOOLEAN NOT NULL,
    discount_price VARCHAR(50) NOT NULL,
    image VARCHAR(200) NOT NULL,
    stock BOOLEAN NOT NULL,
    star FLOAT NOT NULL,
    remark ENUM('popular','new','top','special','trending','regular') NOT NULL,
    category_id INT UNSIGNED,
    brand_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE product_details (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    img1 VARCHAR(200) NOT NULL,
    img2 VARCHAR(200) NOT NULL,
    img3 VARCHAR(200) NOT NULL,
    img4 VARCHAR(200) NOT NULL,
    des LONGTEXT NOT NULL,
    color VARCHAR(200) NOT NULL,
    size VARCHAR(200) NOT NULL,
    product_id INT UNSIGNED UNIQUE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE product_sliders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    short_des VARCHAR(500) NOT NULL,
    price VARCHAR(100) NOT NULL,
    image VARCHAR(200) NOT NULL,
    product_id INT UNSIGNED UNIQUE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    points INT(50) NOT NULL,
    amount INT(50) NOT NULL,
    referral_code VARCHAR(100) UNIQUE NOT NULL,
    otp VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE amount_expenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    is_withdraw  BOOLEAN DEFAULT FALSE,
    is_purchase  BOOLEAN DEFAULT FALSE,
    is_done BOOLEAN DEFAULT FALSE,
    type VARCHAR(100) UNIQUE NOT NULL,
    user_id INT UNSIGNED REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE customer_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cus_name VARCHAR(100) NOT NULL,
    cus_add VARCHAR(500) NOT NULL,
    cus_city VARCHAR(50) NOT NULL,
    cus_state VARCHAR(50) NOT NULL,
    cus_postcode VARCHAR(50) NOT NULL,
    cus_country VARCHAR(50) NOT NULL,
    cus_phone VARCHAR(50) NOT NULL,
    cus_fax VARCHAR(50) NOT NULL,
    ship_name VARCHAR(100) NOT NULL,
    ship_add VARCHAR(100) NOT NULL,
    ship_city VARCHAR(100) NOT NULL,
    ship_state VARCHAR(100) NOT NULL,
    ship_postcode VARCHAR(100) NOT NULL,
    ship_country VARCHAR(100) NOT NULL,
    ship_phone VARCHAR(50) NOT NULL,
    user_id INT UNSIGNED UNIQUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE product_reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(1000) NOT NULL,
    rating VARCHAR(10) NOT NULL,
    user_id INT UNSIGNED,
    product_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE TABLE wishlists (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED,
    user_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE carts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    product_id INT UNSIGNED,
    color VARCHAR(200) NOT NULL,
    size VARCHAR(200) NOT NULL,
    qty VARCHAR(200) NOT NULL,
    price VARCHAR(200) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    total VARCHAR(50) NOT NULL,
    vat VARCHAR(50) NOT NULL,
    payable VARCHAR(50) NOT NULL,
    cus_details VARCHAR(500) NOT NULL,
    ship_details VARCHAR(500) NOT NULL,
    tran_id VARCHAR(100) NOT NULL,
    val_id VARCHAR(100) DEFAULT '0' NOT NULL,
    delivery_status ENUM('Pending','Processing','Completed') NOT NULL,
    payment_status VARCHAR(255) NOT NULL,
    user_id INT UNSIGNED,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED,
    product_id INT UNSIGNED,
    user_id INT UNSIGNED,
    qty VARCHAR(50) NOT NULL,
    sale_price VARCHAR(50) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE commissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(255),
    commission_percent DECIMAL(5,2) UNSIGNED,
    commission_amount DECIMAL(10,2) UNSIGNED,
    generation INT UNSIGNED,
    order_id INT UNSIGNED REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    user_id INT UNSIGNED REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


CREATE TABLE referrals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    referrer_id INT,
    level INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (referrer_id) REFERENCES users(id)
);


CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    prize VARCHAR(255),
    used_points INT,
    coupon_code VARCHAR(255),
    opened_by_id INT,
    open_date DATE,
    is_enabled BOOLEAN DEFAULT FALSE,
    prize_given BOOLEAN DEFAULT FALSE,
    expiry_date DATE,
    FOREIGN KEY (opened_by_id) REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE winners (
    id INT PRIMARY KEY AUTO_INCREMENT,
    position INT,
    prize VARCHAR(255),
    prize_given BOOLEAN DEFAULT FALSE,
    prize_given_to INT,
    prize_given_date DATE,
    FOREIGN KEY (prize_given_to) REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Reseller Table
CREATE TABLE resellers (
    id SERIAL PRIMARY KEY,
    reseller_name VARCHAR(255) NOT NULL,
    reseller_email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -- Add other relevant fields for the reseller
);

-- ResellerProductPrice Table
CREATE TABLE reseller_products (
    id SERIAL PRIMARY KEY,
    reseller_id INT REFERENCES resellers(id),
    product_id INT REFERENCES products(id),
    reseller_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ResellerOrder Table
CREATE TABLE reseller_orders (
    id SERIAL PRIMARY KEY,
    reseller_id INT REFERENCES resellers(id),
    order_date DATE,
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -- Add other relevant fields for the order
);

-- ResellerOrderItem Table
CREATE TABLE reseller_order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES reseller_orders(id),
    product_id INT REFERENCES products(id),
    reseller_product_id INT REFERENCES reseller_products(id),
    quantity INT,
    unit_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -- Add other relevant fields for the order item
);
