-- Database: sanibook_vol1
CREATE DATABASE IF NOT EXISTS `sanibook_vol1`;
USE `sanibook_vol1`;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+03:00";

-- --------------------------------------------------------

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

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) 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 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--
-- Password is 'password123' hashed with bcrypt
--
INSERT INTO `users` (`id`, `org_name`, `email`, `password`, `created_at`) VALUES
(1, 'WaSHVoice', 'admin@washvoice.org', '$2b$12$bkYjp/dr9T.4S465.U5yAD4u5TSiywD7.WDGR5j6XhAum7dA', '2023-10-27 10:00:00')
ON DUPLICATE KEY UPDATE id=id;

-- --------------------------------------------------------

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

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) 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 DEFAULT CHARSET=utf8mb4;

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

INSERT INTO `categories` (`id`, `title`, `full_desc`, `image_file`, `icon`, `color`) 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>', 'ch1_why_matters.jpg', 'fa-chart-line', 'text-brand-dark'),
(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>', 'ch2_access_gaps.jpg', 'fa-triangle-exclamation', 'text-brand-red'),
(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>', 'ch3_behavioral_change.jpg', 'fa-brain', 'text-brand-gold'),
(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>', 'ch4_tech_innovation.jpg', 'fa-microchip', 'text-blue-600'),
(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>', 'ch5_financing.jpg', 'fa-money-bill-wave', 'text-green-600'),
(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>', 'ch6_governance.jpg', 'fa-scale-balanced', 'text-purple-600'),
(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>', 'ch7_data_hub.jpg', 'fa-magnifying-glass-chart', 'text-gray-600'),
(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>', 'ch8_frontline_heroes.jpg', 'fa-hands-holding-circle', 'text-orange-600'),
(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>', 'ch9_stakeholder_portal.jpg', 'fa-users-viewfinder', 'text-teal-600')
ON DUPLICATE KEY UPDATE title=VALUES(title), full_desc=VALUES(full_desc), icon=VALUES(icon), color=VALUES(color);

-- --------------------------------------------------------

--
-- Table structure for table `content`
--

CREATE TABLE IF NOT EXISTS `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `type` enum('story','chronicle') NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` mediumtext NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `content`
--
ALTER TABLE `content`
  ADD CONSTRAINT `content_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `content_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);
COMMIT;
