-- Migration 010: Odoo integration sync columns
-- Adds Odoo ID mapping columns to existing tables and creates sync log.

-- Link customers to Odoo res.partner
ALTER TABLE `customers`
  ADD COLUMN `odoo_partner_id` int(11) DEFAULT NULL AFTER `override_enabled`;

-- Link invoices to Odoo account.move
ALTER TABLE `invoices`
  ADD COLUMN `odoo_move_id` int(11) DEFAULT NULL AFTER `qbo_txn_id`;

-- Link parts/products to Odoo product.product
ALTER TABLE `parts_catalog`
  ADD COLUMN `odoo_product_id` int(11) DEFAULT NULL AFTER `unit_price`;

-- Sync log for tracking push/pull operations
CREATE TABLE IF NOT EXISTS `odoo_sync_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `direction` enum('push','pull') NOT NULL,
  `model` varchar(64) NOT NULL,
  `local_id` int(11) DEFAULT NULL,
  `odoo_id` int(11) DEFAULT NULL,
  `status` enum('success','error') NOT NULL DEFAULT 'success',
  `message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`log_id`),
  KEY `idx_model_local` (`model`, `local_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;