49 lines
1.8 KiB
SQL
49 lines
1.8 KiB
SQL
-- 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;
|