-- =====================================================
-- NEXERA AFRICA - Unified Pi Payments Schema
-- =====================================================
-- IMPORTANT: This schema must match what api/pi_payment.php
-- expects. If you already have a pi_payments table from an
-- older install, run migrate.php instead - it will ALTER
-- the existing table in place without losing data.
-- =====================================================

CREATE TABLE IF NOT EXISTS `pi_payments` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `order_id`   VARCHAR(128)  NOT NULL UNIQUE,
    `payment_id` VARCHAR(255)  DEFAULT NULL,
    `user_uid`   VARCHAR(255)  DEFAULT NULL,
    `amount`     DECIMAL(18,8) NOT NULL,
    `txid`       VARCHAR(255)  DEFAULT NULL,
    `status`     ENUM('initialized','approved','completed','interrupted','cancelled','failed')
                 NOT NULL DEFAULT 'initialized',
    `metadata`   TEXT          DEFAULT NULL,
    `mode`       ENUM('test','mainnet') NOT NULL DEFAULT 'test',
    `created_at` TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_payment_id` (`payment_id`),
    INDEX `idx_status`     (`status`),
    INDEX `idx_mode`       (`mode`),
    INDEX `idx_created`    (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Reference: full application schema
-- =====================================================
-- The migration script will also ensure these tables exist
-- with the right columns. They are reproduced here for
-- documentation / fresh installs.
-- =====================================================

CREATE TABLE IF NOT EXISTS `users` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `username`   VARCHAR(100) NOT NULL UNIQUE,
    `email`      VARCHAR(190) NOT NULL UNIQUE,
    `phone`      VARCHAR(40)  DEFAULT NULL,
    `password`   VARCHAR(255) NOT NULL,
    `role`       ENUM('user','vendor','admin') NOT NULL DEFAULT 'user',
    `verified`   TINYINT(1)   NOT NULL DEFAULT 0,
    `blocked`    TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `categories` (
    `id`   INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unified products table. Both `name` (legacy) and `title` (current)
-- are kept so existing code paths keep working; new code uses `title`.
CREATE TABLE IF NOT EXISTS `products` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT           DEFAULT NULL,
    `vendor_id`   INT           DEFAULT NULL,
    `user_id`     INT           DEFAULT NULL,
    `name`        VARCHAR(190)  DEFAULT NULL,
    `title`       VARCHAR(190)  DEFAULT NULL,
    `description` TEXT,
    `price`       DECIMAL(18,8) DEFAULT NULL,
    `price_pi`    DECIMAL(18,8) DEFAULT NULL,
    `status`      VARCHAR(40)   DEFAULT 'active',
    `created_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_category` (`category_id`),
    INDEX `idx_vendor`   (`vendor_id`),
    INDEX `idx_user`     (`user_id`),
    INDEX `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_images` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `image_path` VARCHAR(500) NOT NULL,
    `is_main`    TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Legacy listings table (kept for compatibility with create_listing.php,
-- view_listing.php, edit_listing_admin.php, verify_listing.php).
CREATE TABLE IF NOT EXISTS `listings` (
    `id`          INT AUTO_INCREMENT PRIMARY KEY,
    `user_id`     INT           NOT NULL,
    `category_id` INT           DEFAULT NULL,
    `title`       VARCHAR(190)  NOT NULL,
    `description` TEXT,
    `price`       DECIMAL(18,8) NOT NULL,
    `currency`    VARCHAR(10)   DEFAULT 'PI',
    `location`    VARCHAR(190)  DEFAULT '',
    `status`      VARCHAR(20)   DEFAULT 'active',
    `verified`    TINYINT(1)    NOT NULL DEFAULT 0,
    `verified_by` INT           DEFAULT NULL,
    `verified_at` TIMESTAMP     NULL DEFAULT NULL,
    `created_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user`     (`user_id`),
    INDEX `idx_category` (`category_id`),
    INDEX `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `images` (
    `id`         INT AUTO_INCREMENT PRIMARY KEY,
    `listing_id` INT NOT NULL,
    `image_path` VARCHAR(500) NOT NULL,
    `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_listing` (`listing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional orders table (referenced by complete_payment.php).
CREATE TABLE IF NOT EXISTS `orders` (
    `id`             INT AUTO_INCREMENT PRIMARY KEY,
    `user_id`        INT NOT NULL,
    `listing_id`     INT DEFAULT NULL,
    `pi_payment_id`  VARCHAR(255) DEFAULT NULL,
    `txid`           VARCHAR(255) DEFAULT NULL,
    `amount`         DECIMAL(18,8) NOT NULL,
    `status`         VARCHAR(20)   DEFAULT 'pending',
    `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user`    (`user_id`),
    INDEX `idx_listing` (`listing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
