-- Pangolin-Dashboard database schema
-- Database name: pangolin_dashboard (use this name to avoid hyphen in identifiers)
-- Run: mysql -u user -p < database/schema.sql
-- Or create DB first, then: mysql -u user -p pangolin_dashboard < database/schema_tables_only.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================================================
-- QBD orchestrator / system
-- =============================================================================

CREATE TABLE IF NOT EXISTS `qbd_system_status` (
  `id` int unsigned NOT NULL DEFAULT 1,
  `qb_online` tinyint(1) NOT NULL DEFAULT 1,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `qbd_system_status` (`id`, `qb_online`) VALUES (1, 1);

CREATE TABLE IF NOT EXISTS `qbd_process_lock` (
  `id` int unsigned NOT NULL DEFAULT 1,
  `holder` varchar(64) NOT NULL DEFAULT 'init',
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `qbd_process_lock` (`id`, `holder`) VALUES (1, 'init');

-- =============================================================================
-- QBE customer sync (Conductor QBD customers)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `qbe_customer_sync_runs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` datetime DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'failed',
  `action_summary` varchar(255) DEFAULT NULL,
  `error_message` text,
  PRIMARY KEY (`id`),
  KEY `idx_status_finished` (`status`, `finished_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `qbe_customer_email_map` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `qbe_customer_id` varchar(64) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `is_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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qbe_customer_id` (`qbe_customer_id`),
  KEY `idx_email` (`email`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- QBD purchase orders (headers) via Conductor
-- =============================================================================

CREATE TABLE IF NOT EXISTS `qbd_po_sync_runs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` datetime DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'failed',
  `action_summary` varchar(255) DEFAULT NULL,
  `error_message` text,
  PRIMARY KEY (`id`),
  KEY `idx_status_finished` (`status`, `finished_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `qbd_purchase_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `qbe_po_id` varchar(64) NOT NULL,
  `ref_number` varchar(64) DEFAULT NULL,
  `vendor_name` varchar(255) DEFAULT NULL,
  `txn_date` date DEFAULT NULL,
  `expected_date` date DEFAULT NULL,
  `total_amount` decimal(14,2) DEFAULT NULL,
  `is_closed` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(32) DEFAULT NULL,
  `revision_number` varchar(32) DEFAULT NULL,
  `raw_payload` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qbe_po_id` (`qbe_po_id`),
  KEY `idx_vendor_txn_date` (`vendor_name`(191), `txn_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- QBD sales orders (headers) via Conductor
-- =============================================================================

CREATE TABLE IF NOT EXISTS `qbd_so_sync_runs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` datetime DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'failed',
  `action_summary` varchar(255) DEFAULT NULL,
  `error_message` text,
  PRIMARY KEY (`id`),
  KEY `idx_status_finished` (`status`, `finished_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `qbd_sales_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `qbe_so_id` varchar(64) NOT NULL,
  `ref_number` varchar(64) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `txn_date` date DEFAULT NULL,
  `ship_date` date DEFAULT NULL,
  `total_amount` decimal(14,2) DEFAULT NULL,
  `balance_remaining` decimal(14,2) DEFAULT NULL,
  `is_fully_invoiced` tinyint(1) NOT NULL DEFAULT 0,
  `is_manually_closed` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(32) DEFAULT NULL,
  `revision_number` varchar(32) DEFAULT NULL,
  `raw_payload` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qbe_so_id` (`qbe_so_id`),
  KEY `idx_customer_txn_date` (`customer_name`(191), `txn_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- QBD reference data (classes, sales reps) — used by getClasses.php, getSalesReps.php
-- =============================================================================

CREATE TABLE IF NOT EXISTS `shop2qbd_classes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `shopify_store` varchar(64) NOT NULL DEFAULT 'pangolincom',
  `qbe_class_id` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `parent_class_id` varchar(64) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `revision_number` varchar(32) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_store_class` (`company_id`, `shopify_store`, `qbe_class_id`),
  KEY `idx_name_active` (`name`(191), `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop2qbd_sales_representatives` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `shopify_store` varchar(64) NOT NULL DEFAULT 'pangolincom',
  `qbe_sales_rep_id` varchar(64) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `initial` varchar(16) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `revision_number` varchar(32) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_store_rep` (`company_id`, `shopify_store`, `qbe_sales_rep_id`),
  KEY `idx_full_name` (`full_name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Optional: Shopify orders (only if you keep orchestrate_qbd Shopify flow)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `shopify_orders_raw` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `processed` tinyint(1) NOT NULL DEFAULT 0,
  `issue_flag` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pending` (`processed`, `issue_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Auth (for when you re-enable auth)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `shop2qbd_companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(64) NOT NULL,
  `is_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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop2qbd_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop2qbd_company_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role` varchar(32) NOT NULL DEFAULT 'user',
  `is_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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_user` (`company_id`, `user_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop2qbd_company_invites` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `email` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `role` varchar(32) NOT NULL DEFAULT 'user',
  `expires_at` datetime NOT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_token` (`token`),
  KEY `idx_company_email` (`company_id`, `email`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
