CREATE DATABASE IF NOT EXISTS kapu_mama CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE kapu_mama;
CREATE TABLE 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);
CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NULL,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('super_admin','tenant_admin','user') NOT NULL DEFAULT 'tenant_admin',status ENUM('active','inactive') NOT NULL DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_users_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE);
CREATE TABLE people (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,full_name VARCHAR(160) NOT NULL,phone VARCHAR(30) NOT NULL,email VARCHAR(190) NULL,address VARCHAR(255) NULL,notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,deleted_at TIMESTAMP NULL,INDEX idx_people_tenant_phone(tenant_id,phone),UNIQUE KEY uq_people_tenant_id(tenant_id,id),CONSTRAINT fk_people_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE);
CREATE TABLE categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,sender_id VARCHAR(50) NULL,description VARCHAR(255) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_categories_tenant_name(tenant_id,name),UNIQUE KEY uq_categories_tenant_id(tenant_id,id),CONSTRAINT fk_categories_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE);
CREATE TABLE pledges (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,person_id INT UNSIGNED NOT NULL,category_id INT UNSIGNED NULL,pledge_number VARCHAR(50) NOT NULL,amount DECIMAL(15,2) NOT NULL DEFAULT 0,pledge_date DATE NOT NULL,due_date DATE NULL,description VARCHAR(255) NULL,notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_pledges_tenant_number(tenant_id,pledge_number),UNIQUE KEY uq_pledges_tenant_id(tenant_id,id),INDEX idx_pledges_tenant(tenant_id),CONSTRAINT fk_pledges_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_pledge_person FOREIGN KEY(person_id) REFERENCES people(id),CONSTRAINT fk_pledge_tenant_person FOREIGN KEY(tenant_id,person_id) REFERENCES people(tenant_id,id),CONSTRAINT fk_pledge_category FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL,CONSTRAINT fk_pledge_tenant_category FOREIGN KEY(tenant_id,category_id) REFERENCES categories(tenant_id,id) ON DELETE SET NULL);
CREATE TABLE payments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,pledge_id INT UNSIGNED NOT NULL,person_id INT UNSIGNED NOT NULL,amount DECIMAL(15,2) NOT NULL,payment_date DATE NOT NULL,payment_method VARCHAR(40) NOT NULL DEFAULT 'Other',reference_number VARCHAR(100) NULL,notes TEXT NULL,recorded_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_payments_tenant(tenant_id),CONSTRAINT fk_payments_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_payment_pledge FOREIGN KEY(pledge_id) REFERENCES pledges(id),CONSTRAINT fk_payment_tenant_pledge FOREIGN KEY(tenant_id,pledge_id) REFERENCES pledges(tenant_id,id),CONSTRAINT fk_payment_person FOREIGN KEY(person_id) REFERENCES people(id),CONSTRAINT fk_payment_tenant_person FOREIGN KEY(tenant_id,person_id) REFERENCES people(tenant_id,id),CONSTRAINT fk_payment_user FOREIGN KEY(recorded_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE sms_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,person_id INT UNSIGNED NULL,phone VARCHAR(30) NOT NULL,message TEXT NOT NULL,sender_id VARCHAR(50) NOT NULL,sms_type VARCHAR(40) NOT NULL,api_response TEXT NULL,provider_message_id VARCHAR(150) NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',sent_at TIMESTAMP NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_sms_tenant(tenant_id),CONSTRAINT fk_sms_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_sms_person FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL);
CREATE TABLE settings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NOT NULL,setting_key VARCHAR(100) NOT NULL,setting_value TEXT NULL,UNIQUE KEY uq_settings_tenant_key(tenant_id,setting_key),CONSTRAINT fk_settings_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE);
CREATE TABLE activity_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id INT UNSIGNED NULL,user_id INT UNSIGNED NULL,action VARCHAR(80) NOT NULL,description TEXT NULL,ip_address VARCHAR(45) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_activity_tenant(tenant_id),CONSTRAINT fk_activity_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_activity_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
INSERT INTO tenants(name,slug,status,sender_id) VALUES('KAPU LA MAMA','kapu-la-mama','active','KAPU LA MAMA') ON DUPLICATE KEY UPDATE name=VALUES(name);
SET @tenant_id=(SELECT id FROM tenants WHERE slug='kapu-la-mama');
INSERT INTO categories(tenant_id,name,sender_id,description) VALUES(@tenant_id,'KAPU LA MAMA','KAPU LA MAMA','KAPU LA MAMA pledges'),(@tenant_id,'SENDOFF','','Send-off pledges') ON DUPLICATE KEY UPDATE sender_id=VALUES(sender_id),description=VALUES(description);
INSERT INTO settings(tenant_id,setting_key,setting_value) VALUES(@tenant_id,'organization_name','KAPU LA MAMA'),(@tenant_id,'currency','TZS'),(@tenant_id,'reminder_template','Ndugu [NAME], tunakukumbusha kuhusu ahadi yako ya TZS [PLEDGED]. Umeshalipa TZS [PAID] na salio lako ni TZS [BALANCE]. Tunakushukuru kwa ushiriki wako. Mungu akubariki.'),(@tenant_id,'payment_template','Ndugu [NAME], tumepokea malipo yako ya TZS [PAYMENT] kwa ajili ya ahadi yako. Jumla uliyolipa hadi sasa ni TZS [PAID] na salio lako ni TZS [BALANCE]. Tunakushukuru sana. Mungu akubariki.') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);