-- ============================================================
-- TradeVault — Full Database Schema v3
-- Run in phpMyAdmin: Import → select this file → Go
-- Safe to re-import on existing installs
-- ============================================================
SET NAMES utf8mb4;
SET time_zone = '+02:00';

-- ── Users ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`           VARCHAR(120) NOT NULL,
  `surname`        VARCHAR(120) DEFAULT '',
  `email`          VARCHAR(191) NOT NULL UNIQUE,
  `password_hash`  VARCHAR(255) NOT NULL,
  `phone`          VARCHAR(30)  DEFAULT NULL,
  `city`           VARCHAR(100) DEFAULT NULL,
  `province`       VARCHAR(80)  DEFAULT NULL,
  `avatar`         VARCHAR(255) DEFAULT NULL,
  `bio`            TEXT         DEFAULT NULL,
  `status`         ENUM('active','suspended','banned') NOT NULL DEFAULT 'active',
  `suspend_reason` VARCHAR(255) DEFAULT NULL,
  `email_verified` TINYINT(1)   NOT NULL DEFAULT 0,
  `total_sales`    INT UNSIGNED NOT NULL DEFAULT 0,
  `rating`         DECIMAL(3,2) NOT NULL DEFAULT 0.00,
  `rating_count`   INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login`     DATETIME     DEFAULT NULL,
  INDEX `idx_status` (`status`),
  INDEX `idx_email`  (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Patch existing installs
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `surname`    VARCHAR(120) DEFAULT '' AFTER `name`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `province`   VARCHAR(80)  DEFAULT NULL AFTER `city`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `bio`        TEXT         DEFAULT NULL AFTER `province`;
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- ── Categories ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `categories` (
  `id`   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(80) NOT NULL,
  `icon` VARCHAR(10) NOT NULL DEFAULT '📦',
  `slug` VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `categories` (`name`,`icon`,`slug`) VALUES
('Electronics','📱','electronics'),('Fashion','👗','fashion'),
('Home & Garden','🏠','home'),('Sports','⚽','sports'),
('Vehicles','🚗','vehicles'),('Toys & Games','🎮','toys'),
('Books & Media','📚','books'),('Art & Collectibles','🎨','art'),
('Health & Beauty','💄','beauty'),('Tools','🔧','tools'),
('Other','📦','other');

-- ── Seller Banking Details ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `seller_banking` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT UNSIGNED NOT NULL UNIQUE,
  `bank_name`    VARCHAR(80)  NOT NULL,
  `account_name` VARCHAR(150) NOT NULL,
  `account_num`  VARCHAR(30)  NOT NULL,
  `branch_code`  VARCHAR(20)  DEFAULT NULL,
  `account_type` ENUM('cheque','savings','transmission') NOT NULL DEFAULT 'cheque',
  `updated_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Listings ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `listings` (
  `id`          INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  `seller_id`   INT UNSIGNED  NOT NULL,
  `category_id` SMALLINT UNSIGNED NOT NULL,
  `title`       VARCHAR(200)  NOT NULL,
  `description` TEXT          NOT NULL,
  `price`       DECIMAL(12,2) NOT NULL,
  `condition`   ENUM('new','like_new','good','fair') NOT NULL DEFAULT 'good',
  `location`    VARCHAR(120)  DEFAULT NULL,
  `status`      ENUM('active','sold','removed','suspended') NOT NULL DEFAULT 'active',
  `images`      TEXT          DEFAULT NULL,
  `views`       INT UNSIGNED  NOT NULL DEFAULT 0,
  `created_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`seller_id`)   REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE RESTRICT,
  INDEX `idx_status`  (`status`),
  INDEX `idx_seller`  (`seller_id`),
  FULLTEXT `ft_search`(`title`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Transactions ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`                      INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  `reference`               VARCHAR(32)   NOT NULL UNIQUE,
  `yoco_checkout_id`        VARCHAR(120)  DEFAULT NULL,
  `listing_id`              INT UNSIGNED  NOT NULL,
  `buyer_id`                INT UNSIGNED  NOT NULL,
  `seller_id`               INT UNSIGNED  NOT NULL,
  `amount`                  DECIMAL(12,2) NOT NULL COMMENT 'Buyer pays this — listed price only',
  `platform_fee`            DECIMAL(12,2) NOT NULL COMMENT 'Deducted from seller payout',
  `seller_payout`           DECIMAL(12,2) NOT NULL COMMENT 'Seller receives this after fee',
  `seller_banking_snapshot` TEXT          DEFAULT NULL COMMENT 'JSON snapshot of banking at purchase time',
  `status` ENUM(
    'pending_payment','payment_received','in_escrow',
    'shipped','delivered','buyer_confirmed',
    'funds_released','disputed','refunded','cancelled'
  ) NOT NULL DEFAULT 'pending_payment',
  `tracking_number`   VARCHAR(100) DEFAULT NULL,
  `shipping_carrier`  VARCHAR(60)  DEFAULT NULL,
  `buyer_notes`       TEXT         DEFAULT NULL,
  `dispute_reason`    TEXT         DEFAULT NULL,
  `dispute_opened_at` DATETIME     DEFAULT NULL,
  `confirmed_at`      DATETIME     DEFAULT NULL,
  `released_at`       DATETIME     DEFAULT NULL,
  `delivery_method`   ENUM('pickup','delivery') NOT NULL DEFAULT 'pickup',
  `delivery_address`  VARCHAR(300) DEFAULT NULL,
  `buyer_name`        VARCHAR(200) DEFAULT NULL,
  `buyer_email`       VARCHAR(191) DEFAULT NULL,
  `buyer_phone`       VARCHAR(30)  DEFAULT NULL,
  `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`listing_id`) REFERENCES `listings`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`buyer_id`)   REFERENCES `users`(`id`)    ON DELETE RESTRICT,
  FOREIGN KEY (`seller_id`)  REFERENCES `users`(`id`)    ON DELETE RESTRICT,
  INDEX `idx_buyer`  (`buyer_id`),
  INDEX `idx_seller` (`seller_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_ref`    (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Patch existing installs
ALTER TABLE `transactions` ADD COLUMN IF NOT EXISTS `seller_banking_snapshot` TEXT DEFAULT NULL AFTER `seller_payout`;
ALTER TABLE `transactions` ADD COLUMN IF NOT EXISTS `buyer_notes` TEXT DEFAULT NULL AFTER `buyer_phone`;
ALTER TABLE `transactions` ADD COLUMN IF NOT EXISTS `dispute_opened_at` DATETIME DEFAULT NULL AFTER `dispute_reason`;
ALTER TABLE `transactions` ADD COLUMN IF NOT EXISTS `confirmed_at` DATETIME DEFAULT NULL AFTER `dispute_opened_at`;
ALTER TABLE `transactions` ADD COLUMN IF NOT EXISTS `released_at` DATETIME DEFAULT NULL AFTER `confirmed_at`;

-- ── Transaction Audit Log ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transaction_log` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT UNSIGNED NOT NULL,
  `from_status`    VARCHAR(40)  DEFAULT NULL,
  `to_status`      VARCHAR(40)  NOT NULL,
  `note`           VARCHAR(500) DEFAULT NULL,
  `actor`          VARCHAR(40)  DEFAULT 'system',
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  INDEX `idx_tx` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Chat Messages (per transaction) ─────────────────────────
CREATE TABLE IF NOT EXISTS `messages` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT UNSIGNED NOT NULL,
  `sender_id`      INT UNSIGNED NOT NULL,
  `body`           TEXT         NOT NULL,
  `msg_type`       ENUM('chat','system','payment','tracking') NOT NULL DEFAULT 'chat',
  `is_read`        TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_id`)      REFERENCES `users`(`id`)        ON DELETE CASCADE,
  INDEX `idx_tx`   (`transaction_id`),
  INDEX `idx_read` (`is_read`,`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Patch: add msg_type if missing
ALTER TABLE `messages` ADD COLUMN IF NOT EXISTS `msg_type` ENUM('chat','system','payment','tracking') NOT NULL DEFAULT 'chat' AFTER `body`;
ALTER TABLE `messages` DROP COLUMN IF EXISTS `attachment`;

-- ── Admin Actions Log ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admin_log` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `action`     VARCHAR(80)  NOT NULL,
  `target`     VARCHAR(80)  DEFAULT NULL,
  `details`    TEXT         DEFAULT NULL,
  `ip`         VARCHAR(45)  DEFAULT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Reviews ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `reviews` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` INT UNSIGNED NOT NULL UNIQUE,
  `reviewer_id`    INT UNSIGNED NOT NULL,
  `reviewed_id`    INT UNSIGNED NOT NULL,
  `rating`         TINYINT      NOT NULL,
  `comment`        TEXT         DEFAULT NULL,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;