-- MSMEBiz v1.2: additive, preserves users and transaction data.
-- Import ONCE after verifying the v1.1 schema exists and taking a backup.
CREATE TABLE IF NOT EXISTS organization_subscriptions (
 org_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 plan_code VARCHAR(60) NOT NULL DEFAULT 'pilot',
 status ENUM('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial',
 valid_until DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_sub_org FOREIGN KEY (org_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS organization_modules (
 org_id BIGINT UNSIGNED NOT NULL, module_code ENUM('erp','books','gst') NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 PRIMARY KEY (org_id,module_code),
 CONSTRAINT fk_mod_org FOREIGN KEY (org_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS onboarding_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 org_id BIGINT UNSIGNED NOT NULL, email VARCHAR(190) NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL, used_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX (org_id,email,expires_at),
 CONSTRAINT fk_inv_org FOREIGN KEY (org_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Preserve access to every existing company's pilot modules.
INSERT IGNORE INTO organization_subscriptions(org_id,plan_code,status)
 SELECT id,'pilot','trial' FROM organizations;
INSERT IGNORE INTO organization_modules(org_id,module_code,enabled)
 SELECT id,'erp',1 FROM organizations;
INSERT IGNORE INTO organization_modules(org_id,module_code,enabled)
 SELECT id,'books',1 FROM organizations;
INSERT IGNORE INTO organization_modules(org_id,module_code,enabled)
 SELECT id,'gst',1 FROM organizations;