-- Marketing Buy-Back Schema V2 (With Demo Data)

-- 1. Support Tables (If they don't exist, for standalone testing)
CREATE TABLE IF NOT EXISTS user_account (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(50) UNIQUE,
    password VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    status ENUM('active', 'inactive') DEFAULT 'active',
    is_activated BOOLEAN DEFAULT TRUE,
    pin VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS user_assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(50),
    email VARCHAR(255),
    amount DECIMAL(15,8) DEFAULT 0.00,  -- Crypto
    bdt DECIMAL(10,2) DEFAULT 0.00,     -- BDT
    last_adjustment_date DATETIME
    -- Foreign key omitted for flexibility in dev
);

-- 2. Marketing System Tables
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    specs TEXT,
    image_url VARCHAR(255),
    profit_percentage DECIMAL(5,2) DEFAULT 52.00,
    stock INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    product_id INT NOT NULL,
    receipt_number VARCHAR(50) UNIQUE,
    purchase_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    payment_method ENUM('available_bdt', 'available_crypto', 'bkash') NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    choice INT DEFAULT 0,  -- 0: none, 1: collect_with_profit, 2: buy_back
    choice_date DATETIME,
    profit_start_date DATETIME,
    total_profit DECIMAL(10,2) DEFAULT 0.00,
    weekly_profit DECIMAL(10,2) DEFAULT 0.00,
    weeks_total INT DEFAULT 52,
    weeks_completed INT DEFAULT 0,
    total_withdrawn DECIMAL(10,2) DEFAULT 0.00,
    collected BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS profit_withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_id INT NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    week INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    withdrawal_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    method ENUM('mobile_banking', 'bank_transfer', 'crypto') NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE CASCADE
);

-- 3. DEMO PRODUCTS
-- Clearing old products to avoid duplicates if re-running
TRUNCATE TABLE products;

INSERT INTO products (name, description, price, specs, profit_percentage, stock, image_url) VALUES
('Premium Ultra Gadget', 'High-end electronic gadget with premium finish.', 100000.00, 'Color: Black, Storage: 1TB', 52.00, 50, 'https://cdn.pixabay.com/photo/2014/10/23/18/05/phone-500223_1280.jpg'),
('Gold Investment Bond', 'Secure gold bond backed by real reserves.', 50000.00, '24K Gold equivalent', 52.00, 100, 'https://cdn.pixabay.com/photo/2016/09/19/22/46/bullion-1681284_1280.jpg'),
('Silver Package', 'Entry level investment package.', 10000.00, 'Standard features', 52.00, 200, 'https://cdn.pixabay.com/photo/2016/09/19/22/46/silver-1681282_1280.jpg'),
('Real Estate Micro-Share', 'Invest in premium city properties.', 250000.00, 'Location: Banani, Dhaka\nType: Commercial', 60.00, 20, 'https://cdn.pixabay.com/photo/2017/04/10/22/28/residence-2219972_1280.jpg'),
('Green Energy Fund', 'Sustainable solar power project.', 75000.00, 'Capacity: 5KW Share\nLocation: Solar Park', 48.00, 50, 'https://cdn.pixabay.com/photo/2017/09/12/13/21/photovoltaic-system-2742302_1280.jpg'),
('Tech Startup Equity', 'Early stage funding for promising AI startups.', 500000.00, 'Sector: AI & Blockchain\nRisk: High', 75.00, 10, 'https://cdn.pixabay.com/photo/2018/05/08/08/44/artificial-intelligence-3382507_1280.jpg');

-- 4. DEMO USER (Optional, for testing if needed)
-- INSERT IGNORE INTO user_account (account_number, email, first_name, last_name, password) VALUES 
-- ('9999888877', 'demo@chainpay.com', 'Demo', 'User', '123456');

-- INSERT IGNORE INTO user_assets (account_number, email, amount, bdt) VALUES 
-- ('9999888877', 'demo@chainpay.com', 1000.00, 500000.00);