-- Sanibook Migration Fix
-- Use this script to create the necessary parent tables (users, categories) 
-- BEFORE running sanibook_vol1.sql on the live server.

SET FOREIGN_KEY_CHECKS=0;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `org_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'WaSHVoice','admin@washvoice.org','$2y$10$xefid8mVvnXALzyehyvQFutp190jwfZhKAhuEzf.V7Fr67AW9kkPa','2023-10-27 07:00:00');
UNLOCK TABLES;

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `full_desc` text NOT NULL,
  `short_desc` varchar(255) DEFAULT NULL,
  `image_file` varchar(255) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `categories`
--

LOCK TABLES `categories` WRITE;
INSERT INTO `categories` VALUES 
(1,'Why Sanitation Matters','<p class=\"mb-4\">Sanitation is not just a health issue; it is a massive economic engine waiting to be unlocked. Every dollar invested in sanitation yields a $5.50 return in economic productivity.</p><p>We must shift the narrative from charity to investment.</p>',NULL,'ch1_why_matters.jpg','fa-chart-line','text-brand-dark','2026-02-03 02:06:33'),
(2,'Coverage Gaps','<p class=\"mb-4\">Despite progress, significant regional disparities exist. We need to identify and target the most underserved areas to ensure equitable access for all Kenyans.</p>',NULL,'ch2_access_gaps.jpg','fa-triangle-exclamation','text-brand-red','2026-02-03 02:06:33'),
(3,'Behavioral Barriers','<p class=\"mb-4\">Infrastructure alone is not enough. We must address the cultural and social norms that influence sanitation behaviors and adoption of new solutions.</p>',NULL,'ch3_behavioral_change.jpg','fa-brain','text-brand-gold','2026-02-03 02:06:33'),
(4,'Technology Adoption','<h3 class=\"text-xl font-bold mb-2\">The Approval Bottleneck</h3><p class=\"mb-4\">Innovators face a 2-3 year waiting period for certification. We need a Single-window clearance system to fast-track proven solutions.</p><h3 class=\"text-xl font-bold mb-2\">Single-Window Clearance</h3><p>By streamlining approvals, we can reduce deployment time by 60%.</p>',NULL,'ch4_tech_innovation.jpg','fa-microchip','text-blue-600','2026-02-03 02:06:33'),
(5,'Chronic Underfunding','<p>The sector currently faces a 60% financing gap. Innovative financing models like blended finance and sanitation bonds are essential to bridge this divide.</p>',NULL,'ch5_financing.jpg','fa-money-bill-wave','text-green-600','2026-02-03 02:06:33'),
(6,'Fragmented Governance','<p class=\"mb-4\">Overlapping mandates and lack of coordination hinder progress. We need a unified regulatory framework to streamline efforts and maximize impact.</p>',NULL,'ch6_governance.jpg','fa-scale-balanced','text-purple-600','2026-02-03 02:06:33'),
(7,'Data Deficiencies','<p class=\"mb-4\">We cannot manage what we cannot measure. Robust data systems are needed to track progress, identify gaps, and inform evidence-based decision making.</p>',NULL,'ch7_data_hub.jpg','fa-magnifying-glass-chart','text-gray-600','2026-02-03 02:06:33'),
(8,'Unmet Aspirations','<p class=\"mb-4\">Community voices must be at the center of sanitation solutions. We need to listen to their needs and aspirations to design interventions that truly work.</p>',NULL,'ch8_frontline_heroes.jpg','fa-hands-holding-circle','text-orange-600','2026-02-03 02:06:33'),
(9,'Stakeholder Portal','<p class=\"text-lg\">Welcome to the central registry for Kenya\'s sanitation actors. By registering your organization, you become visible to partners, funders, and policymakers.</p><p class=\"mt-4\">This data directly informs the National Sanitation Strategy.</p>',NULL,'ch9_stakeholder_portal.jpg','fa-users-viewfinder','text-teal-600','2026-02-03 02:06:33');
UNLOCK TABLES;

SET FOREIGN_KEY_CHECKS=1;
