-- Hausa Quotes Database Schema
-- Database Name: hausa_quotes

CREATE DATABASE IF NOT EXISTS `hausa_quotes` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `hausa_quotes`;

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

-- Table structure for table `admins`
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table structure for table `categories`
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table structure for table `quotes`
CREATE TABLE IF NOT EXISTS `quotes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL,
  `quote` TEXT NOT NULL,
  `status` ENUM('Published', 'Draft') NOT NULL DEFAULT 'Published',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table structure for table `settings`
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `app_name` VARCHAR(100) NOT NULL DEFAULT 'Hausa Quotes',
  `logo` VARCHAR(255) NULL,
  `about` TEXT NOT NULL,
  `privacy_policy` TEXT NOT NULL,
  `terms_conditions` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- Insert Default Admin User
-- Username: admin
-- Password: password123 (bcrypt hash: $2y$10$wLDRjM.E03j3L95VlXhFneSFrX9Vl5F4v8C8nSgU5YfXp08zby.U6)
INSERT INTO `admins` (`username`, `password`) VALUES
('admin', '$2y$10$wLDRjM.E03j3L95VlXhFneSFrX9Vl5F4v8C8nSgU5YfXp08zby.U6')
ON DUPLICATE KEY UPDATE `username`=`username`;

-- Insert Default Settings Record (Only one should exist)
INSERT INTO `settings` (`id`, `app_name`, `logo`, `about`, `privacy_policy`, `terms_conditions`) VALUES
(1, 'Hausa Quotes', 'logo.png', 'Barka da zuwa Hausa Quotes - babban dandali na samun zantuka na hikima, karfafan gwiwa, da nishadi a cikin harshen Hausa.', 'Kariyar sirri na da matukar mahimmanci a gare mu. Ba ma tattara bayanan sirri na masu amfani da wannan manhaja ba tare da amincewarsu ba.', 'Yin amfani da wannan manhaja yana nufin kun amince da dukkan sharuɗɗanmu na amfani da sabis.')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- Insert Default Categories
INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Hikima da Ilimi'),
(2, 'Soyayya da Rayuwa'),
(3, 'Karfafa Gwiwa'),
(4, 'Barkwanci da Nishadi')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- Insert Default Quotes
INSERT INTO `quotes` (`category_id`, `quote`, `status`) VALUES
(1, 'Wanda bai ji kiran ruwa ba, ko ya ji kiran kwado.', 'Published'),
(1, 'Idan kace kura tana da kyau, to kace kura ba ta da kashi.', 'Published'),
(2, 'Aboki na gari ya fi dan uwa na banza.', 'Published'),
(2, 'Soyayya ruwan zuma ce, tana bukatar kulawa da gaskiya.', 'Published'),
(3, 'Hakuri shi ne babban makamin nasara a rayuwa.', 'Published'),
(3, 'Kada ka taba karaya domin dukkan dare yana da wayewar gari.', 'Published'),
(4, 'Waiwaye tafiyar kwado, in ji mai nema.', 'Published'),
(4, 'Idan kaga gemun dan uwanka ya kama wuta, shafawa naka ruwa.', 'Published')
ON DUPLICATE KEY UPDATE `id`=`id`;