-- Swavroski Market v3 — MySQL schema.
-- Run this once against the target database:
--   mysql -u <user> -p <DB_NAME> < schema.sql

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  chat_id     VARCHAR(32)     NOT NULL PRIMARY KEY,
  username    VARCHAR(64)     NULL,
  first_name  VARCHAR(128)    NULL,
  balance     DECIMAL(12,2)   NOT NULL DEFAULT 0,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chat_id     VARCHAR(32)     NOT NULL UNIQUE,
  role        ENUM('super_admin','staff') NOT NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id           INT            NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(255)   NOT NULL,
  description  TEXT           NULL,
  price        DECIMAL(12,2)  NOT NULL,
  stock        INT            NOT NULL DEFAULT 0,
  image_url    TEXT           NULL,
  category     VARCHAR(64)    NULL,
  sub_category VARCHAR(64)    NULL,
  active       TINYINT        NOT NULL DEFAULT 1,
  created_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_products_subcat (sub_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chat_id     VARCHAR(32)     NOT NULL,
  product_id  INT             NOT NULL,
  price       DECIMAL(12,2)   NOT NULL,
  status      ENUM('pending','paid','delivered') NOT NULL DEFAULT 'paid',
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_orders_chat (chat_id),
  INDEX idx_orders_status (status),
  CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transactions (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chat_id     VARCHAR(32)     NOT NULL,
  type        ENUM('deposit','purchase','admin_credit','admin_debit') NOT NULL,
  amount      DECIMAL(12,2)   NOT NULL,
  note        TEXT            NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tx_chat (chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deposit_requests (
  id             INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chat_id        VARCHAR(32)  NOT NULL,
  method         ENUM('btc','usdt','eth','ltc','usdc') NOT NULL,
  amount         DECIMAL(12,2) NOT NULL,
  tx_hash        VARCHAR(255) NULL,
  photo_file_id  VARCHAR(255) NULL,
  status         ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_id       INT          NULL,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  decided_at     TIMESTAMP    NULL DEFAULT NULL,
  INDEX idx_dep_chat (chat_id),
  INDEX idx_dep_status (status),
  CONSTRAINT fk_dep_admin FOREIGN KEY (admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_logs (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id    INT             NOT NULL,
  admin_id    INT             NOT NULL,
  chat_id     VARCHAR(32)     NOT NULL,
  message     TEXT            NOT NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_dlog_order (order_id),
  CONSTRAINT fk_dlog_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_dlog_admin FOREIGN KEY (admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcast_logs (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  chat_id     VARCHAR(32)     NOT NULL,
  status      ENUM('success','failed') NOT NULL,
  attempts    INT             NOT NULL DEFAULT 0,
  last_error  TEXT            NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_blog_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_logs (
  id          INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
  admin_id    INT             NOT NULL,
  action      TEXT            NOT NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_alog_admin FOREIGN KEY (admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;