CREATE DATABASE IF NOT EXISTS omosala_marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE omosala_marketplace;

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(40),
  password_hash VARCHAR(255),
  role ENUM('customer','vendor','dispatch','accountant','admin','super-admin') DEFAULT 'customer',
  status ENUM('Active','Suspended','Pending') DEFAULT 'Active',
  provider VARCHAR(40),
  wallet_balance DECIMAL(14,2) DEFAULT 0,
  loyalty_points INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vendors (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40),
  store_name VARCHAR(190) NOT NULL,
  slug VARCHAR(190) UNIQUE,
  logo_url TEXT,
  banner_url TEXT,
  status ENUM('Pending','Approved','Suspended') DEFAULT 'Pending',
  rating DECIMAL(3,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(40) PRIMARY KEY,
  vendor_id VARCHAR(40),
  name VARCHAR(220) NOT NULL,
  category VARCHAR(120) NOT NULL,
  description TEXT,
  price DECIMAL(14,2) NOT NULL,
  sale_price DECIMAL(14,2),
  stock INT DEFAULT 0,
  sku VARCHAR(80) UNIQUE,
  barcode VARCHAR(120),
  image_url TEXT,
  status ENUM('Draft','Pending','Approved','Rejected') DEFAULT 'Pending',
  flash_sale TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS product_variants (
  id VARCHAR(40) PRIMARY KEY,
  product_id VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  value VARCHAR(120) NOT NULL,
  price_delta DECIMAL(14,2) DEFAULT 0,
  stock INT DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS customer_addresses (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40) NOT NULL,
  label VARCHAR(80),
  full_name VARCHAR(160),
  phone VARCHAR(40),
  address TEXT NOT NULL,
  city VARCHAR(120),
  state VARCHAR(120),
  is_default TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(40) PRIMARY KEY,
  order_number VARCHAR(60) NOT NULL UNIQUE,
  tracking_number VARCHAR(80) NOT NULL UNIQUE,
  user_id VARCHAR(40),
  customer_name VARCHAR(160) NOT NULL,
  phone VARCHAR(40),
  address TEXT,
  subtotal DECIMAL(14,2) NOT NULL,
  delivery_fee DECIMAL(14,2) DEFAULT 0,
  discount DECIMAL(14,2) DEFAULT 0,
  total DECIMAL(14,2) NOT NULL,
  payment_method VARCHAR(80),
  payment_status ENUM('Pending','Paid','Failed','Refunded') DEFAULT 'Pending',
  status ENUM('Pending','Processing','Packed','Shipped','Out for delivery','Delivered','Cancelled') DEFAULT 'Pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS order_items (
  id VARCHAR(40) PRIMARY KEY,
  order_id VARCHAR(40) NOT NULL,
  product_id VARCHAR(40),
  name VARCHAR(220) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(14,2) NOT NULL,
  total DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS payments (
  id VARCHAR(40) PRIMARY KEY,
  order_id VARCHAR(40),
  provider ENUM('Paystack','Flutterwave','Bank Transfer','Wallet','Cash on Delivery') NOT NULL,
  reference VARCHAR(120) UNIQUE,
  amount DECIMAL(14,2) NOT NULL,
  currency VARCHAR(10) DEFAULT 'NGN',
  status ENUM('Pending','Paid','Failed','Refunded') DEFAULT 'Pending',
  metadata JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS delivery_zones (
  id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(120),
  fee DECIMAL(14,2) NOT NULL,
  estimated_days VARCHAR(40),
  active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS riders (
  id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40),
  vehicle VARCHAR(80),
  status ENUM('Available','Assigned','Offline') DEFAULT 'Available'
);

CREATE TABLE IF NOT EXISTS delivery_assignments (
  id VARCHAR(40) PRIMARY KEY,
  order_id VARCHAR(40) NOT NULL,
  rider_id VARCHAR(40),
  status ENUM('Assigned','Picked Up','Out for delivery','Delivered','Failed') DEFAULT 'Assigned',
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (rider_id) REFERENCES riders(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS quotes (
  id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  contact VARCHAR(80),
  service VARCHAR(160),
  quantity INT DEFAULT 1,
  notes TEXT,
  estimated_total DECIMAL(14,2),
  status ENUM('New','Reviewed','Approved','Rejected') DEFAULT 'New',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS print_orders (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40),
  service VARCHAR(160) NOT NULL,
  size VARCHAR(80),
  material VARCHAR(120),
  quantity INT NOT NULL,
  file_url TEXT,
  notes TEXT,
  approval_status ENUM('Awaiting File','Under Review','Approved','Changes Requested') DEFAULT 'Awaiting File',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS uploads (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40),
  public_id VARCHAR(180),
  file_url TEXT NOT NULL,
  file_type VARCHAR(80),
  status ENUM('Uploaded','Validated','Rejected') DEFAULT 'Uploaded',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS support_tickets (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40),
  name VARCHAR(160),
  contact VARCHAR(80),
  subject VARCHAR(220),
  message TEXT,
  status ENUM('Open','In Progress','Resolved','Closed') DEFAULT 'Open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS wishlists (
  user_id VARCHAR(40) NOT NULL,
  product_id VARCHAR(40) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, product_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS notifications (
  id VARCHAR(40) PRIMARY KEY,
  user_id VARCHAR(40),
  type ENUM('whatsapp','order','payment','support','system') DEFAULT 'system',
  title VARCHAR(220),
  message TEXT,
  whatsapp_url TEXT,
  read_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS refunds (
  id VARCHAR(40) PRIMARY KEY,
  payment_id VARCHAR(40),
  amount DECIMAL(14,2) NOT NULL,
  reason TEXT,
  status ENUM('Requested','Approved','Rejected','Paid') DEFAULT 'Requested',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS coupons (
  id VARCHAR(40) PRIMARY KEY,
  code VARCHAR(60) NOT NULL UNIQUE,
  type ENUM('percentage','fixed') NOT NULL,
  value DECIMAL(14,2) NOT NULL,
  starts_at DATETIME,
  ends_at DATETIME,
  active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  actor_id VARCHAR(40),
  action VARCHAR(255) NOT NULL,
  metadata JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
