-- =====================================================
-- Telegram Marketplace Bot — Database Schema
-- Import this in phpMyAdmin or via: mysql -u user -p db < install.sql
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS disputes;
DROP TABLE IF EXISTS withdraws;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS escrow_orders;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product_items;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS referrals;
DROP TABLE IF EXISTS sellers;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS admins;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------- USERS ----------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(64),
    first_name VARCHAR(128),
    wallet_balance DECIMAL(12,2) DEFAULT 0,
    locked_balance DECIMAL(12,2) DEFAULT 0,
    available_balance DECIMAL(12,2) DEFAULT 0,
    is_seller TINYINT(1) DEFAULT 0,
    is_banned TINYINT(1) DEFAULT 0,
    referred_by BIGINT NULL,
    flow_state VARCHAR(64) DEFAULT NULL,
    flow_data TEXT DEFAULT NULL,
    rating DECIMAL(3,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_telegram (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ADMINS ----------
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin: username=admin password=admin123 (CHANGE IT!)
INSERT INTO admins (username, password_hash) VALUES
('admin', '$2y$10$8daJtjdoLpV8IOpp1Lzbr.s4qVLfHOWPOs0e3M0XEKBjrUD53.j5u');

-- ---------- SETTINGS ----------
CREATE TABLE settings (
    key_name VARCHAR(64) PRIMARY KEY,
    value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (key_name, value) VALUES
('commission_percent', '5'),
('min_withdraw', '100'),
('min_deposit', '50'),
('auto_confirm_hours', '72'),
('bkash_number', '01XXXXXXXXX'),
('nagad_number', '01XXXXXXXXX'),
('rocket_number', '01XXXXXXXXX'),
('vaipay_api_key', ''),
('vaipay_secret_key', ''),
('vaipay_brand_key', ''),
('support_username', '@yoursupport'),
('referral_bonus', '10');

-- ---------- PRODUCTS ----------
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    seller_id INT NOT NULL,
    category VARCHAR(64) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(12,2) NOT NULL,
    stock INT DEFAULT 0,
    sold INT DEFAULT 0,
    status ENUM('active','paused','removed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category, status),
    INDEX idx_seller (seller_id),
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PRODUCT ITEMS (one per stock unit / account credential) ----------
CREATE TABLE product_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    payload TEXT NOT NULL,
    is_sold TINYINT(1) DEFAULT 0,
    sold_to_order INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id, is_sold),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ORDERS ----------
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    buyer_id INT NOT NULL,
    seller_id INT NOT NULL,
    product_id INT NOT NULL,
    item_id INT,
    amount DECIMAL(12,2) NOT NULL,
    commission DECIMAL(12,2) DEFAULT 0,
    delivered_payload TEXT,
    status ENUM('locked','delivered','confirmed','disputed','refunded','cancelled') DEFAULT 'locked',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_at TIMESTAMP NULL,
    INDEX idx_buyer (buyer_id),
    INDEX idx_seller (seller_id),
    INDEX idx_status (status),
    FOREIGN KEY (buyer_id) REFERENCES users(id),
    FOREIGN KEY (seller_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ESCROW ORDERS (audit log per state change) ----------
CREATE TABLE escrow_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    action VARCHAR(32) NOT NULL,
    amount DECIMAL(12,2),
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PAYMENTS (deposits) ----------
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    method ENUM('bkash','nagad','rocket','zinipay','manual') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    txn_id VARCHAR(128),
    sender_number VARCHAR(32),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- WITHDRAWS ----------
CREATE TABLE withdraws (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    method ENUM('bkash','nagad','rocket') NOT NULL,
    account VARCHAR(64) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending','paid','rejected') DEFAULT 'pending',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- DISPUTES ----------
CREATE TABLE disputes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    opened_by INT NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('open','resolved_buyer','resolved_seller') DEFAULT 'open',
    resolution TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (opened_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- REFERRALS ----------
CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL UNIQUE,
    bonus_paid DECIMAL(12,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id),
    FOREIGN KEY (referred_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- NOTIFICATIONS ----------
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Done!