-- =============================================================================
--  NexTech Store — Complete Database Schema v3
--  Includes: ZynlePay transactions, payment_settings, social_links
--  Charset: utf8mb4 throughout
-- =============================================================================

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

-- ── admins ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `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;

-- Default admin: password is "admin123" — CHANGE IMMEDIATELY AFTER INSTALL
INSERT IGNORE INTO `admins` VALUES
(1,'Admin','admin@nextech.com','$2y$10$OV.38ugVKMM.kXH0N4Sst.r0ZeWXkK4jQW1jwPQ.kNQehaFh99B2K','superadmin',NULL,NOW());

-- ── site_settings ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `site_settings` (
  `id`              int(11)      NOT NULL AUTO_INCREMENT,
  `site_name`       varchar(150) DEFAULT 'NexTech Store',
  `logo`            varchar(255) DEFAULT NULL,
  `favicon`         varchar(255) DEFAULT NULL,
  `currency`        varchar(10)  DEFAULT 'ZMW',
  `currency_symbol` varchar(10)  DEFAULT 'K',
  `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 IGNORE INTO `site_settings` (`id`,`site_name`,`currency`,`currency_symbol`) VALUES
(1,'NexTech Store','ZMW','K');

-- ── payment_settings (NEW) ────────────────────────────────────────────────────
-- Stores ZynlePay (and future gateway) credentials — loaded dynamically at runtime
CREATE TABLE IF NOT EXISTS `payment_settings` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `gateway_name` varchar(100) NOT NULL DEFAULT 'ZynlePay',
  `api_key`      varchar(500) DEFAULT NULL  COMMENT 'ZynlePay API Key',
  `api_id`       varchar(255) DEFAULT NULL  COMMENT 'ZynlePay API ID',
  `merchant_id`  varchar(255) DEFAULT NULL  COMMENT 'ZynlePay Merchant ID',
  `service_id`   varchar(50)  NOT NULL DEFAULT '1002',
  `channel`      varchar(50)  NOT NULL DEFAULT 'momo',
  `base_url`     varchar(500) NOT NULL DEFAULT 'https://sandbox.zynlepay.com/api',
  `callback_url` varchar(500) DEFAULT NULL  COMMENT 'Public webhook URL',
  `currency`     varchar(10)  NOT NULL DEFAULT 'ZMW',
  `created_at`   timestamp    NULL DEFAULT current_timestamp(),
  `updated_at`   timestamp    NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=2;

INSERT IGNORE INTO `payment_settings` (`id`,`gateway_name`,`base_url`,`service_id`,`channel`,`currency`) VALUES
(1,'ZynlePay','https://sandbox.zynlepay.com/api','1002','momo','ZMW');

