-- Zylon: single-install database schema + demo seed
-- Fresh install only: CREATE DATABASE ... + all tables + sample data.

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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL DEFAULT '',
  username VARCHAR(60) NOT NULL UNIQUE,
  email VARCHAR(190) NULL DEFAULT NULL,
  phone VARCHAR(32) NULL DEFAULT NULL,
  profile_image VARCHAR(512) NULL DEFAULT NULL,
  address TEXT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'customer',
  wallet_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  suspended TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_phone ON users (phone);
CREATE INDEX idx_users_role ON users (role);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(40) NOT NULL,
  sku VARCHAR(60) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  category VARCHAR(120) NOT NULL,
  brand VARCHAR(120) NULL DEFAULT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  short_description TEXT,
  long_description MEDIUMTEXT,
  image_url VARCHAR(512) DEFAULT NULL,
  video_path VARCHAR(512) DEFAULT NULL,
  discount_percent INT NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  weight_kg DECIMAL(10,3) NULL DEFAULT NULL,
  dimensions VARCHAR(120) NULL DEFAULT NULL,
  tags TEXT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_products_slug (slug),
  UNIQUE KEY uq_products_tracking (tracking_code)
);
CREATE INDEX idx_products_title ON products (title);
CREATE INDEX idx_products_sku ON products (sku);
CREATE INDEX idx_products_category ON products (category);

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_tracking_id VARCHAR(40) NULL DEFAULT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  customer_address TEXT NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  payment_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  payment_ref VARCHAR(100) DEFAULT NULL,
  payment_authority VARCHAR(100) DEFAULT NULL,
  gateway VARCHAR(40) DEFAULT 'zarinpal',
  tracking_code VARCHAR(60) DEFAULT NULL,
  shipping_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_tracking_public (order_tracking_id)
);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_payment_status ON orders (payment_status);
CREATE INDEX idx_orders_authority ON orders (payment_authority);

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  product_tracking_code VARCHAR(40) NULL DEFAULT NULL,
  product_title VARCHAR(255) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  price DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

CREATE TABLE IF NOT EXISTS bank_cards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bank_name VARCHAR(120) NOT NULL,
  card_number_masked VARCHAR(32) NOT NULL,
  card_holder VARCHAR(120) NOT NULL,
  iban VARCHAR(40) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE INDEX idx_bank_cards_active ON bank_cards (is_active, sort_order);

CREATE TABLE IF NOT EXISTS newsletters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  summary TEXT,
  body MEDIUMTEXT,
  cover_image VARCHAR(255) DEFAULT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'published',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_blog_posts_created_at ON blog_posts (created_at);

CREATE TABLE IF NOT EXISTS tutorial_videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  video_url VARCHAR(255) NOT NULL,
  thumbnail_url VARCHAR(255) DEFAULT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'published',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_tutorial_videos_created_at ON tutorial_videos (created_at);

CREATE TABLE IF NOT EXISTS site_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- --- Demo catalog (matches legacy seed.sql; image paths relative to web root)

INSERT INTO products (tracking_code, sku, title, slug, category, price, short_description, image_url, stock) VALUES
('PRD-LEG-000001', 'ZY-INC-1001', 'دستگاه جوجه کشی صنعتی 2520 تایی', 'product-zy-inc-1001', 'طیور', 89000000, 'مناسب فارم‌های حرفه‌ای با کنترل هوشمند دما و رطوبت', 'assets/images/p1.jpg', 12),
('PRD-LEG-000002', 'ZY-AGR-2001', 'دستگاه دان خردکن صنعتی کشاورزی', 'product-zy-agr-2001', 'کشاورزی', 26500000, 'توان بالا برای واحدهای تولیدی و خوراک دام', 'assets/images/p2.jpg', 18),
('PRD-LEG-000003', 'ZY-SUN-3001', 'دستگاه پخت تخمه آفتابگردان مدل X9', 'product-zy-sun-3001', 'صنایع غذایی', 74500000, 'پخت یکنواخت با مصرف انرژی کنترل‌شده', 'assets/images/p3.jpg', 7),
('PRD-LEG-000004', 'ZY-INC-1002', 'ستر و هچر نیمه صنعتی 960 تایی', 'product-zy-inc-1002', 'طیور', 36000000, 'گزینه اقتصادی برای کارگاه‌های متوسط', 'assets/images/p4.jpg', 14);
