-- MSMEBiz v1.5 (additive). Run after v1.3 migration; back up DB first.
CREATE TABLE IF NOT EXISTS saas_pricing_plans (
 code VARCHAR(60) PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
 description VARCHAR(255) NOT NULL DEFAULT '',
 is_public TINYINT(1) NOT NULL DEFAULT 0,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO saas_pricing_plans(code,name,monthly_price,description,is_public) VALUES
 ('pilot','Pilot',0,'Invitation-only evaluation',0),
 ('starter','Starter',0,'Price not configured',0),
 ('business','Business',0,'Price not configured',0),
 ('professional','Professional',0,'Price not configured',0);
CREATE TABLE IF NOT EXISTS saas_billing_invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL,
 invoice_ref VARCHAR(64) NOT NULL UNIQUE,
 description VARCHAR(255) NOT NULL,
 issue_date DATE NOT NULL,
 due_date DATE NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'INR',
 status ENUM('draft','issued','void') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_bill_org(org_id,id),
 FOREIGN KEY(org_id) REFERENCES organizations(id),
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS saas_billing_payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 invoice_id BIGINT UNSIGNED NOT NULL,
 org_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 paid_on DATE NOT NULL,
 reference VARCHAR(100) NOT NULL,
 note VARCHAR(255) NOT NULL DEFAULT '',
 entered_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_payment_org(org_id,invoice_id),
 FOREIGN KEY(invoice_id) REFERENCES saas_billing_invoices(id),
 FOREIGN KEY(org_id) REFERENCES organizations(id),
 FOREIGN KEY(entered_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;