-- ── transactions (NEW) ───────────────────────────────────────────────────────
-- Records every Mobile Money transaction attempt
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`                     int(11)                          NOT NULL AUTO_INCREMENT,
  `reference`              varchar(100)                     NOT NULL  COMMENT 'Matches orders.reference',
  `order_id`               int(11)                          NOT NULL,
  `amount`                 decimal(10,2)                    NOT NULL DEFAULT 0.00,
  `phone`                  varchar(30)                      DEFAULT NULL,
  `network`                varchar(30)                      DEFAULT NULL  COMMENT 'AIRTEL | MTN | ZAMTEL',
  `status`                 enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `provider_transaction_id` varchar(255)                    DEFAULT NULL,
  `created_at`             timestamp                        NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `order_id`   (`order_id`),
  KEY `status`     (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── social_links (NEW) ───────────────────────────────────────────────────────
-- Admin-managed social media links — appear in footer, contact, mobile menu
CREATE TABLE IF NOT EXISTS `social_links` (
  `id`         int(11)      NOT NULL AUTO_INCREMENT,
  `platform`   varchar(50)  NOT NULL  COMMENT 'Facebook | Instagram | WhatsApp | X | TikTok | LinkedIn | YouTube',
  `url`        varchar(500) NOT NULL,
  `icon`       varchar(50)  DEFAULT NULL  COMMENT 'Icon identifier',
  `created_at` timestamp    NULL DEFAULT current_timestamp(),
  `updated_at` timestamp    NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform` (`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── users ─────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`                int(11)      NOT NULL AUTO_INCREMENT,
  `email`             varchar(150) NOT NULL,
  `password`          varchar(255) NOT NULL,
  `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',
  `created_at`        timestamp    NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── categories ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `categories` (
  `id`          int(11)      NOT NULL AUTO_INCREMENT,
  `name`        varchar(100) NOT NULL,
  `slug`        varchar(110) NOT NULL,
  `description` text         DEFAULT NULL,
  `image`       varchar(255) DEFAULT NULL,
  `sort_order`  int(11)      DEFAULT 0,
  `status`      enum('active','inactive') DEFAULT 'active',
  `created_at`  timestamp    NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── products ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `products` (
  `id`               int(11)      NOT NULL AUTO_INCREMENT,
  `name`             varchar(150) NOT NULL,
  `description`      text         DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `price`            decimal(10,2) NOT NULL DEFAULT 0.00,
  `sale_price`       decimal(10,2) DEFAULT NULL,
  `is_free`          tinyint(1)   NOT NULL DEFAULT 0,
  `category`         varchar(100) DEFAULT NULL,
  `image`            varchar(255) DEFAULT NULL,
  `slug`             varchar(200) DEFAULT NULL,
  `type`             enum('physical_product','software_product','physical_service','software_service') NOT NULL DEFAULT 'physical_product',
  `sku`              varchar(100) DEFAULT NULL,
  `stock_qty`        int(11)      DEFAULT NULL,
  `download_url`     varchar(500) DEFAULT NULL,
  `license_type`     varchar(100) DEFAULT NULL,
  `version`          varchar(50)  DEFAULT NULL,
  `platform`         varchar(100) DEFAULT NULL,
  `is_featured`      tinyint(1)   DEFAULT 0,
  `status`           enum('active','inactive','draft') DEFAULT 'draft',
  `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`),
  UNIQUE KEY `slug` (`slug`),
  KEY `status`      (`status`),
  KEY `is_featured` (`is_featured`),
  KEY `created_by`  (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── product_images ────────────────────────────────────────────────────────────
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`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── orders ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `orders` (
  `id`               int(11)      NOT NULL AUTO_INCREMENT,
  `user_id`          int(11)      DEFAULT NULL,
  `reference`        varchar(100) DEFAULT NULL,
  `status`           enum('Pending','Processing','Completed','Cancelled','Shipped','Refunded') DEFAULT 'Pending',
  `payment_status`   enum('unpaid','paid','partially_paid','refunded') DEFAULT 'unpaid',
  `payment_method`   varchar(50)  DEFAULT NULL  COMMENT 'momo | bank | card',
  `payment_reference` varchar(200) DEFAULT NULL,
  `subtotal`         decimal(10,2) DEFAULT 0.00,
  `discount`         decimal(10,2) DEFAULT 0.00,
  `total`            decimal(10,2) DEFAULT NULL,
  `notes`            text         DEFAULT NULL,
  `address`          text         DEFAULT NULL,
  `phone`            varchar(30)  DEFAULT NULL,
  `created_at`       timestamp    NULL DEFAULT current_timestamp(),
  `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 IF NOT EXISTS `order_items` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `order_id`     int(11)      DEFAULT NULL,
  `product_id`   int(11)      DEFAULT NULL,
  `product_name` varchar(150) DEFAULT NULL,
  `product_type` enum('physical_product','software_product','physical_service','software_service') DEFAULT NULL,
  `qty`          int(11)      DEFAULT 1,
  `quantity`     int(11)      DEFAULT 1  COMMENT 'alias for qty',
  `price`        decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id`   (`order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── carousel ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── notifications ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `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;

-- ── payment_verifications ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `payment_verifications` (
  `id`          int(11)      NOT NULL AUTO_INCREMENT,
  `order_id`    int(11)      NOT NULL,
  `verified_by` int(11)      NOT NULL,
  `method`      enum('momo','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;

-- ── Foreign Keys ──────────────────────────────────────────────────────────────
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

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

ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE;

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;

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

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

COMMIT;

-- =============================================================================
-- MIGRATION: If upgrading from v2, run these ALTER statements instead:
-- =============================================================================
/*
CREATE TABLE IF NOT EXISTS `payment_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gateway_name` varchar(100) NOT NULL DEFAULT 'ZynlePay',
  `api_key` varchar(500) DEFAULT NULL,
  `merchant_id` varchar(255) DEFAULT NULL,
  `base_url` varchar(500) NOT NULL DEFAULT 'https://sandbox.zynlepay.com/api/v1',
  `callback_url` varchar(500) DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'ZMW',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `payment_settings` (id,gateway_name,base_url,currency) VALUES (1,'ZynlePay','https://sandbox.zynlepay.com/api/v1','ZMW');

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) NOT NULL,
  `order_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `phone` varchar(30) DEFAULT NULL,
  `network` varchar(30) DEFAULT NULL,
  `status` enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `provider_transaction_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `order_id` (`order_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `social_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `platform` varchar(50) NOT NULL,
  `url` varchar(500) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform` (`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `site_settings`
  DROP COLUMN IF EXISTS `lenco_api_key`,
  DROP COLUMN IF EXISTS `lenco_environment`,
  DROP COLUMN IF EXISTS `lenco_secret_key`;

-- v3.2 additions (ZynlePay new credentials — run if upgrading):
ALTER TABLE `payment_settings`
  ADD COLUMN IF NOT EXISTS `api_id`     varchar(255) DEFAULT NULL  AFTER `api_key`,
  ADD COLUMN IF NOT EXISTS `service_id` varchar(50)  NOT NULL DEFAULT '1002' AFTER `merchant_id`,
  ADD COLUMN IF NOT EXISTS `channel`    varchar(50)  NOT NULL DEFAULT 'momo' AFTER `service_id`;

UPDATE `payment_settings` SET
  `base_url`   = 'https://sandbox.zynlepay.com/api',
  `service_id` = '1002',
  `channel`    = 'momo'
WHERE id=1 AND base_url LIKE '%zynlepay%';

-- v3.1 additions (run if upgrading from v3.0):
ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `address` text DEFAULT NULL AFTER `notes`,
  ADD COLUMN IF NOT EXISTS `phone`   varchar(30) DEFAULT NULL AFTER `address`;

ALTER TABLE `orders`
  MODIFY COLUMN `status` enum('Pending','Processing','Completed','Cancelled','Shipped','Refunded') DEFAULT 'Pending';

ALTER TABLE `order_items`
  ADD COLUMN IF NOT EXISTS `quantity` int(11) DEFAULT 1 AFTER `qty`;

ALTER TABLE `transactions`
  MODIFY COLUMN `network` varchar(30) DEFAULT NULL;
*/

-- ── v4.0 additions: Lipila payment gateway columns ──────────────────────────
ALTER TABLE `payment_settings`
  ADD COLUMN IF NOT EXISTS `lipila_api_key`      varchar(500) DEFAULT NULL  COMMENT 'Lipila x-api-key',
  ADD COLUMN IF NOT EXISTS `lipila_base_url`     varchar(500) DEFAULT 'https://api.lipila.dev' COMMENT 'Lipila API base URL',
  ADD COLUMN IF NOT EXISTS `lipila_callback_url` varchar(500) DEFAULT NULL  COMMENT 'Lipila webhook URL',
  ADD COLUMN IF NOT EXISTS `lipila_redirect_url` varchar(500) DEFAULT NULL  COMMENT 'Redirect after payment',
  ADD COLUMN IF NOT EXISTS `lipila_back_url`     varchar(500) DEFAULT NULL  COMMENT 'Cancel / back URL',
  ADD COLUMN IF NOT EXISTS `active_gateway`      varchar(30)  DEFAULT 'zynlepay' COMMENT 'zynlepay | lipila';

-- ── Ensure carousel uploads directory entry exists ───────────────────────────
-- (The /admin/uploads/carousel/ folder must exist on the filesystem)