-- phpMyAdmin SQL Dump
-- Connect Store — Updated Schema v2
-- New features:
--   • products.is_free         TINYINT  — mark a product as free (skips payment)
--   • product_images table     — multiple images per product with sort_order
--   • site_settings.currency_symbol — admin-configurable symbol (e.g. $, £, ₦, €)
--   • Charset upgraded to utf8mb4 throughout

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------
-- admins
-- --------------------------------------------------------
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('superadmin','admin') DEFAULT 'admin',
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=2;

INSERT INTO `admins` VALUES
(1,'Admin','admin@nex.com','$2y$10$OV.38ugVKMM.kXH0N4Sst.r0ZeWXkK4jQW1jwPQ.kNQehaFh99B2K','superadmin','2026-03-07 03:31:06','2026-02-27 10:14:43');

-- --------------------------------------------------------
-- carousel
-- --------------------------------------------------------
CREATE TABLE `carousel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `subtitle` varchar(300) DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `button_text` varchar(100) DEFAULT NULL,
  `button_url` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- notifications
-- --------------------------------------------------------
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `recipient_type` enum('user','admin') NOT NULL DEFAULT 'user',
  `recipient_id` int(11) NOT NULL,
  `type` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `data` longtext DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `recipient` (`recipient_type`,`recipient_id`),
  KEY `is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- orders
-- --------------------------------------------------------
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `status` enum('Pending','Processing','Completed','Cancelled','Refunded') DEFAULT 'Pending',
  `total` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `reference` varchar(100) DEFAULT NULL,
  `payment_status` enum('unpaid','paid','partially_paid','refunded') DEFAULT 'unpaid',
  `payment_method` varchar(50) DEFAULT NULL,
  `payment_reference` varchar(200) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- order_items
-- --------------------------------------------------------
CREATE TABLE `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT 1,
  `price` decimal(10,2) DEFAULT NULL,
  `product_name` varchar(150) DEFAULT NULL,
  `product_type` enum('physical_product','software_product','physical_service','software_service') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- payment_verifications
-- --------------------------------------------------------
CREATE TABLE `payment_verifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `verified_by` int(11) NOT NULL,
  `method` enum('lenco','manual_override') DEFAULT 'manual_override',
  `amount` decimal(10,2) DEFAULT NULL,
  `reference` varchar(200) DEFAULT NULL,
  `proof_image` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `verified_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `verified_by` (`verified_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- products
-- --------------------------------------------------------
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `category` varchar(100) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `slug` varchar(200) DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `sale_price` decimal(10,2) DEFAULT NULL,
  `is_free` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'When 1 the item is free; price is ignored at checkout',
  `type` enum('physical_product','software_product','physical_service','software_service') NOT NULL DEFAULT 'physical_product',
  `gallery` longtext DEFAULT NULL COMMENT 'Legacy field — use product_images table instead',
  `sku` varchar(100) DEFAULT NULL,
  `stock_qty` int(11) DEFAULT NULL,
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` varchar(100) DEFAULT NULL,
  `download_url` varchar(500) DEFAULT NULL,
  `license_type` varchar(100) DEFAULT NULL,
  `version` varchar(50) DEFAULT NULL,
  `platform` varchar(100) DEFAULT NULL,
  `duration` varchar(100) DEFAULT NULL,
  `delivery_method` varchar(100) DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `created_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `products_ibfk_1` (`created_by`),
  KEY `idx_is_free` (`is_free`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=4;

INSERT INTO `products` (`id`,`name`,`description`,`price`,`category`,`image`,`created_at`,`slug`,`short_description`,`sale_price`,`is_free`,`type`,`gallery`,`sku`,`stock_qty`,`weight`,`dimensions`,`download_url`,`license_type`,`version`,`platform`,`duration`,`delivery_method`,`is_featured`,`status`,`created_by`,`updated_at`) VALUES
(3,'Headphones','',600.00,'','products/69ab9cabba82b3.69514327.jpg','2026-03-07 03:34:03','headphones','',850.00,0,'physical_product',NULL,'',50,0.00,'','','','','','','',0,'draft',1,'2026-03-07 03:34:03');

-- --------------------------------------------------------
-- product_images  (NEW)
-- Stores additional/gallery images per product.
-- The primary cover image stays in products.image for backwards compatibility.
-- --------------------------------------------------------
CREATE TABLE `product_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `image` varchar(255) NOT NULL COMMENT 'Path relative to admin uploads dir',
  `alt_text` varchar(255) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0 COMMENT 'Ascending; 0 = first in gallery',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If 1 this image is also synced to products.image',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- site_settings
-- --------------------------------------------------------
CREATE TABLE `site_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_name` varchar(150) DEFAULT 'Connect',
  `logo` varchar(255) DEFAULT NULL,
  `favicon` varchar(255) DEFAULT NULL,
  `lenco_api_key` varchar(255) DEFAULT NULL,
  `lenco_environment` enum('dev','prod') DEFAULT 'dev',
  `lenco_secret_key` varchar(255) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'USD' COMMENT 'ISO 4217 code, e.g. USD, GBP, NGN',
  `currency_symbol` varchar(10) DEFAULT '$' COMMENT 'Display symbol, e.g. $, £, ₦, €',
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `updated_by` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=2;

INSERT INTO `site_settings` (`id`,`site_name`,`logo`,`favicon`,`lenco_api_key`,`lenco_environment`,`lenco_secret_key`,`currency`,`currency_symbol`,`updated_by`,`updated_at`) VALUES
(1,'Connect','logos/69a16ed30913d6.52385094.png',NULL,NULL,'dev',NULL,'USD','$',1,'2026-02-27 10:15:48');

-- --------------------------------------------------------
-- users
-- --------------------------------------------------------
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','suspended') DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Foreign Keys ──────────────────────────────────────────────────────────────

ALTER TABLE `carousel`
  ADD CONSTRAINT `carousel_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

ALTER TABLE `payment_verifications`
  ADD CONSTRAINT `payment_verifications_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payment_verifications_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `admins` (`id`);

ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

ALTER TABLE `product_images`
  ADD CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `site_settings`
  ADD CONSTRAINT `site_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

COMMIT;

-- ─────────────────────────────────────────────────────────────────────────────
-- MIGRATION (existing installs — run these ALTER statements instead)
-- ─────────────────────────────────────────────────────────────────────────────
-- ALTER TABLE `products`
--   ADD COLUMN `is_free` tinyint(1) NOT NULL DEFAULT 0 AFTER `sale_price`;
--
-- CREATE TABLE IF NOT EXISTS `product_images` (
--   `id` int(11) NOT NULL AUTO_INCREMENT,
--   `product_id` int(11) NOT NULL,
--   `image` varchar(255) NOT NULL,
--   `alt_text` varchar(255) DEFAULT NULL,
--   `sort_order` int(11) NOT NULL DEFAULT 0,
--   `is_primary` tinyint(1) NOT NULL DEFAULT 0,
--   `created_at` timestamp NULL DEFAULT current_timestamp(),
--   PRIMARY KEY (`id`),
--   KEY `product_id` (`product_id`),
--   CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`)
--     REFERENCES `products` (`id`) ON DELETE CASCADE
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- ALTER TABLE `site_settings`
--   ADD COLUMN `currency_symbol` varchar(10) DEFAULT '$' AFTER `currency`;
-- UPDATE `site_settings` SET `currency_symbol`='₦' WHERE `id`=1 AND `currency`='NGN';