-- Afrochip Electronics Spares: MySQL / MariaDB schema
-- Import once through cPanel > phpMyAdmin > Import. Requires MySQL 5.7+ or MariaDB 10.3+.
SET NAMES utf8mb4;

CREATE TABLE users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(254) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name          VARCHAR(120) NOT NULL,
  is_admin      TINYINT(1) NOT NULL DEFAULT 0,
  created_at    INT UNSIGNED NOT NULL,
  last_login_at INT UNSIGNED NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE buyer_profiles (
  user_id    INT UNSIGNED NOT NULL PRIMARY KEY,
  org        VARCHAR(120) NOT NULL,
  name       VARCHAR(120) NOT NULL,
  email      VARCHAR(254) NOT NULL,
  phone      VARCHAR(20)  NOT NULL,
  country    VARCHAR(40)  NOT NULL,
  city       VARCHAR(80)  NOT NULL,
  address    VARCHAR(200) NOT NULL DEFAULT '',
  tax_pin    VARCHAR(20)  NOT NULL DEFAULT '',
  sector     VARCHAR(4)   NOT NULL DEFAULT 'hc',
  updated_at INT UNSIGNED NOT NULL,
  CONSTRAINT fk_bp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE suppliers (
  user_id      INT UNSIGNED NOT NULL PRIMARY KEY,
  legal        VARCHAR(120) NOT NULL,
  trading      VARCHAR(120) NOT NULL,
  reg_no       VARCHAR(40)  NOT NULL,
  kra_pin      VARCHAR(11)  NOT NULL DEFAULT '',
  city         VARCHAR(60)  NOT NULL,
  address      VARCHAR(200) NOT NULL,
  website      VARCHAR(200) NOT NULL DEFAULT '',
  sectors      VARCHAR(40)  NOT NULL,
  source       VARCHAR(60)  NOT NULL,
  oem_auth     VARCHAR(200) NOT NULL DEFAULT '',
  supplies     TEXT         NOT NULL,
  contact_name VARCHAR(120) NOT NULL,
  email        VARCHAR(254) NOT NULL,
  phone        VARCHAR(20)  NOT NULL,
  country      VARCHAR(40)  NOT NULL,
  status       VARCHAR(12)  NOT NULL DEFAULT 'pending',
  review_note  VARCHAR(500) NOT NULL DEFAULT '',
  reviewed_at  INT UNSIGNED NULL,
  reviewed_by  INT UNSIGNED NULL,
  submitted_at INT UNSIGNED NOT NULL,
  updated_at   INT UNSIGNED NOT NULL,
  KEY idx_sup_status (status),
  CONSTRAINT fk_sup_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE listings (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  supplier_id   INT UNSIGNED NOT NULL,
  title         VARCHAR(120) NOT NULL,
  cat_id        VARCHAR(10)  NOT NULL DEFAULT '',
  sector        VARCHAR(4)   NOT NULL,
  lvl           VARCHAR(30)  NOT NULL,
  cond          VARCHAR(30)  NOT NULL,
  oem           VARCHAR(80)  NOT NULL,
  pn            VARCHAR(60)  NOT NULL DEFAULT '',
  qty           INT UNSIGNED NOT NULL,
  price         DECIMAL(14,2) NOT NULL,
  currency      CHAR(3)      NOT NULL,
  dispatch_days SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  warranty_days SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  city          VARCHAR(60)  NOT NULL,
  description   TEXT         NOT NULL,
  active        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    INT UNSIGNED NOT NULL,
  updated_at    INT UNSIGNED NOT NULL,
  KEY idx_list_sup (supplier_id),
  KEY idx_list_active (active),
  CONSTRAINT fk_list_sup FOREIGN KEY (supplier_id) REFERENCES suppliers(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE requests (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ref        VARCHAR(24)  NOT NULL UNIQUE,
  user_id    INT UNSIGNED NULL,
  type       VARCHAR(40)  NOT NULL,
  status     VARCHAR(12)  NOT NULL DEFAULT 'New',
  org        VARCHAR(120) NOT NULL,
  name       VARCHAR(120) NOT NULL,
  email      VARCHAR(254) NOT NULL,
  phone      VARCHAR(20)  NOT NULL,
  country    VARCHAR(40)  NOT NULL,
  city       VARCHAR(80)  NOT NULL DEFAULT '',
  urgency    VARCHAR(60)  NOT NULL DEFAULT '',
  payment    VARCHAR(30)  NOT NULL DEFAULT '',
  po         VARCHAR(60)  NOT NULL DEFAULT '',
  summary    VARCHAR(400) NOT NULL DEFAULT '',
  notes      TEXT         NOT NULL,
  ip_hash    CHAR(64)     NOT NULL,
  created_at INT UNSIGNED NOT NULL,
  status_at  INT UNSIGNED NULL,
  KEY idx_req_user (user_id),
  KEY idx_req_created (created_at),
  CONSTRAINT fk_req_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE request_lines (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNSIGNED NOT NULL,
  kind       VARCHAR(8)   NOT NULL,
  cat_id     VARCHAR(10)  NOT NULL DEFAULT '',
  opt        VARCHAR(10)  NOT NULL DEFAULT '',
  listing_id INT UNSIGNED NULL,
  title      VARCHAR(160) NOT NULL,
  supplier   VARCHAR(120) NOT NULL DEFAULT '',
  price      DECIMAL(14,2) NULL,
  currency   CHAR(3)      NOT NULL DEFAULT '',
  qty        INT UNSIGNED NOT NULL,
  KEY idx_rl_req (request_id),
  CONSTRAINT fk_rl_req FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
  user_id    INT UNSIGNED NOT NULL PRIMARY KEY,
  token_hash CHAR(64)     NOT NULL,
  expires_at INT UNSIGNED NOT NULL,
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rate_events (
  id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bucket  VARCHAR(80)  NOT NULL,
  at      INT UNSIGNED NOT NULL,
  KEY idx_rate (bucket, at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;