-- Charset & engine
CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(191) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(191) DEFAULT NULL,
  is_super TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(191) UNIQUE,
  mobile VARCHAR(32),
  name VARCHAR(191),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  slug VARCHAR(191) UNIQUE NOT NULL,
  parent_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  name VARCHAR(191) NOT NULL,
  slug VARCHAR(191) UNIQUE NOT NULL,
  price BIGINT NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  image VARCHAR(255),
  short_desc VARCHAR(255),
  long_desc TEXT,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(category_id),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_code VARCHAR(64) UNIQUE NOT NULL,
  user_id BIGINT NULL,
  name VARCHAR(191) NOT NULL,
  email VARCHAR(191) NULL,
  mobile VARCHAR(32) NOT NULL,
  address TEXT NOT NULL,
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  amount BIGINT NOT NULL,
  status ENUM('pending','processing','paid','failed','canceled') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(status),
  INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  qty INT NOT NULL,
  price BIGINT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  INDEX(order_id),
  INDEX(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  gateway VARCHAR(32) NOT NULL,
  track_id VARCHAR(64) NOT NULL,
  result_code INT NOT NULL,
  ref_number VARCHAR(128) NULL,
  raw_response MEDIUMTEXT NULL,
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_track (track_id),
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  skey VARCHAR(64) PRIMARY KEY,
  svalue TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seo_urls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('product','category','page') NOT NULL,
  ref_id BIGINT NOT NULL,
  slug VARCHAR(191) UNIQUE NOT NULL,
  title VARCHAR(191),
  description VARCHAR(255),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(type), INDEX(ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- demo
INSERT IGNORE INTO categories (id, name, slug) VALUES (1,'پیش‌فرض','default');
INSERT IGNORE INTO products (category_id,name,slug,price,stock,short_desc,long_desc,image) VALUES
(1,'نمونه محصول','sample-product',99000,50,'توضیح کوتاه محصول نمونه','توضیحات بلند برای محصول نمونه.','/assets/sample.jpg');
