SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `settings` (
  `key_name` VARCHAR(64) NOT NULL PRIMARY KEY,
  `value_text` TEXT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uploads` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `original_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `mime` VARCHAR(128) NOT NULL DEFAULT 'application/octet-stream',
  `size_bytes` INT UNSIGNED NOT NULL DEFAULT 0,
  `analysis_json` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_uploads_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `order_code` VARCHAR(32) NOT NULL,
  `customer_name` VARCHAR(120) NOT NULL,
  `customer_email` VARCHAR(180) NOT NULL,
  `customer_phone` VARCHAR(40) NULL,
  `upload_id` CHAR(36) NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_url` VARCHAR(500) NOT NULL,
  `material` VARCHAR(20) NOT NULL DEFAULT 'PLA',
  `color` VARCHAR(60) NULL,
  `quantity` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `volume_cm3` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `unit_price_try` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `total_price_try` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `status` ENUM('awaiting_payment','queued','assigned','printing','completed','cancelled') NOT NULL DEFAULT 'awaiting_payment',
  `payment_status` ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  `payment_provider` VARCHAR(32) NULL,
  `shopier_product_id` VARCHAR(64) NULL,
  `shopier_order_id` VARCHAR(64) NULL,
  `farm_synced` TINYINT(1) NOT NULL DEFAULT 0,
  `farm_response` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` DATETIME NULL DEFAULT NULL,
  UNIQUE KEY `uq_order_code` (`order_code`),
  INDEX `idx_orders_status` (`status`),
  INDEX `idx_orders_created` (`created_at`),
  CONSTRAINT `fk_orders_upload` FOREIGN KEY (`upload_id`) REFERENCES `uploads`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`key_name`, `value_text`) VALUES
  ('site_name', '3dbastir.com.tr'),
  ('site_url', 'https://3dbastir.com.tr'),
  ('sync_api_key', ''),
  ('payment_mode', 'shopier'),
  ('shopier_shop_slug', '3dbastir'),
  ('shopier_webhook_token', ''),
  ('shopier_osb_username', ''),
  ('shopier_osb_password', ''),
  ('mail_from', ''),
  ('mail_from_name', '')
ON DUPLICATE KEY UPDATE `value_text` = VALUES(`value_text`);

CREATE TABLE IF NOT EXISTS `workshop_users` (
  `id` CHAR(36) NOT NULL PRIMARY KEY,
  `username` VARCHAR(64) NOT NULL,
  `display_name` VARCHAR(120) NOT NULL,
  `password_salt` VARCHAR(64) NOT NULL,
  `password_hash` VARCHAR(128) NOT NULL,
  `role` ENUM('user','superadmin') NOT NULL DEFAULT 'user',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_workshop_username` (`username`),
  INDEX `idx_workshop_users_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `workshop_audit_logs` (
  `id` VARCHAR(64) NOT NULL PRIMARY KEY,
  `logged_at` DATETIME(3) NOT NULL,
  `level` VARCHAR(16) NOT NULL DEFAULT 'info',
  `category` VARCHAR(64) NOT NULL,
  `message` TEXT NOT NULL,
  `meta_json` TEXT NULL,
  `user_id` CHAR(36) NULL,
  `user_username` VARCHAR(64) NULL,
  `user_display_name` VARCHAR(120) NULL,
  `device_id` VARCHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_logged_at` (`logged_at` DESC),
  INDEX `idx_audit_user_id` (`user_id`),
  INDEX `idx_audit_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `catalog_categories` (
  `id` VARCHAR(64) NOT NULL PRIMARY KEY,
  `slug` VARCHAR(80) NOT NULL,
  `name_tr` VARCHAR(120) NOT NULL,
  `icon` VARCHAR(16) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY `uq_catalog_cat_slug` (`slug`),
  INDEX `idx_catalog_cat_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `catalog_products` (
  `id` VARCHAR(64) NOT NULL PRIMARY KEY,
  `category_id` VARCHAR(64) NOT NULL,
  `slug` VARCHAR(80) NOT NULL,
  `title_tr` VARCHAR(180) NOT NULL,
  `description_tr` TEXT NOT NULL,
  `image_url` VARCHAR(500) NOT NULL DEFAULT '',
  `stl_file` VARCHAR(255) NULL,
  `stl_url` VARCHAR(500) NULL,
  `makerworld_url` VARCHAR(500) NULL,
  `makerworld_id` VARCHAR(64) NULL,
  `material` VARCHAR(20) NOT NULL DEFAULT 'PLA',
  `suggested_color` VARCHAR(60) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY `uq_catalog_prod_slug` (`slug`),
  INDEX `idx_catalog_prod_category` (`category_id`),
  INDEX `idx_catalog_prod_sort` (`sort_order`),
  CONSTRAINT `fk_catalog_prod_category` FOREIGN KEY (`category_id`) REFERENCES `catalog_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;