-- ===============================================
-- SaniBook Subscription System Migration
-- Date: 2026-02-12
-- Description: Adds subscription packages, user subscriptions, and media uploads
-- ===============================================

USE `sanibook_vol1`;

-- 1. Subscription Packages Table
CREATE TABLE IF NOT EXISTS `subscription_packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price_kes` decimal(10,2) NOT NULL DEFAULT 0.00,
  `billing_period` varchar(20) NOT NULL DEFAULT 'annual' COMMENT 'annual, monthly',
  `quota_images` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `quota_videos` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `quota_audio` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `quota_text` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default subscription packages (annual billing in KES)
INSERT INTO `subscription_packages` (`name`, `price_kes`, `billing_period`, `quota_images`, `quota_videos`, `quota_audio`, `quota_text`) VALUES
('Free', 0.00, 'annual', 5, 0, 0, 10),
('Basic', 10000.00, 'annual', 50, 5, 10, NULL),
('Premium', 25000.00, 'annual', 200, 20, 50, NULL),
('Enterprise', 100000.00, 'annual', NULL, NULL, NULL, NULL);

-- 2. User Subscriptions Table
CREATE TABLE IF NOT EXISTS `user_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `package_id` int(11) NOT NULL,
  `status` enum('pending','approved','rejected','active','expired','cancelled') NOT NULL DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL COMMENT 'Admin user ID who approved',
  `approved_at` timestamp NULL DEFAULT NULL,
  `payment_status` enum('pending','simulated','completed','failed') NOT NULL DEFAULT 'pending',
  `payment_simulated_at` timestamp NULL DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `package_id` (`package_id`),
  KEY `status` (`status`),
  CONSTRAINT `fk_user_subscription_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_subscription_package` FOREIGN KEY (`package_id`) REFERENCES `subscription_packages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Media Uploads Table
