USE cashless_db;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_code VARCHAR(20) NOT NULL UNIQUE,
  customer_id INT UNSIGNED DEFAULT NULL,
  cashier_id INT UNSIGNED DEFAULT NULL,
  order_type ENUM('dine_in','takeout','delivery') NOT NULL DEFAULT 'takeout',
  status ENUM('pending','preparing','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'pending',
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  customer_name VARCHAR(100) DEFAULT NULL,
  customer_phone VARCHAR(30) DEFAULT NULL,
  delivery_address VARCHAR(500) DEFAULT NULL,
  delivery_address_type ENUM('manual','current_location') DEFAULT NULL,
  delivery_city VARCHAR(120) DEFAULT NULL,
  delivery_barangay VARCHAR(120) DEFAULT NULL,
  delivery_street VARCHAR(255) DEFAULT NULL,
  delivery_building VARCHAR(150) DEFAULT NULL,
  delivery_landmark VARCHAR(255) DEFAULT NULL,
  delivery_instructions TEXT DEFAULT NULL,
  delivery_latitude DECIMAL(10,7) DEFAULT NULL,
  delivery_longitude DECIMAL(10,7) DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_cashier FOREIGN KEY (cashier_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_orders_status_created (status, created_at),
  INDEX idx_orders_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED DEFAULT NULL,
  product_name VARCHAR(120) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  line_total DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
  INDEX idx_items_order (order_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  method ENUM('cash','gcash','maya','card','visa','mastercard','bank','ewallet') NOT NULL DEFAULT 'cash',
  amount DECIMAL(10,2) NOT NULL,
  reference_no VARCHAR(80) DEFAULT NULL,
  status ENUM('pending','pending_verification','paid','failed','rejected','refunded') NOT NULL DEFAULT 'pending',
  currency CHAR(3) NOT NULL DEFAULT 'PHP',
  manual_reference_no VARCHAR(80) DEFAULT NULL,
  manual_reference_normalized VARCHAR(80) DEFAULT NULL,
  receipt_path VARCHAR(255) DEFAULT NULL,
  receipt_sha256 CHAR(64) DEFAULT NULL,
  ocr_reference_no VARCHAR(80) DEFAULT NULL,
  ocr_amount DECIMAL(10,2) DEFAULT NULL,
  ocr_transaction_at DATETIME DEFAULT NULL,
  ocr_transaction_status VARCHAR(30) DEFAULT NULL,
  ocr_status VARCHAR(20) NOT NULL DEFAULT 'unavailable',
  ocr_warnings VARCHAR(255) DEFAULT NULL,
  amount_mismatch TINYINT(1) NOT NULL DEFAULT 0,
  duplicate_reference_detected TINYINT(1) NOT NULL DEFAULT 0,
  duplicate_reference_candidate VARCHAR(80) DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  verified_by INT UNSIGNED DEFAULT NULL,
  rejected_at DATETIME DEFAULT NULL,
  rejected_by INT UNSIGNED DEFAULT NULL,
  rejection_reason VARCHAR(255) DEFAULT NULL,
  paid_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_user FOREIGN KEY (paid_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_rejected_by FOREIGN KEY (rejected_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_payments_manual_reference (manual_reference_no),
  UNIQUE KEY uq_payments_manual_reference_normalized (manual_reference_normalized),
  UNIQUE KEY uq_payments_receipt_sha256 (receipt_sha256),
  INDEX idx_payments_status_created (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_verification_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NOT NULL,
  actor_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(30) NOT NULL,
  from_status VARCHAR(30) DEFAULT NULL,
  to_status VARCHAR(30) NOT NULL,
  reason VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_history_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_payment_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_payment_history_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_payment_history_order_created (order_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED DEFAULT NULL,
  payment_id INT UNSIGNED DEFAULT NULL,
  type ENUM('sale','refund','void') NOT NULL DEFAULT 'sale',
  amount DECIMAL(10,2) NOT NULL,
  created_by INT UNSIGNED DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tx_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_tx_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
  CONSTRAINT fk_tx_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_tx_type_created (type, created_at)
) ENGINE=InnoDB;