-- Add Stripe Configuration table
CREATE TABLE IF NOT EXISTS stripe_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default placeholder keys (empty values)
INSERT IGNORE INTO stripe_config (setting_key, setting_value) VALUES 
('stripe_publishable_key', ''),
('stripe_secret_key', ''),
('stripe_webhook_secret', ''),
('currency', 'GBP');

-- Create Credit Packages table
CREATE TABLE IF NOT EXISTS credit_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    credits INT NOT NULL,
    price_amount DECIMAL(10, 2) NOT NULL,
    price_currency VARCHAR(3) DEFAULT 'GBP',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert initial credit packages
INSERT IGNORE INTO credit_packages (name, description, credits, price_amount) VALUES 
('Single LPA', 'Purchase 1 LPA credit', 1, 39.00),
('Standard Pack', 'Purchase 3 LPA credits', 3, 99.00),
('Value Pack', 'Purchase 5 LPA credits', 5, 149.00);

-- Create Invoices table
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'GBP',
    status VARCHAR(50) DEFAULT 'unpaid', -- unpaid, paid, cancelled
    stripe_payment_intent_id VARCHAR(255),
    credits_added INT DEFAULT 0,
    items_json TEXT, -- JSON representation of items purchased
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;