CREATE TABLE IF NOT EXISTS `media_uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `media_type` enum('image','video','audio','text') NOT NULL,
  `file_path` varchar(512) DEFAULT NULL COMMENT 'NULL for text-only posts',
  `file_size` bigint(20) DEFAULT NULL COMMENT 'Size in bytes',
  `mime_type` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL COMMENT 'Link to categories table if available',
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `subscription_id` (`subscription_id`),
  KEY `media_type` (`media_type`),
  KEY `status` (`status`),
  CONSTRAINT `fk_media_upload_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_media_upload_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `user_subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Add subscription tracking to users table
ALTER TABLE `users` 
ADD COLUMN `current_subscription_id` int(11) DEFAULT NULL AFTER `created_at`,
ADD KEY `current_subscription_id` (`current_subscription_id`);

-- 5. Create view for active subscriptions with quota tracking
CREATE OR REPLACE VIEW `v_active_subscriptions` AS
SELECT 
    us.id AS subscription_id,
    us.user_id,
    u.org_name,
    u.email,
    sp.name AS package_name,
    sp.price_kes,
    sp.quota_images,
    sp.quota_videos,
    sp.quota_audio,
    sp.quota_text,
    us.start_date,
    us.end_date,
    COALESCE(SUM(CASE WHEN mu.media_type = 'image' THEN 1 ELSE 0 END), 0) AS used_images,
    COALESCE(SUM(CASE WHEN mu.media_type = 'video' THEN 1 ELSE 0 END), 0) AS used_videos,
    COALESCE(SUM(CASE WHEN mu.media_type = 'audio' THEN 1 ELSE 0 END), 0) AS used_audio,
    COALESCE(SUM(CASE WHEN mu.media_type = 'text' THEN 1 ELSE 0 END), 0) AS used_text
FROM user_subscriptions us
INNER JOIN users u ON us.user_id = u.id
INNER JOIN subscription_packages sp ON us.package_id = sp.id
LEFT JOIN media_uploads mu ON us.id = mu.subscription_id AND mu.status = 'approved'
WHERE us.status = 'active' 
  AND (us.end_date IS NULL OR us.end_date >= CURDATE())
GROUP BY us.id, us.user_id, u.org_name, u.email, sp.name, sp.price_kes, 
         sp.quota_images, sp.quota_videos, sp.quota_audio, sp.quota_text, 
         us.start_date, us.end_date;

-- ===============================================
-- DUMMY DATA FOR TESTING
-- ===============================================

-- 6. Insert test users (password: 'password123' - bcrypt hashed)
INSERT IGNORE INTO `users` (`org_name`, `email`, `password`) VALUES
('WaterAid Kenya', 'wateraid@test.com', '$2y$10$abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJ'),
('Sanitation Solutions Ltd', 'sanisolutions@test.com', '$2y$10$abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJ'),
('Clean Water Initiative', 'cleanwater@test.com', '$2y$10$abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJ'),
('Hygiene First Foundation', 'hygienefirst@test.com', '$2y$10$abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJ'),
('EcoSan Africa', 'ecosan@test.com', '$2y$10$abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJ');

-- 7. Insert test subscriptions (assuming user IDs 1-5)
INSERT INTO `user_subscriptions` (`user_id`, `package_id`, `status`, `approved_by`, `approved_at`, `payment_status`, `payment_simulated_at`, `start_date`, `end_date`) VALUES
-- User 1: Active Enterprise subscription
(1, 4, 'active', 1, NOW(), 'completed', NOW(), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
-- User 2: Active Premium subscription
(2, 3, 'active', 1, NOW(), 'completed', NOW(), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
-- User 3: Active Basic subscription
(3, 2, 'active', 1, NOW(), 'completed', NOW(), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR)),
-- User 4: Pending approval (Basic package)
(4, 2, 'pending', NULL, NULL, 'pending', NULL, NULL, NULL),
-- User 5: Free tier (active)
(5, 1, 'active', 1, NOW(), 'completed', NOW(), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR));

-- 8. Update users table to link current subscriptions
UPDATE `users` SET `current_subscription_id` = 1 WHERE `id` = 1;
UPDATE `users` SET `current_subscription_id` = 2 WHERE `id` = 2;
UPDATE `users` SET `current_subscription_id` = 3 WHERE `id` = 3;
UPDATE `users` SET `current_subscription_id` = 5 WHERE `id` = 5;

-- 9. Insert dummy media uploads (assuming subscription IDs 1-5)
INSERT INTO `media_uploads` (`user_id`, `subscription_id`, `media_type`, `file_path`, `file_size`, `mime_type`, `title`, `description`, `status`, `uploaded_at`, `approved_at`) VALUES
-- Enterprise user (unlimited quota)
(1, 1, 'image', '/uploads/images/enterprise_toilet_system.jpg', 245678, 'image/jpeg', 'Modern Toilet Installation', 'Commercial toilet system in Nairobi mall', 'approved', NOW(), NOW()),
(1, 1, 'video', '/uploads/videos/wastewater_treatment.mp4', 15234567, 'video/mp4', 'Wastewater Treatment Process', 'Time-lapse of treatment plant operations', 'approved', NOW(), NOW()),
(1, 1, 'text', NULL, NULL, NULL, 'Policy Brief on Urban Sanitation', 'Recommendations for improving urban sanitation infrastructure in Kenyan cities', 'approved', NOW(), NOW()),

-- Premium user (200 images, 20 videos, 50 audio)
(2, 2, 'image', '/uploads/images/community_latrine.jpg', 189456, 'image/jpeg', 'Community Latrine Project', 'Shared sanitation facility in informal settlement', 'approved', NOW(), NOW()),
(2, 2, 'image', '/uploads/images/handwashing_station.jpg', 156789, 'image/jpeg', 'Handwashing Station Design', 'Low-cost handwashing station prototype', 'approved', NOW(), NOW()),
(2, 2, 'video', '/uploads/videos/hygiene_training.mp4', 8945612, 'video/mp4', 'Community Hygiene Training', 'Workshop on proper sanitation practices', 'approved', NOW(), NOW()),
(2, 2, 'audio', '/uploads/audio/radio_interview.mp3', 4567890, 'audio/mpeg', 'Radio Interview on MHM', 'Discussion about menstrual hygiene management in schools', 'approved', NOW(), NOW()),

-- Basic user (50 images, 5 videos, 10 audio)
(3, 3, 'image', '/uploads/images/pit_latrine.jpg', 234567, 'image/jpeg', 'Improved Pit Latrine', 'VIP latrine construction in rural area', 'approved', NOW(), NOW()),
(3, 3, 'text', NULL, NULL, NULL, 'Case Study: School WASH Program', 'Implementation report from school WASH intervention in Kisumu County', 'approved', NOW(), NOW()),
(3, 3, 'text', NULL, NULL, NULL, 'Budget Analysis for Sanitation Projects', 'Cost breakdown for various sanitation technologies', 'pending', NOW(), NULL),

-- Free user (5 images, 0 videos, 0 audio, 10 text)
(5, 5, 'image', '/uploads/images/water_point.jpg', 123456, 'image/jpeg', 'Water Access Point', 'Community water kiosk', 'approved', NOW(), NOW()),
(5, 5, 'image', '/uploads/images/soap_distribution.jpg', 98765, 'image/jpeg', 'Soap Distribution Event', 'COVID-19 hygiene kit distribution', 'approved', NOW(), NOW()),
(5, 5, 'text', NULL, NULL, NULL, 'Monthly Progress Report', 'Summary of WASH activities for January 2026', 'approved', NOW(), NOW()),
(5, 5, 'text', NULL, NULL, NULL, 'Community Feedback Survey Results', 'Findings from user satisfaction survey', 'approved', NOW(), NOW());

-- ===============================================
-- Migration Complete
-- ===============================================
-- Next Steps:
-- 1. Create admin interface for approving subscription requests
-- 2. Build package selection page for users
-- 3. Implement media upload form with quota validation
-- 4. Add payment simulation workflow
-- 
-- Test Credentials:
-- Users: wateraid@test.com, sanisolutions@test.com, cleanwater@test.com, hygienefirst@test.com, ecosan@test.com
-- Password (all users): password123
-- ===============================================
