USE kapu_mama;
-- Run this only against an EXISTING installation using the original single-tenant schema.
-- Backup the database first. This migration creates one tenant and attaches all existing records to it.
START TRANSACTION;
CREATE TABLE IF NOT EXISTS tenants (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,slug VARCHAR(160) NOT NULL UNIQUE,status ENUM('active','inactive') NOT NULL DEFAULT 'active',sender_id VARCHAR(50) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO tenants(name,slug,status,sender_id) VALUES('KAPU LA MAMA','kapu-la-mama','active','KAPU LA MAMA') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SET @tid=LAST_INSERT_ID();
ALTER TABLE users ADD COLUMN tenant_id INT UNSIGNED NULL, ADD COLUMN role ENUM('super_admin','tenant_admin','user') NOT NULL DEFAULT 'tenant_admin', ADD COLUMN status ENUM('active','inactive') NOT NULL DEFAULT 'active';
UPDATE users SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE people ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE people SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE categories ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE categories SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE pledges ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE pledges SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE payments ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE payments SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE sms_logs ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE sms_logs SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE settings ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE settings SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE activity_logs ADD COLUMN tenant_id INT UNSIGNED NULL;
UPDATE activity_logs SET tenant_id=@tid WHERE tenant_id IS NULL;
ALTER TABLE users MODIFY tenant_id INT UNSIGNED NULL;
ALTER TABLE people MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE categories MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE pledges MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE payments MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE sms_logs MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE settings MODIFY tenant_id INT UNSIGNED NOT NULL;
ALTER TABLE activity_logs MODIFY tenant_id INT UNSIGNED NULL;
ALTER TABLE categories DROP INDEX name, ADD UNIQUE KEY uq_categories_tenant_name(tenant_id,name);
ALTER TABLE pledges DROP INDEX pledge_number, ADD UNIQUE KEY uq_pledges_tenant_number(tenant_id,pledge_number);
ALTER TABLE settings DROP PRIMARY KEY, ADD COLUMN id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, ADD UNIQUE KEY uq_settings_tenant_key(tenant_id,setting_key);
ALTER TABLE activity_logs ADD INDEX idx_activity_tenant(tenant_id);
ALTER TABLE people ADD UNIQUE KEY uq_people_tenant_id(tenant_id,id);
ALTER TABLE categories ADD UNIQUE KEY uq_categories_tenant_id(tenant_id,id);
ALTER TABLE pledges ADD UNIQUE KEY uq_pledges_tenant_id(tenant_id,id);
ALTER TABLE pledges ADD CONSTRAINT fk_pledge_tenant_person FOREIGN KEY(tenant_id,person_id) REFERENCES people(tenant_id,id);
ALTER TABLE pledges ADD CONSTRAINT fk_pledge_tenant_category FOREIGN KEY(tenant_id,category_id) REFERENCES categories(tenant_id,id) ON DELETE SET NULL;
ALTER TABLE payments ADD CONSTRAINT fk_payment_tenant_pledge FOREIGN KEY(tenant_id,pledge_id) REFERENCES pledges(tenant_id,id);
ALTER TABLE payments ADD CONSTRAINT fk_payment_tenant_person FOREIGN KEY(tenant_id,person_id) REFERENCES people(tenant_id,id);
ALTER TABLE users ADD CONSTRAINT fk_users_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE people ADD CONSTRAINT fk_people_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE categories ADD CONSTRAINT fk_categories_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE pledges ADD CONSTRAINT fk_pledges_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE payments ADD CONSTRAINT fk_payments_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE sms_logs ADD CONSTRAINT fk_sms_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE settings ADD CONSTRAINT fk_settings_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
ALTER TABLE activity_logs ADD CONSTRAINT fk_activity_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;
UPDATE users SET role='tenant_admin' WHERE role IS NULL OR role='user';
COMMIT;