-- Migration: Full schema for receipt-printer
-- Database: epson_prints
-- Run once on a fresh MySQL instance

CREATE DATABASE IF NOT EXISTS epson_prints
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE epson_prints;

-- -------------------------------------------------------
-- Table: connections
-- Stores pairing between Shopify source and printer target
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS connections (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  source     VARCHAR(255)        DEFAULT NULL,
  target     VARCHAR(255)        DEFAULT NULL,
  code       VARCHAR(20)  NOT NULL UNIQUE,
  created_at DATETIME     NOT NULL DEFAULT NOW(),
  updated_at DATETIME     NOT NULL DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- Table: queues
-- Stores pending and processed print jobs
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS queues (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  order_id   VARCHAR(255) NOT NULL,
  order_name VARCHAR(255)        DEFAULT NULL,
  printer_ip VARCHAR(100) NOT NULL,
  shop       VARCHAR(255) NOT NULL,
  line_items JSON                DEFAULT NULL,
  status     ENUM('pending', 'processed') NOT NULL DEFAULT 'pending',
  created_at DATETIME     NOT NULL DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- Table: device_settings
-- Stores template/receipt settings keyed by connection code
-- -------------------------------------------------------
CREATE TABLE IF NOT EXISTS device_settings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  connection_code VARCHAR(20)  NOT NULL UNIQUE,
  settings        JSON         NOT NULL,
  created_at      DATETIME     NOT NULL DEFAULT NOW(),
  updated_at      DATETIME     NOT NULL DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
