VPS 4 Builder
52 lines
2.0 KiB
SQL
52 lines
2.0 KiB
SQL
-- financeiro plugin: subscription plans, invoices, commissions
|
|
|
|
CREATE TABLE IF NOT EXISTS subscription_plans (
|
|
id VARCHAR(50) PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
monthly_price DECIMAL(10,2) NOT NULL,
|
|
min_employees INT DEFAULT 1,
|
|
max_employees INT NULL,
|
|
custom_branding BOOLEAN DEFAULT FALSE,
|
|
priority_support BOOLEAN DEFAULT FALSE,
|
|
analytics_level VARCHAR(20) DEFAULT 'basic',
|
|
custom_benefits BOOLEAN DEFAULT FALSE,
|
|
api_access BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Seed default plans (idempotent via INSERT IGNORE)
|
|
INSERT IGNORE INTO subscription_plans (id, name, monthly_price, min_employees, max_employees, custom_branding, priority_support, analytics_level, custom_benefits, api_access) VALUES
|
|
('basic', 'Básico', 299.00, 1, 50, FALSE, FALSE, 'basic', FALSE, FALSE),
|
|
('pro', 'Profissional', 799.00, 51, 250, TRUE, TRUE, 'advanced', TRUE, FALSE),
|
|
('enterprise', 'Enterprise', 1999.00, 251, NULL, TRUE, TRUE, 'advanced', TRUE, TRUE);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoices (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
partner_id VARCHAR(255) NOT NULL,
|
|
plan_id VARCHAR(50) NOT NULL,
|
|
amount DECIMAL(10,2) NOT NULL,
|
|
issue_date DATE NOT NULL,
|
|
due_date DATE NOT NULL,
|
|
status ENUM('Paga', 'Pendente', 'Atrasada') NOT NULL DEFAULT 'Pendente',
|
|
paid_at TIMESTAMP NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_invoice_partner (partner_id),
|
|
INDEX idx_invoice_status (status),
|
|
INDEX idx_invoice_due (due_date)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS commissions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
benefit_id VARCHAR(255) NOT NULL,
|
|
benefit_title VARCHAR(255),
|
|
partner_id VARCHAR(255) NOT NULL,
|
|
user_id INT NOT NULL,
|
|
user_name VARCHAR(255),
|
|
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
transaction_value DECIMAL(10,2) NOT NULL,
|
|
commission_rate DECIMAL(5,2) NOT NULL,
|
|
commission_value DECIMAL(10,2) NOT NULL,
|
|
INDEX idx_commission_partner (partner_id),
|
|
INDEX idx_commission_date (transaction_date)
|
|
)
|