CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('buyer','seller','admin') DEFAULT 'buyer',
    status ENUM('active','pending','blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, phone, email, password, role)
VALUES 
('John Buyer', '0700000001', 'buyer@test.com', 'hashed_pass', 'buyer'),
('Alice Seller', '0700000002', 'seller@test.com', 'hashed_pass', 'seller'),
('Super Admin', '0700000003', 'admin@test.com', 'hashed_pass', 'admin');


CREATE TABLE sellers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    shop_name VARCHAR(150),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    balance DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO sellers (user_id, shop_name)
VALUES (2, 'Alice Electronics');



CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seller_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (seller_id) REFERENCES sellers(id) ON DELETE CASCADE
);


INSERT INTO products (seller_id, name, description, price)
VALUES 
(1, 'Wireless Headphones', 'High quality sound', 150000),
(1, 'Smart Watch', 'Fitness tracking watch', 80000);




CREATE TABLE product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image_url VARCHAR(255),
    is_primary TINYINT(1) DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);


INSERT INTO product_images (product_id, image_url, is_primary)
VALUES
(1, 'uploads/headphones1.jpg', 1),
(1, 'uploads/headphones2.jpg', 0),
(2, 'uploads/watch1.jpg', 1);



CREATE TABLE seller_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seller_id INT NOT NULL,
    amount DECIMAL(10,2),
    type ENUM('listing_fee','subscription'),
    status ENUM('pending','paid','failed') DEFAULT 'pending',
    reference VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (seller_id) REFERENCES sellers(id) ON DELETE CASCADE
);

INSERT INTO seller_payments (seller_id, amount, type, status, reference)
VALUES (1, 5000, 'listing_fee', 'paid', 'PAY123456');


CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    status ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO orders (user_id, total, status)
VALUES (1, 230000, 'paid');

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    seller_id INT NOT NULL,
    price DECIMAL(10,2),
    qty INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

INSERT INTO order_items (order_id, product_id, seller_id, price, qty)
VALUES
(1, 1, 1, 150000, 1),
(1, 2, 1, 80000, 1);


CREATE TABLE disputes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    user_id INT,
    reason TEXT,
    status ENUM('open','resolved','rejected') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO disputes (order_id, user_id, reason)
VALUES (1, 1, 'Item not delivered');

CREATE TABLE ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    image_url VARCHAR(255),
    link VARCHAR(255),
    status TINYINT(1) DEFAULT 1
);

INSERT INTO ads (title, image_url, link)
VALUES
('Promo 1', 'ads/ad1.jpg', 'https://example.com'),
('Promo 2', 'ads/ad2.jpg', 'https://example.com');



CREATE TABLE seller_listing_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seller_id INT,
    amount DECIMAL(10,2),
    status ENUM('pending','paid','failed'),
    reference VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



CREATE TABLE affiliates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    code VARCHAR(50),
    earnings DECIMAL(10,2) DEFAULT 0
);


CREATE TABLE payment_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan VARCHAR(100) NOT NULL,
    duration VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    buyer_id INT,
    seller_id INT,
    product_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT,
    sender_type ENUM('buyer','seller'),
    sender_id INT,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);