-- MKL Portal Database Schema (MariaDB) -- Database: mkl CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, google_id VARCHAR(255) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, display_name VARCHAR(255), rep_name VARCHAR(100) DEFAULT NULL, picture_url TEXT, slack_user_id VARCHAR(50) DEFAULT NULL, phone VARCHAR(50) DEFAULT NULL, role ENUM('admin', 'manager', 'user') DEFAULT 'user', is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_login TIMESTAMP NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS user_module_access ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, module_id VARCHAR(50) NOT NULL, access_level ENUM('none', 'view', 'edit', 'admin') DEFAULT 'view', granted_by INT, granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uq_user_module (user_id, module_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Vendor directory CREATE TABLE IF NOT EXISTS vendors ( id INT AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(255) NOT NULL, logo_url VARCHAR(500), solution_types JSON, is_partner TINYINT(1) DEFAULT 0, sourcing JSON, deal_reg TINYINT(1) DEFAULT 0, tier VARCHAR(100), website VARCHAR(500), notes TEXT, created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS vendor_contacts ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_id INT NOT NULL, name VARCHAR(200) NOT NULL, email VARCHAR(255), phone VARCHAR(50), title VARCHAR(150), notes TEXT, sort_order INT DEFAULT 0, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS vendor_materials ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_id INT NOT NULL, name VARCHAR(255) NOT NULL, url VARCHAR(500) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS vendor_files ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_id INT NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL, mime_type VARCHAR(100), file_size INT, label VARCHAR(255), uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Deal Desk -- Migration: ALTER TABLE deals MODIFY deal_number VARCHAR(30); CREATE TABLE IF NOT EXISTS deals ( id INT AUTO_INCREMENT PRIMARY KEY, deal_number VARCHAR(30) UNIQUE, zoho_quote_number VARCHAR(100) DEFAULT NULL, zoho_quote_id VARCHAR(100), deal_name VARCHAR(500), account_name VARCHAR(500), zoho_created_by VARCHAR(255), zoho_created_at TIMESTAMP NULL, zoho_data JSON, status ENUM('draft','awaiting_pricing','priced','sent') DEFAULT 'draft', notes TEXT, deal_registration VARCHAR(50) DEFAULT NULL, sub_total DECIMAL(12,2) DEFAULT 0, grand_total DECIMAL(12,2) DEFAULT 0, total_cost DECIMAL(12,2) DEFAULT 0, total_margin DECIMAL(12,2) DEFAULT 0, submitted_by INT, submitted_at TIMESTAMP NULL, reviewed_by INT, reviewed_at TIMESTAMP NULL, review_notes TEXT, -- MKL quoting module fields source ENUM('zoho','mkl') DEFAULT 'zoho', opportunity_id INT DEFAULT NULL, customer_org_id INT DEFAULT NULL, contact_name VARCHAR(255) DEFAULT NULL, contact_email VARCHAR(255) DEFAULT NULL, contact_phone VARCHAR(50) DEFAULT NULL, outcome ENUM('pending','won','lost','superseded') DEFAULT 'pending', sent_at TIMESTAMP NULL, pdf_path VARCHAR(500) DEFAULT NULL, -- Per-quote address (overrides customer_organizations address) billing_name VARCHAR(500) DEFAULT NULL, billing_attn VARCHAR(255) DEFAULT NULL, billing_street VARCHAR(500) DEFAULT NULL, billing_street2 VARCHAR(500) DEFAULT NULL, billing_city VARCHAR(255) DEFAULT NULL, billing_state VARCHAR(100) DEFAULT NULL, billing_zip VARCHAR(20) DEFAULT NULL, shipping_name VARCHAR(500) DEFAULT NULL, shipping_attn VARCHAR(255) DEFAULT NULL, shipping_street VARCHAR(500) DEFAULT NULL, shipping_street2 VARCHAR(500) DEFAULT NULL, shipping_city VARCHAR(255) DEFAULT NULL, shipping_state VARCHAR(100) DEFAULT NULL, shipping_zip VARCHAR(20) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Soft-delete (hard delete removed to prevent Zoho cascade accidents) deleted_at TIMESTAMP NULL DEFAULT NULL, deleted_by INT DEFAULT NULL, FOREIGN KEY (submitted_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL, -- Zoho sync tracking zoho_sync_status ENUM('synced','pending','failed') DEFAULT 'pending', zoho_sync_error TEXT DEFAULT NULL, -- Link to order when converted order_id INT DEFAULT NULL, -- Customer Application invite (sent from Deal Desk pre-PO) customer_app_invite_token VARCHAR(128) DEFAULT NULL, customer_app_email_sent_at TIMESTAMP NULL, -- Chrome License + ZTE form-email send timestamps (Deal Desk pre-PO) chrome_license_email_sent_at TIMESTAMP NULL, zte_email_sent_at TIMESTAMP NULL, INDEX idx_deal_source (source), INDEX idx_deal_opportunity (opportunity_id), INDEX idx_deal_outcome (outcome), INDEX idx_deal_customer_org (customer_org_id), INDEX idx_deal_deleted_at (deleted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_items ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, zoho_line_item_id VARCHAR(100), product_code VARCHAR(100), product_name VARCHAR(500), list_price DECIMAL(12,2) DEFAULT 0, new_price DECIMAL(12,2) DEFAULT 0, quantity DECIMAL(10,2) DEFAULT 1, amount DECIMAL(12,2) DEFAULT 0, discount DECIMAL(12,2) DEFAULT 0, discounted_unit_price DECIMAL(12,2) DEFAULT 0, total DECIMAL(12,2) DEFAULT 0, item_cost DECIMAL(12,2) DEFAULT 0, margin DECIMAL(12,2) DEFAULT 0, margin_percent DECIMAL(5,2) DEFAULT 0, notes TEXT, line_description TEXT DEFAULT NULL, sort_order INT DEFAULT 0, -- MKL quoting module fields vendor VARCHAR(50) DEFAULT NULL, vendor_sku VARCHAR(100) DEFAULT NULL, vendor_qty INT DEFAULT NULL, aue_date DATE DEFAULT NULL, is_accessory TINYINT(1) DEFAULT 0, tax_rate DECIMAL(7,5) DEFAULT NULL, FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Catalog status persistence (survives server restarts) CREATE TABLE IF NOT EXISTS catalog_status ( vendor_key VARCHAR(50) PRIMARY KEY, loaded TINYINT(1) DEFAULT 0, item_count INT DEFAULT 0, last_load TIMESTAMP NULL, last_error TEXT, validation_stats JSON, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Vendor sync audit log CREATE TABLE IF NOT EXISTS vendor_sync_log ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_key VARCHAR(50) NOT NULL, sync_type ENUM('catalog_load', 'vendor_sync', 'multivendor_rollup', 'backfill') NOT NULL, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP NULL, status ENUM('running', 'success', 'failed', 'blocked') DEFAULT 'running', items_processed INT DEFAULT 0, items_updated INT DEFAULT 0, error_message TEXT, details JSON, INDEX idx_vendor_sync (vendor_key, sync_type), INDEX idx_started (started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Auto-markup rules (behind AUTO_MARKUP_ENABLED feature flag) CREATE TABLE IF NOT EXISTS markup_rules ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, priority INT DEFAULT 100, vendor_key VARCHAR(50) DEFAULT NULL, manufacturer VARCHAR(255) DEFAULT NULL, product_type VARCHAR(255) DEFAULT NULL, cost_min DECIMAL(12,2) DEFAULT NULL, cost_max DECIMAL(12,2) DEFAULT NULL, markup_type ENUM('percentage', 'fixed', 'target_margin') NOT NULL DEFAULT 'percentage', markup_value DECIMAL(12,4) NOT NULL, min_margin_pct DECIMAL(5,2) DEFAULT NULL, max_price DECIMAL(12,2) DEFAULT NULL, enabled TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_priority (priority), INDEX idx_vendor (vendor_key), INDEX idx_enabled (enabled) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Endless Aisle per-tier pricing rules (Tier1 = D&H/TD Synnex/IM, Tier2 = HubX etc.) CREATE TABLE IF NOT EXISTS ea_pricing_rules ( id INT AUTO_INCREMENT PRIMARY KEY, tier ENUM('tier1', 'tier2') NOT NULL, min_margin_pct DECIMAL(5,2) NOT NULL DEFAULT 8.00, min_dollar_markup DECIMAL(10,2) NOT NULL DEFAULT 10.00, use_retail TINYINT(1) NOT NULL DEFAULT 1, rounding_style ENUM('five_or_nine', 'nine_only', 'none') NOT NULL DEFAULT 'five_or_nine', max_price_cap DECIMAL(12,2) DEFAULT NULL, updated_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_tier (tier) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- CA sales tax rates (zip code → combined rate lookup for /tax Slack command) CREATE TABLE IF NOT EXISTS ca_tax_rates ( id INT AUTO_INCREMENT PRIMARY KEY, zip_code VARCHAR(5) NOT NULL, city VARCHAR(255) NOT NULL, county VARCHAR(255) NOT NULL, state_rate DECIMAL(7,5) NOT NULL DEFAULT 0.07250, district_rate DECIMAL(7,5) NOT NULL DEFAULT 0.00000, combined_rate DECIMAL(7,5) NOT NULL, effective_date DATE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_zip (zip_code), INDEX idx_county (county) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Catalog table compound indexes (catalog table created by vendor sync, not here) -- These speed up verifyVendorLink() in vendor-backfill.js -- Run manually on live DB if not already present: -- CREATE INDEX IF NOT EXISTS idx_vendor_vendor_sku ON catalog(vendor, vendor_sku); -- CREATE INDEX IF NOT EXISTS idx_vendor_mfg_part ON catalog(vendor, mfg_part); CREATE TABLE IF NOT EXISTS deal_files ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, file_size INT DEFAULT 0, uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_comments ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, user_id INT, content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_deal_created (deal_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Shopify variant cache (local copy of Shopify product/variant data for DB-centric vendor linking) CREATE TABLE IF NOT EXISTS shopify_variants ( variant_id BIGINT PRIMARY KEY, variant_gid VARCHAR(100) NOT NULL, product_id BIGINT NOT NULL, product_gid VARCHAR(100) NOT NULL, inventory_item_id BIGINT, inventory_item_gid VARCHAR(100), sku VARCHAR(100), title VARCHAR(500), product_title VARCHAR(500), product_type VARCHAR(100) DEFAULT NULL, product_status VARCHAR(20) DEFAULT NULL, price DECIMAL(12,2), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_sku (sku), INDEX idx_product (product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Vendor↔variant links (replaces Shopify metafield-based vendor linking) CREATE TABLE IF NOT EXISTS product_vendor_links ( id INT AUTO_INCREMENT PRIMARY KEY, variant_id BIGINT NOT NULL, vendor VARCHAR(50) NOT NULL, vendor_sku VARCHAR(100) NOT NULL, confidence INT DEFAULT 100, linked_by ENUM('auto','manual','migration') DEFAULT 'auto', linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_variant_vendor (variant_id, vendor), INDEX idx_vendor_sku (vendor, vendor_sku), INDEX idx_variant (variant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Vendor link exclusions (prevents auto-linker from re-creating manually removed links) CREATE TABLE IF NOT EXISTS vendor_link_exclusions ( id INT AUTO_INCREMENT PRIMARY KEY, variant_id BIGINT NOT NULL, vendor VARCHAR(50) NOT NULL, reason VARCHAR(255) DEFAULT NULL, excluded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, excluded_by VARCHAR(100) DEFAULT NULL, UNIQUE KEY uq_variant_vendor_excl (variant_id, vendor), INDEX idx_vendor (vendor) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Rollup snapshot (tracks last-pushed qty/cost per variant for change detection) CREATE TABLE IF NOT EXISTS rollup_snapshot ( variant_id BIGINT PRIMARY KEY, total_qty INT NOT NULL DEFAULT 0, avg_cost DECIMAL(12,2) NOT NULL DEFAULT 0, zero_since TIMESTAMP NULL DEFAULT NULL, synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Shopify Payments fee export tracking (prevents duplicate MYOB journal exports) CREATE TABLE IF NOT EXISTS shopify_fee_exports ( payout_id BIGINT PRIMARY KEY, payout_date DATE NOT NULL, fee_amount DECIMAL(10,2) NOT NULL, payout_amount DECIMAL(10,2) NOT NULL, filename VARCHAR(255), exported_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Email archive sync state (per-mailbox tracking for AI training data collection) CREATE TABLE IF NOT EXISTS email_archive_sync ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, display_name VARCHAR(255) NOT NULL, category VARCHAR(50) NOT NULL, label_path VARCHAR(500) NOT NULL, last_history_id VARCHAR(50) DEFAULT NULL, last_sync_at TIMESTAMP NULL, messages_synced INT DEFAULT 0, backfill_complete TINYINT(1) DEFAULT 0, backfill_started_at TIMESTAMP NULL, backfill_completed_at TIMESTAMP NULL, status ENUM('idle', 'syncing', 'backfilling', 'error') DEFAULT 'idle', last_error TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_email (email), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Email archive dedup log (prevents double-copying messages to api@) CREATE TABLE IF NOT EXISTS email_archive_messages ( id BIGINT AUTO_INCREMENT PRIMARY KEY, source_email VARCHAR(255) NOT NULL, source_message_id VARCHAR(100) NOT NULL, archived_message_id VARCHAR(100) DEFAULT NULL, label_id VARCHAR(100) DEFAULT NULL, archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_source_msg (source_email, source_message_id), INDEX idx_source_email (source_email), INDEX idx_archived_at (archived_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Gmail handler dedup log — message IDs that have been dispatched to the -- handler pipeline. Pub/Sub is at-least-once and the history API can replay -- ids, so handlers would otherwise re-fire (e.g. 40 identical Slack pings). CREATE TABLE IF NOT EXISTS gmail_processed_messages ( message_id VARCHAR(128) NOT NULL PRIMARY KEY, subject VARCHAR(500) DEFAULT NULL, from_addr VARCHAR(255) DEFAULT NULL, processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_processed_at (processed_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════ -- Customers module — unified customer database -- Merges Zoho CRM (Contacts + Leads + Accounts) and ActiveCampaign -- ══════════════════════════════════════════════════════════════ -- Customer organizations (maps to Zoho Accounts — one school/company per row) CREATE TABLE IF NOT EXISTS customer_organizations ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_account_id VARCHAR(50) UNIQUE DEFAULT NULL, name VARCHAR(500) NOT NULL, phone VARCHAR(50), tech_contact_name VARCHAR(255) DEFAULT NULL, tech_contact_phone VARCHAR(50) DEFAULT NULL, tech_contact_email VARCHAR(255) DEFAULT NULL, secondary_email VARCHAR(255) DEFAULT NULL, zte_client_id VARCHAR(255) DEFAULT NULL, zte_enrollment_token TEXT DEFAULT NULL, website VARCHAR(500), billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_zip VARCHAR(20), billing_country VARCHAR(100) DEFAULT 'US', shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_zip VARCHAR(20), shipping_country VARCHAR(100) DEFAULT 'US', ap_first_name VARCHAR(100), ap_last_name VARCHAR(100), ap_title VARCHAR(150), ap_phone VARCHAR(50), ap_email VARCHAR(255), ap_invoice_preference VARCHAR(100), school_type VARCHAR(100), enrollment INT DEFAULT NULL, is_district TINYINT(1) DEFAULT 0, is_school TINYINT(1) DEFAULT 0, district VARCHAR(255), county VARCHAR(255), region VARCHAR(50), checkout_approved TINYINT(1) DEFAULT 1, tax_exempt TINYINT(1) DEFAULT 0, tax_exempt_number VARCHAR(100), tax_expiration DATE DEFAULT NULL, default_tax_rate DECIMAL(7,5) NULL COMMENT 'Per-org tax rate override (e.g. Suncoast Prep = 0.07750). NULL = use zip lookup.', chrome_license_date DATE DEFAULT NULL, chrome_license_enrollment VARCHAR(255), chrome_management_console VARCHAR(255), chrome_primary_email VARCHAR(255) DEFAULT NULL, total_spent DECIMAL(12,2) DEFAULT 0, first_invoice_date DATE DEFAULT NULL, last_invoice_date DATE DEFAULT NULL, rep VARCHAR(100) DEFAULT NULL, intent_topic VARCHAR(255) DEFAULT NULL, topic_score INT DEFAULT NULL, delta_trend INT DEFAULT NULL, intent_data VARCHAR(100) DEFAULT NULL, seamless_notes TEXT DEFAULT NULL, nces_id VARCHAR(12) DEFAULT NULL, notes TEXT, zte_customer_id VARCHAR(255) DEFAULT NULL, zte_company_id VARCHAR(50) DEFAULT NULL, zte_enrollment_token TEXT DEFAULT NULL, google_admin_refresh_token TEXT DEFAULT NULL, google_admin_token_email VARCHAR(255) DEFAULT NULL, google_admin_authorized_at TIMESTAMP NULL, customer_application_on_file TINYINT(1) NOT NULL DEFAULT 0, default_fulfillment_type ENUM("hq","dropship","hybrid") DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_org_name (name(191)), INDEX idx_org_region (region), INDEX idx_org_nces (nces_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customers — one row per person (email is the natural dedup key) CREATE TABLE IF NOT EXISTS customers ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), phone VARCHAR(50), mobile VARCHAR(50), title VARCHAR(255), org_id INT DEFAULT NULL, street VARCHAR(500), city VARCHAR(255), state VARCHAR(100), zip VARCHAR(20), country VARCHAR(100) DEFAULT 'US', assigned_rep_id INT DEFAULT NULL, region VARCHAR(50), time_zone VARCHAR(100), zoho_contact_id VARCHAR(50) DEFAULT NULL, zoho_lead_id VARCHAR(50) DEFAULT NULL, ac_contact_id VARCHAR(50) DEFAULT NULL, total_spent DECIMAL(12,2) DEFAULT 0, last_purchase_date DATE DEFAULT NULL, lead_source VARCHAR(255), email_status ENUM('active','unsubscribed','bounced','complained','suppressed','pending') DEFAULT 'active', subscribed_at TIMESTAMP NULL, unsubscribed_at TIMESTAMP NULL, engagement_score INT DEFAULT 0, engagement_level ENUM('none','low','medium','high') DEFAULT 'none', last_email_sent_at TIMESTAMP NULL, last_email_opened_at TIMESTAMP NULL, last_email_clicked_at TIMESTAMP NULL, last_interaction_at TIMESTAMP NULL, device_imacs INT DEFAULT 0, device_ipads INT DEFAULT 0, device_macbook_air INT DEFAULT 0, device_macbook_pro INT DEFAULT 0, device_chromebooks INT DEFAULT 0, device_windows INT DEFAULT 0, device_apple_tv INT DEFAULT 0, device_smart_boards INT DEFAULT 0, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_email (email), UNIQUE KEY uq_zoho_contact (zoho_contact_id), INDEX idx_zoho_lead (zoho_lead_id), INDEX idx_ac_contact (ac_contact_id), INDEX idx_org (org_id), INDEX idx_rep (assigned_rep_id), INDEX idx_region (region), INDEX idx_email_status (email_status), INDEX idx_name (last_name, first_name), FOREIGN KEY (org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (assigned_rep_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customer tags (shared with future Email Marketing module for segmentation) CREATE TABLE IF NOT EXISTS customer_tags ( id INT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(100) NOT NULL, label VARCHAR(200) NOT NULL, category VARCHAR(50) DEFAULT NULL, color VARCHAR(20) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_slug (slug), INDEX idx_category (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customer-tag assignments (many-to-many) CREATE TABLE IF NOT EXISTS customer_tag_assignments ( customer_id INT NOT NULL, tag_id INT NOT NULL, assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (customer_id, tag_id), FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES customer_tags(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customer lists (maps to ActiveCampaign lists: Main List, Customers, Wholesale, etc.) CREATE TABLE IF NOT EXISTS customer_lists ( id INT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(100) NOT NULL, name VARCHAR(200) NOT NULL, description TEXT, ac_list_id VARCHAR(50) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customer list memberships CREATE TABLE IF NOT EXISTS customer_list_members ( customer_id INT NOT NULL, list_id INT NOT NULL, status ENUM('active','unsubscribed','bounced') DEFAULT 'active', added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (customer_id, list_id), FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (list_id) REFERENCES customer_lists(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Customer sync tracking (migration/import progress per source) CREATE TABLE IF NOT EXISTS customer_sync_log ( id INT AUTO_INCREMENT PRIMARY KEY, source ENUM('zoho_contacts','zoho_leads','zoho_accounts','activecampaign') NOT NULL, sync_type ENUM('full','incremental','merge') NOT NULL, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP NULL, status ENUM('running','success','failed') DEFAULT 'running', records_processed INT DEFAULT 0, records_created INT DEFAULT 0, records_updated INT DEFAULT 0, records_skipped INT DEFAULT 0, error_message TEXT, details JSON, INDEX idx_source_type (source, sync_type), INDEX idx_started (started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════ -- Buyback Quoting module — device buyback pricing and quoting -- ══════════════════════════════════════════════════════════════ -- Quote number sequences (auto-incrementing quote numbers with MTQ prefix) CREATE TABLE IF NOT EXISTS buyback_quote_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 107000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Buyback quotes — one row per quote CREATE TABLE IF NOT EXISTS buyback_quotes ( id INT AUTO_INCREMENT PRIMARY KEY, quote_number VARCHAR(20) UNIQUE NOT NULL, quote_type ENUM('flat','graded') NOT NULL DEFAULT 'graded', status ENUM('draft','sent','signed','in_progress','completed','paid','cancelled') DEFAULT 'draft', customer_name VARCHAR(255), customer_org VARCHAR(500), customer_email VARCHAR(255), customer_phone VARCHAR(50), customer_address_street VARCHAR(500), customer_address_city VARCHAR(255), customer_address_state VARCHAR(100), customer_address_zip VARCHAR(20), tts_contact_name VARCHAR(255), tts_contact_email VARCHAR(255), tts_contact_phone VARCHAR(50), quote_date DATE, valid_until DATE, shipped_by DATE, total_devices INT DEFAULT 0, quote_total DECIMAL(12,2) DEFAULT 0, high_offer DECIMAL(12,2) DEFAULT 0, notes TEXT, internal_notes TEXT, custom_terms TEXT COMMENT 'JSON array of {title, text} sections for custom T&Cs on PDF, NULL = use defaults', zoho_account_id VARCHAR(50) NULL, zoho_contact_id VARCHAR(50) NULL, zoho_quote_id VARCHAR(50) NULL, created_by INT, sent_at TIMESTAMP NULL, signed_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_customer_name (customer_name), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Buyback quote line items — one row per device model per quote CREATE TABLE IF NOT EXISTS buyback_quote_items ( id INT AUTO_INCREMENT PRIMARY KEY, quote_id INT NOT NULL, model_number VARCHAR(255), description VARCHAR(500), device_category ENUM('portable','tablet','all_in_one','desktop','other') DEFAULT 'portable', quantity INT DEFAULT 0, unit_price DECIMAL(10,2) DEFAULT NULL, price_grade_a DECIMAL(10,2) DEFAULT NULL, price_grade_b DECIMAL(10,2) DEFAULT NULL, price_grade_c DECIMAL(10,2) DEFAULT NULL, price_grade_d DECIMAL(10,2) DEFAULT NULL, line_total DECIMAL(12,2) DEFAULT 0, high_offer DECIMAL(12,2) DEFAULT 0, sort_order INT DEFAULT 0, notes TEXT, FOREIGN KEY (quote_id) REFERENCES buyback_quotes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Buyback files — generated PDFs + uploaded signed documents CREATE TABLE IF NOT EXISTS buyback_files ( id INT AUTO_INCREMENT PRIMARY KEY, quote_id INT NOT NULL, file_type ENUM('quote_pdf','signed_pdf','attachment') NOT NULL DEFAULT 'quote_pdf', filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, file_size INT DEFAULT 0, uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (quote_id) REFERENCES buyback_quotes(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Buyback devices — individual device serial tracking (stub for future Warehouse module) CREATE TABLE IF NOT EXISTS buyback_devices ( id INT AUTO_INCREMENT PRIMARY KEY, quote_id INT, item_id INT, serial_number VARCHAR(100), model_name VARCHAR(500), model_identifier VARCHAR(255), cosmetic_grade ENUM('A','B','C','D','F') DEFAULT NULL, functional_status ENUM('pass','fail','untested') DEFAULT 'untested', notes TEXT, received_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (quote_id) REFERENCES buyback_quotes(id) ON DELETE SET NULL, FOREIGN KEY (item_id) REFERENCES buyback_quote_items(id) ON DELETE SET NULL, INDEX idx_serial (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Google AUE (Auto Update Expiration) dates for Chrome OS devices CREATE TABLE IF NOT EXISTS google_aue_dates ( id INT AUTO_INCREMENT PRIMARY KEY, manufacturer VARCHAR(255) NOT NULL, product_name VARCHAR(500) NOT NULL, aue_date DATE NOT NULL, model_numbers VARCHAR(500) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_mfg_product (manufacturer, product_name), INDEX idx_manufacturer (manufacturer), INDEX idx_aue_date (aue_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── RFQ (Request for Quote) Log ─── CREATE TABLE IF NOT EXISTS rfq_log ( id INT AUTO_INCREMENT PRIMARY KEY, sender_email VARCHAR(255) NOT NULL, sender_name VARCHAR(255), vendor_key VARCHAR(50) NOT NULL, vendor_email VARCHAR(255) NOT NULL, vendor_sku VARCHAR(100), mfg_part VARCHAR(100), manufacturer VARCHAR(200), description TEXT, quantity INT NOT NULL, message TEXT, gmail_message_id VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_vendor (vendor_key), INDEX idx_sender (sender_email), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Shipment Notification Ingestion ─── -- Master table: one row per ingested notification (email or API poll) CREATE TABLE IF NOT EXISTS shipment_notifications ( id INT AUTO_INCREMENT PRIMARY KEY, source ENUM('email','api') DEFAULT 'email', source_id VARCHAR(150) NOT NULL, source_thread_id VARCHAR(100), vendor_key VARCHAR(50) NOT NULL, sender_email VARCHAR(255), subject VARCHAR(500), received_at TIMESTAMP NULL, raw_filename VARCHAR(255), raw_file_size INT DEFAULT 0, parse_status ENUM('pending','parsed','partial','failed') DEFAULT 'pending', parse_error TEXT, invoices_found INT DEFAULT 0, details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_source_id (source_id), INDEX idx_source (source), INDEX idx_vendor (vendor_key), INDEX idx_parse_status (parse_status), INDEX idx_received (received_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Parsed invoices from shipment notifications CREATE TABLE IF NOT EXISTS shipment_invoices ( id INT AUTO_INCREMENT PRIMARY KEY, notification_id INT NOT NULL, vendor_key VARCHAR(50) NOT NULL, invoice_number VARCHAR(100), invoice_date DATE, po_number VARCHAR(100), sp_number VARCHAR(50), mts_number VARCHAR(50), sales_order_number VARCHAR(100), vendor_order_number VARCHAR(100), ship_to_name VARCHAR(500), ship_to_address TEXT, carrier VARCHAR(100), tracking_number VARCHAR(255), ship_date DATE, carton_count INT, subtotal DECIMAL(12,2), freight DECIMAL(12,2), handling DECIMAL(12,2), sales_tax DECIMAL(12,2), total_invoice DECIMAL(12,2), details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (notification_id) REFERENCES shipment_notifications(id) ON DELETE CASCADE, INDEX idx_notification (notification_id), INDEX idx_invoice_number (invoice_number), INDEX idx_po_number (po_number), INDEX idx_sp_number (sp_number), INDEX idx_mts_number (mts_number), INDEX idx_tracking (tracking_number), INDEX idx_vendor_date (vendor_key, invoice_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Line items within each invoice CREATE TABLE IF NOT EXISTS shipment_invoice_items ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, line_number INT, vendor_sku VARCHAR(100), mfg_part VARCHAR(100), description VARCHAR(500), quantity INT DEFAULT 0, unit_price DECIMAL(12,2), amount DECIMAL(12,2), serial_numbers JSON, tracking_number VARCHAR(255), details JSON, FOREIGN KEY (invoice_id) REFERENCES shipment_invoices(id) ON DELETE CASCADE, INDEX idx_invoice (invoice_id), INDEX idx_vendor_sku (vendor_sku), INDEX idx_mfg_part (mfg_part) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Outbound Shipping Labels (created via ShipStation or direct carrier APIs) ─── CREATE TABLE IF NOT EXISTS shipping_labels ( id INT AUTO_INCREMENT PRIMARY KEY, order_id VARCHAR(50), shipment_ref VARCHAR(50), shipstation_shipment_id VARCHAR(100), shipstation_order_id VARCHAR(100), carrier_code VARCHAR(50) NOT NULL, carrier_name VARCHAR(100), service_code VARCHAR(100), service_name VARCHAR(200), tracking_number VARCHAR(255), label_data LONGBLOB, label_format VARCHAR(10) DEFAULT 'pdf', insurance_cost DECIMAL(10,2), ship_cost DECIMAL(10,2), ship_date DATE, voided BOOLEAN DEFAULT FALSE, voided_at TIMESTAMP NULL, weight_oz INT, dimensions_json JSON, ship_from_json JSON, ship_to_json JSON, status ENUM('created','shipped','in_transit','delivered','returned','voided','exception') DEFAULT 'created', created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_tracking (tracking_number), INDEX idx_ss_shipment (shipstation_shipment_id), INDEX idx_status (status), FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Shipping Tracking Events (from ShipStation webhooks + direct carrier polling) ─── CREATE TABLE IF NOT EXISTS shipping_tracking_events ( id INT AUTO_INCREMENT PRIMARY KEY, label_id INT, tracking_number VARCHAR(255) NOT NULL, carrier VARCHAR(50), event_timestamp TIMESTAMP NOT NULL, status_code VARCHAR(50), status_description VARCHAR(500), city VARCHAR(100), state VARCHAR(50), zip VARCHAR(20), country VARCHAR(10) DEFAULT 'US', source ENUM('shipstation','ups','fedex','usps','shippo','manual') DEFAULT 'shipstation', raw_event JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_label (label_id), INDEX idx_tracking (tracking_number), INDEX idx_timestamp (event_timestamp), UNIQUE INDEX idx_tracking_dedup (tracking_number, event_timestamp, status_code), FOREIGN KEY (label_id) REFERENCES shipping_labels(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Wilbur Pigs (Mac Mini stations for device receiving) ─── CREATE TABLE IF NOT EXISTS wilbur_pigs ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, hostname VARCHAR(255), ip_address VARCHAR(45), api_key VARCHAR(64) NOT NULL, is_active TINYINT(1) DEFAULT 1, charge_threshold_ma INT DEFAULT 100, sync_threshold_ma INT DEFAULT 500, assigned_reference VARCHAR(50) DEFAULT NULL, assigned_reference_type ENUM('buyback','purchase') DEFAULT NULL, last_heartbeat TIMESTAMP NULL, agent_version VARCHAR(20), notes TEXT, legacy_machine_id INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_pig_name (name), UNIQUE KEY uq_pig_api_key (api_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Wilbur Hubs (Cambrionix USB hubs attached to pigs) ─── CREATE TABLE IF NOT EXISTS wilbur_hubs ( id INT AUTO_INCREMENT PRIMARY KEY, pig_id INT NOT NULL, hub_serial VARCHAR(100) NOT NULL, model VARCHAR(100), port_count INT DEFAULT 0, slot TINYINT DEFAULT 0, firmware_version VARCHAR(50), is_active TINYINT(1) DEFAULT 1, first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_seen TIMESTAMP NULL, FOREIGN KEY (pig_id) REFERENCES wilbur_pigs(id) ON DELETE CASCADE, UNIQUE KEY uq_hub_serial (hub_serial), INDEX idx_pig (pig_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Wilbur Command Queue (MKL → agent via heartbeat) ─── CREATE TABLE IF NOT EXISTS wilbur_commands ( id INT AUTO_INCREMENT PRIMARY KEY, pig_id INT NOT NULL, hub_serial VARCHAR(100) NOT NULL, port_number INT NOT NULL, ecid VARCHAR(50), device_serial VARCHAR(50), action ENUM('get_info','restore','erase','revive','activate','prepare','self_update','clear_ipsw_cache','power_cycle','list_devices','restore_recovery') NOT NULL, params JSON, status ENUM('pending','sent','running','completed','failed','cancelled') DEFAULT 'pending', dispatched_by INT, batch_id VARCHAR(36), result JSON, error_message TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, sent_at TIMESTAMP NULL, started_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, FOREIGN KEY (pig_id) REFERENCES wilbur_pigs(id) ON DELETE CASCADE, FOREIGN KEY (dispatched_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_pig_status (pig_id, status), INDEX idx_batch (batch_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Wilbur Device Snapshots (cfgutil get_info results + Cambrionix data) ─── CREATE TABLE IF NOT EXISTS wilbur_device_snapshots ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(50) NOT NULL, ecid VARCHAR(50), pig_id INT, hub_serial VARCHAR(100), port_number INT, model_name VARCHAR(200), model_code VARCHAR(50), os_version VARCHAR(50), battery_level INT, battery_health INT, storage_total_gb DECIMAL(10,2), storage_available_gb DECIMAL(10,2), activation_locked TINYINT(1), mdm_enrolled TINYINT(1), supervised TINYINT(1), cfgutil_data JSON, cambrionix_data JSON, snapshot_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (pig_id) REFERENCES wilbur_pigs(id) ON DELETE SET NULL, INDEX idx_serial (device_serial), INDEX idx_ecid (ecid), INDEX idx_pig_port (pig_id, hub_serial, port_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Case Compatibility (direct brand case → device model mapping) ──────────── CREATE TABLE IF NOT EXISTS case_products ( id INT AUTO_INCREMENT PRIMARY KEY, brand VARCHAR(50) NOT NULL, sku VARCHAR(100) NOT NULL, product_title VARCHAR(500) NOT NULL, case_type VARCHAR(50) DEFAULT 'hardshell', msrp DECIMAL(10,2) DEFAULT NULL, cost_price DECIMAL(10,2) DEFAULT NULL, available TINYINT(1) DEFAULT 1, shopify_product_id BIGINT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_brand_sku (brand, sku), INDEX idx_brand (brand), INDEX idx_shopify_product (shopify_product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS case_compatibility ( id INT AUTO_INCREMENT PRIMARY KEY, case_product_id INT NOT NULL, device_manufacturer VARCHAR(100) NOT NULL, device_model VARCHAR(255) NOT NULL, device_model_numbers VARCHAR(500) DEFAULT NULL, form_factor VARCHAR(20) DEFAULT NULL, screen_size VARCHAR(10) DEFAULT NULL, confidence INT DEFAULT 100, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (case_product_id) REFERENCES case_products(id) ON DELETE CASCADE, UNIQUE KEY uq_case_device (case_product_id, device_manufacturer, device_model), INDEX idx_mfg_model (device_manufacturer, device_model) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Safe migrations (idempotent column additions for existing tables) ─── ALTER TABLE shopify_variants ADD COLUMN IF NOT EXISTS product_status VARCHAR(20) DEFAULT NULL AFTER product_type; ALTER TABLE shopify_variants ADD COLUMN IF NOT EXISTS continue_selling TINYINT(1) DEFAULT 0 AFTER price; ALTER TABLE shopify_variants ADD COLUMN IF NOT EXISTS vendor VARCHAR(255) DEFAULT NULL AFTER product_type; ALTER TABLE rollup_snapshot ADD COLUMN IF NOT EXISTS eol_since TIMESTAMP NULL DEFAULT NULL AFTER zero_since; ALTER TABLE vendor_sync_log MODIFY COLUMN sync_type ENUM('catalog_load','vendor_sync','multivendor_rollup','backfill','inventory_health','eol_detection') NOT NULL; -- ─── Forms Module ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS form_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 100001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS form_submissions ( id INT AUTO_INCREMENT PRIMARY KEY, app_number VARCHAR(20) UNIQUE NOT NULL, form_type VARCHAR(50) NOT NULL DEFAULT 'customer_application', status ENUM('pending_signature','signed','processing','completed','expired','cancelled') DEFAULT 'pending_signature', -- Section I: Organization Info legal_name VARCHAR(500) NOT NULL, dba_trade_name VARCHAR(500), billing_street VARCHAR(500) NOT NULL, billing_city VARCHAR(255) NOT NULL, billing_state VARCHAR(100) NOT NULL, billing_zip VARCHAR(20) NOT NULL, org_type ENUM('public_school_district','charter_for_profit','charter_non_profit','private_for_profit','private_non_profit','other') NOT NULL, tax_status ENUM('taxable','tax_exempt') NOT NULL, exemption_expiration DATE DEFAULT NULL, exemption_no_expiration TINYINT(1) NOT NULL DEFAULT 0, time_established ENUM('less_than_1','1_to_3','3_to_5','more_than_5') NOT NULL, federal_tax_id VARCHAR(50) NOT NULL, cmo_emo VARCHAR(500), -- Section II: AP Contact ap_first_name VARCHAR(100) NOT NULL, ap_last_name VARCHAR(100) NOT NULL, ap_title VARCHAR(200) NOT NULL, ap_email VARCHAR(255) NOT NULL, ap_phone VARCHAR(50) NOT NULL, -- Section III: Authorized Signer (filled by AP person) signer_first_name VARCHAR(100) NOT NULL, signer_last_name VARCHAR(100) NOT NULL, signer_title VARCHAR(200) NOT NULL, signer_email VARCHAR(255) NOT NULL, signer_phone VARCHAR(50) NOT NULL, -- Signing audit trail signing_token VARCHAR(128) UNIQUE, signing_token_expires_at TIMESTAMP NULL, signature_data LONGTEXT, signature_hash VARCHAR(128), signed_at TIMESTAMP NULL, signer_ip VARCHAR(45), signer_user_agent TEXT, -- Territory routing territory VARCHAR(50), assigned_salesperson VARCHAR(255), assigned_salesperson_email VARCHAR(255), -- AI + Zoho ai_credit_analysis TEXT, zoho_account_id VARCHAR(50), zoho_account_status ENUM('found','created','not_found','error'), -- PDF pdf_filename VARCHAR(255), -- Optional per-application T&C override. JSON array of {title, text, bullets?}. -- Null = use default TERMS_AND_CONDITIONS from core/lib/customer-app-pdf.js. custom_terms_json JSON NULL, -- Review workflow review_status ENUM('pending_review','approved','approved_with_conditions','denied') DEFAULT NULL, review_notes TEXT, terms ENUM('net_30','prepay_50_net_0','prepay_50_net_30','prepay_100') DEFAULT NULL, reviewed_by INT DEFAULT NULL, reviewed_at TIMESTAMP NULL, credit_approval_id INT DEFAULT NULL, linked_order_id INT DEFAULT NULL, linked_deal_id INT DEFAULT NULL, -- Audit submitted_ip VARCHAR(45), submitted_user_agent TEXT, submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP NULL, INDEX idx_form_type (form_type), INDEX idx_status (status), INDEX idx_signing_token (signing_token), INDEX idx_legal_name (legal_name(191)), INDEX idx_linked_order (linked_order_id), INDEX idx_review_status (review_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════ -- Orders module — MKL order management (migrated from Zoho CRM) -- Source: Zoho Invoices + Quotes + Purchase Orders -- Universal ID: MTS_Quote_Number (e.g., MTS79750) -- ══════════════════════════════════════════════════════════════ -- Orders — one row per MTS order (Quote → Invoice lifecycle) CREATE TABLE IF NOT EXISTS orders ( id INT AUTO_INCREMENT PRIMARY KEY, mts_number VARCHAR(20) NOT NULL, zoho_quote_id VARCHAR(50) DEFAULT NULL, zoho_invoice_id VARCHAR(50) DEFAULT NULL, status ENUM('new','awaiting','customer_response','approved','on_order','in_transit','fulfillment','picked','ready_to_ship','partially_shipped','shipped','delivered','complete','closed','cancelled','return','inbound','in_process') DEFAULT 'new', fulfillment_type ENUM('hq','dropship','hybrid') DEFAULT NULL, zoho_invoice_status VARCHAR(100) DEFAULT NULL, zoho_open_orders_status VARCHAR(50) DEFAULT NULL, zoho_quote_stage VARCHAR(50) DEFAULT NULL, customer_name VARCHAR(255) DEFAULT NULL, customer_org_id INT DEFAULT NULL, contact_name VARCHAR(255) DEFAULT NULL, contact_email VARCHAR(255) DEFAULT NULL, contact_phone VARCHAR(50) DEFAULT NULL, tech_contact_name VARCHAR(255) DEFAULT NULL, tech_contact_phone VARCHAR(50) DEFAULT NULL, secondary_email VARCHAR(255) DEFAULT NULL, zte_client_id VARCHAR(255) DEFAULT NULL, zte_enrollment_token TEXT DEFAULT NULL, rep VARCHAR(100) DEFAULT NULL, order_date DATE DEFAULT NULL, invoice_date DATE DEFAULT NULL, ship_date DATE DEFAULT NULL, ship_by_date DATE DEFAULT NULL, po_number VARCHAR(100) DEFAULT NULL, shopify_order_number VARCHAR(50) DEFAULT NULL, lead_source VARCHAR(100) DEFAULT NULL, ship_method VARCHAR(100) DEFAULT NULL, bill_to_name VARCHAR(255) DEFAULT NULL, ship_to_name VARCHAR(255) DEFAULT NULL, billing_street VARCHAR(255) DEFAULT NULL, billing_city VARCHAR(100) DEFAULT NULL, billing_state VARCHAR(50) DEFAULT NULL, billing_zip VARCHAR(20) DEFAULT NULL, shipping_street VARCHAR(255) DEFAULT NULL, shipping_city VARCHAR(100) DEFAULT NULL, shipping_state VARCHAR(50) DEFAULT NULL, shipping_zip VARCHAR(20) DEFAULT NULL, subtotal DECIMAL(12,2) DEFAULT NULL, tax_amount DECIMAL(12,2) DEFAULT NULL, adjustment DECIMAL(12,2) DEFAULT NULL, grand_total DECIMAL(12,2) DEFAULT NULL, total_cost DECIMAL(12,2) DEFAULT NULL, cost_source ENUM('spreadsheet','po_match','manual','computed') DEFAULT NULL, cost_notes TEXT DEFAULT NULL, cost_updated_at TIMESTAMP NULL, cost_updated_by INT DEFAULT NULL, paid TINYINT(1) DEFAULT 0, fulfillment_notes TEXT, dh_confirmation VARCHAR(100) DEFAULT NULL, dh_order_status VARCHAR(50) DEFAULT NULL, deal_registration TINYINT(1) DEFAULT 0, is_buyback TINYINT(1) DEFAULT 0, zoho_data JSON, signature_required TINYINT(1) DEFAULT 1, shipping_preference VARCHAR(50) DEFAULT 'lowest_cost', shipstation_order_id INT DEFAULT NULL, shipstation_order_status VARCHAR(50) DEFAULT NULL, deal_id INT DEFAULT NULL, source_quote VARCHAR(20) DEFAULT NULL, import_source ENUM('zoho_import','manual','api') DEFAULT 'zoho_import', imported_at TIMESTAMP NULL, myob_exported_at TIMESTAMP NULL, zte_customer_id VARCHAR(255) DEFAULT NULL, zte_company_id VARCHAR(50) DEFAULT NULL, zte_enrollment_token TEXT DEFAULT NULL, zte_ou_path VARCHAR(500) DEFAULT NULL, zte_wifi_details TEXT DEFAULT NULL, zte_requested_at TIMESTAMP NULL, zte_received_at TIMESTAMP NULL, -- Repair order fields (NULL for sales orders) order_type ENUM('sales','repair') NOT NULL DEFAULT 'sales', repair_type ENUM('warranty','paid','evaluation') DEFAULT NULL, diagnosis TEXT DEFAULT NULL, resolution TEXT DEFAULT NULL, condition_in VARCHAR(100) DEFAULT NULL, condition_out VARCHAR(100) DEFAULT NULL, inbound_tracking VARCHAR(255) DEFAULT NULL, inbound_carrier VARCHAR(100) DEFAULT NULL, quoted_amount DECIMAL(12,2) DEFAULT NULL, final_amount DECIMAL(12,2) DEFAULT NULL, assigned_to INT DEFAULT NULL, created_by INT DEFAULT NULL, received_at TIMESTAMP NULL, diagnosed_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, shipped_at TIMESTAMP NULL, delivered_at TIMESTAMP NULL, chrome_form_sent_at TIMESTAMP NULL, payment_email_sent_at TIMESTAMP NULL, customer_app_invite_token VARCHAR(128) DEFAULT NULL, customer_app_email_sent_at TIMESTAMP NULL, notifications_suppressed TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_mts_number (mts_number), FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_order_type (order_type), INDEX idx_order_type_status (order_type, status), INDEX idx_customer_name (customer_name), INDEX idx_order_date (order_date), INDEX idx_zoho_invoice_id (zoho_invoice_id), INDEX idx_zoho_quote_id (zoho_quote_id), INDEX idx_shopify (shopify_order_number), INDEX idx_assigned_to (assigned_to), line_items_locked TINYINT(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order line items (from Zoho Invoice Invoiced_Items subform) CREATE TABLE IF NOT EXISTS order_line_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, line_number INT DEFAULT 0, product_code VARCHAR(100) DEFAULT NULL, product_name VARCHAR(255) DEFAULT NULL, description TEXT, quantity INT DEFAULT 0, unit_price DECIMAL(12,2) DEFAULT NULL, unit_cost DECIMAL(12,2) DEFAULT NULL, discount DECIMAL(12,2) DEFAULT NULL, tax DECIMAL(12,2) DEFAULT NULL, line_total DECIMAL(12,2) DEFAULT NULL, zoho_product_id VARCHAR(50) DEFAULT NULL, zoho_line_item_id VARCHAR(100) DEFAULT NULL, source ENUM('zoho','manual') NOT NULL DEFAULT 'zoho', notes TEXT DEFAULT NULL, sku_status ENUM('', 'awaiting_info', 'awaiting_quote', 'out_of_stock', 'at_hq', 'on_order', 'shipped') DEFAULT '', -- Repair device fields (used when parent order is order_type='repair') device_type VARCHAR(50) DEFAULT NULL, model VARCHAR(100) DEFAULT NULL, serial_number VARCHAR(100) DEFAULT NULL, issue_description TEXT DEFAULT NULL, parts_cost DECIMAL(10,2) DEFAULT NULL, labor_cost DECIMAL(10,2) DEFAULT NULL, diagnosis TEXT DEFAULT NULL, resolution TEXT DEFAULT NULL, asset_number VARCHAR(100) DEFAULT NULL, condition_out VARCHAR(100) DEFAULT NULL, warranty_months INT DEFAULT NULL, original_order_id INT DEFAULT NULL, original_order_number VARCHAR(20) DEFAULT NULL, original_ship_date DATE DEFAULT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id), INDEX idx_product_code (product_code), INDEX idx_oli_serial (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order procurement sources (maps to Zoho Purchase_Orders) CREATE TABLE IF NOT EXISTS order_sources ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, line_item_id INT DEFAULT NULL, source_type ENUM('vendor_po','at_hq') DEFAULT 'vendor_po', vendor VARCHAR(100) DEFAULT NULL, po_number VARCHAR(50) DEFAULT NULL, vendor_order_number VARCHAR(100) DEFAULT NULL, zoho_po_id VARCHAR(50) DEFAULT NULL, status VARCHAR(50) DEFAULT NULL, carrier VARCHAR(100) DEFAULT NULL, tracking_number VARCHAR(255) DEFAULT NULL, additional_tracking TEXT, eta DATE DEFAULT NULL, ship_method ENUM('dropship','ship_to_hq') DEFAULT NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (line_item_id) REFERENCES order_line_items(id) ON DELETE SET NULL, INDEX idx_order (order_id), INDEX idx_po_number (po_number), INDEX idx_zoho_po_id (zoho_po_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Outbound tracking numbers (from Invoice Tracking_Number_1-5) CREATE TABLE IF NOT EXISTS order_tracking_numbers ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, tracking_number VARCHAR(255) NOT NULL, carrier VARCHAR(100) DEFAULT NULL, service VARCHAR(100) DEFAULT NULL, ship_date DATE DEFAULT NULL, weight_oz DECIMAL(10,2) DEFAULT NULL, length_in DECIMAL(6,1) DEFAULT NULL, width_in DECIMAL(6,1) DEFAULT NULL, height_in DECIMAL(6,1) DEFAULT NULL, shipment_cost DECIMAL(10,2) DEFAULT NULL, shipstation_shipment_id INT DEFAULT NULL, source ENUM('zoho_invoice','shipment_data','shipstation','manual') DEFAULT 'zoho_invoice', status ENUM('pre_transit','in_transit','out_for_delivery','delivered','returned','exception','unknown') DEFAULT 'unknown', eta_date DATE DEFAULT NULL, details JSON DEFAULT NULL, delivered_at TIMESTAMP NULL, last_polled_at TIMESTAMP NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id), INDEX idx_tracking (tracking_number), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Serial numbers per order (parsed from Invoice Serial_Number textarea) CREATE TABLE IF NOT EXISTS order_serials ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, line_item_id INT DEFAULT NULL, serial_number VARCHAR(255) NOT NULL, inventory_number VARCHAR(255) DEFAULT NULL, asset_tag VARCHAR(255) DEFAULT NULL, mac_address VARCHAR(50) DEFAULT NULL, source ENUM('zoho_invoice','shipment_data','manual') DEFAULT 'zoho_invoice', FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (line_item_id) REFERENCES order_line_items(id) ON DELETE SET NULL, INDEX idx_order (order_id), INDEX idx_serial (serial_number), INDEX idx_asset_tag (asset_tag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Outbound shipments (for future MKL-managed shipping, not populated from Zoho) CREATE TABLE IF NOT EXISTS order_shipments ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, shipment_id VARCHAR(20) DEFAULT NULL, status ENUM('draft','ready','shipped','delivered') DEFAULT 'draft', ship_method ENUM('package','freight') DEFAULT 'package', carrier VARCHAR(100) DEFAULT NULL, service VARCHAR(100) DEFAULT NULL, tracking_number VARCHAR(255) DEFAULT NULL, weight DECIMAL(8,2) DEFAULT NULL, length DECIMAL(8,2) DEFAULT NULL, width DECIMAL(8,2) DEFAULT NULL, height DECIMAL(8,2) DEFAULT NULL, package_count INT DEFAULT 1, notes TEXT, shipped_at TIMESTAMP NULL, delivered_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id), INDEX idx_tracking (tracking_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Items in each outbound shipment CREATE TABLE IF NOT EXISTS order_shipment_items ( id INT AUTO_INCREMENT PRIMARY KEY, shipment_id INT NOT NULL, line_item_id INT NOT NULL, quantity INT DEFAULT 0, FOREIGN KEY (shipment_id) REFERENCES order_shipments(id) ON DELETE CASCADE, FOREIGN KEY (line_item_id) REFERENCES order_line_items(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Boxes/parcels within a shipment (multi-box support via Shippo) CREATE TABLE IF NOT EXISTS order_shipment_boxes ( id INT AUTO_INCREMENT PRIMARY KEY, shipment_id INT NOT NULL, box_number INT NOT NULL DEFAULT 1, box_name VARCHAR(100) DEFAULT NULL, weight DECIMAL(8,2) DEFAULT NULL, length DECIMAL(8,2) DEFAULT NULL, width DECIMAL(8,2) DEFAULT NULL, height DECIMAL(8,2) DEFAULT NULL, tracking_number VARCHAR(255) DEFAULT NULL, shippo_parcel_id VARCHAR(100) DEFAULT NULL, shippo_transaction_id VARCHAR(100) DEFAULT NULL, shipstation_shipment_id BIGINT DEFAULT NULL, label_url TEXT DEFAULT NULL, label_data LONGBLOB DEFAULT NULL, label_format VARCHAR(10) DEFAULT 'pdf', ship_cost DECIMAL(10,2) DEFAULT NULL, status ENUM('draft','label_purchased','shipped','refunded') DEFAULT 'draft', refund_status VARCHAR(20) DEFAULT NULL, refund_id VARCHAR(100) DEFAULT NULL, voided_by INT DEFAULT NULL, voided_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (shipment_id) REFERENCES order_shipments(id) ON DELETE CASCADE, INDEX idx_shipment (shipment_id), INDEX idx_tracking (tracking_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Serial-to-box assignments (links order_serials to specific boxes) CREATE TABLE IF NOT EXISTS order_serial_box_assignments ( id INT AUTO_INCREMENT PRIMARY KEY, serial_id INT NOT NULL, box_id INT NOT NULL, FOREIGN KEY (serial_id) REFERENCES order_serials(id) ON DELETE CASCADE, FOREIGN KEY (box_id) REFERENCES order_shipment_boxes(id) ON DELETE CASCADE, UNIQUE KEY uq_serial_box (serial_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Serial numbers scanned into specific boxes during shipment wizard CREATE TABLE IF NOT EXISTS order_shipment_box_serials ( id INT AUTO_INCREMENT PRIMARY KEY, box_id INT NOT NULL, shipment_item_id INT NOT NULL, serial_number VARCHAR(255) NOT NULL, scanned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (box_id) REFERENCES order_shipment_boxes(id) ON DELETE CASCADE, FOREIGN KEY (shipment_item_id) REFERENCES order_shipment_items(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Shipment wizard: ship-to override, signature, preference, rate selection ALTER TABLE order_shipments ADD COLUMN IF NOT EXISTS ship_to_name VARCHAR(255) DEFAULT NULL, ADD COLUMN IF NOT EXISTS ship_to_street VARCHAR(500) DEFAULT NULL, ADD COLUMN IF NOT EXISTS ship_to_street2 VARCHAR(500) DEFAULT NULL, ADD COLUMN IF NOT EXISTS ship_to_city VARCHAR(100) DEFAULT NULL, ADD COLUMN IF NOT EXISTS ship_to_state VARCHAR(10) DEFAULT NULL, ADD COLUMN IF NOT EXISTS ship_to_zip VARCHAR(20) DEFAULT NULL, ADD COLUMN IF NOT EXISTS signature_required TINYINT(1) DEFAULT NULL, ADD COLUMN IF NOT EXISTS shipping_preference VARCHAR(50) DEFAULT NULL, ADD COLUMN IF NOT EXISTS selected_rate_json JSON DEFAULT NULL; -- External tracking: label purchased on another platform ALTER TABLE order_shipments MODIFY COLUMN ship_method ENUM('package','freight','external') DEFAULT 'package', ADD COLUMN IF NOT EXISTS external_tracking VARCHAR(255) DEFAULT NULL; -- Shipment items: box assignment ALTER TABLE order_shipment_items ADD COLUMN IF NOT EXISTS box_id INT DEFAULT NULL; -- Shipment boxes: box name (standard size name from ShipStation) ALTER TABLE order_shipment_boxes ADD COLUMN IF NOT EXISTS box_name VARCHAR(100) DEFAULT NULL AFTER box_number; -- Shipment boxes: ShipStation/Shippo packageCode (e.g. 'package', 'small_flat_rate_box') -- Captured at label-purchase time so future shipments of the same SKU mix can pre-fill it. ALTER TABLE order_shipment_boxes ADD COLUMN IF NOT EXISTS package_code VARCHAR(50) DEFAULT NULL AFTER box_name; -- Ad-hoc shipments (standalone, no underlying order) ALTER TABLE order_shipments MODIFY COLUMN order_id INT NULL, ADD COLUMN IF NOT EXISTS shipment_type ENUM('order','adhoc') NOT NULL DEFAULT 'order' AFTER order_id, ADD COLUMN IF NOT EXISTS adhoc_reference VARCHAR(100) DEFAULT NULL; -- Order triage check results (MKL-only, not from Zoho) CREATE TABLE IF NOT EXISTS order_triage ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, customer_standing ENUM('pass','fail','na') DEFAULT 'na', payment_verified ENUM('pass','fail','na') DEFAULT 'na', ap_info ENUM('pass','fail','na') DEFAULT 'na', po_received ENUM('pass','fail','na') DEFAULT 'na', license_compliance ENUM('pass','fail','na') DEFAULT 'na', UNIQUE KEY uq_order (order_id), FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order internal notes CREATE TABLE IF NOT EXISTS order_notes ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, user_id INT DEFAULT NULL, note TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_order (order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order activity / audit log CREATE TABLE IF NOT EXISTS order_activity ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, user_id INT DEFAULT NULL, action VARCHAR(100) NOT NULL, details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_order (order_id), INDEX idx_action (action), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order tags (many-to-many, reuses pattern from customer_tags) CREATE TABLE IF NOT EXISTS order_tags ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, tag VARCHAR(50) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, UNIQUE KEY uq_order_tag (order_id, tag), INDEX idx_tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS form_files ( id INT AUTO_INCREMENT PRIMARY KEY, application_id INT NOT NULL, form_type VARCHAR(50) NOT NULL DEFAULT 'customer_application', file_type ENUM('w9','tax_exempt_cert','audited_financial','generated_pdf') NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, mime_type VARCHAR(100), file_size INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (application_id) REFERENCES form_submissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Installation Pricing Worksheet submissions CREATE TABLE IF NOT EXISTS installation_pricing_submissions ( id INT AUTO_INCREMENT PRIMARY KEY, submission_number VARCHAR(20) UNIQUE NOT NULL, status ENUM('submitted','sent','completed') DEFAULT 'submitted', -- Requester info requester_name VARCHAR(255) NOT NULL, requester_email VARCHAR(255) NOT NULL, requester_phone VARCHAR(50) NOT NULL, -- Site info site_name VARCHAR(500) NOT NULL, site_address VARCHAR(500) NOT NULL, site_state VARCHAR(10) NOT NULL, school_name VARCHAR(500) NOT NULL, -- Project environment number_of_rooms INT NOT NULL DEFAULT 1, general_notes TEXT, wall_construction VARCHAR(100) NOT NULL, ceiling_height VARCHAR(100) NOT NULL, ceiling_construction VARCHAR(100) NOT NULL, ladder_or_lift VARCHAR(100) NOT NULL, shipping_dock VARCHAR(100) NOT NULL, trash_location VARCHAR(100) NOT NULL, hardware_disposal VARCHAR(100) NOT NULL, -- Display mount installation mount_installations INT NOT NULL DEFAULT 0, -- Large format display installation displays_under_75 INT NOT NULL DEFAULT 0, displays_76_85 INT NOT NULL DEFAULT 0, displays_86_95 INT NOT NULL DEFAULT 0, displays_96_120 INT NOT NULL DEFAULT 0, -- Collaboration room components sound_bars INT NOT NULL DEFAULT 0, table_top_devices INT NOT NULL DEFAULT 0, wall_mounted_devices INT NOT NULL DEFAULT 0, ceiling_mounted_devices INT NOT NULL DEFAULT 0, -- Collaboration software & design collaboration_apps INT NOT NULL DEFAULT 0, collab_rooms_design INT NOT NULL DEFAULT 0, collab_rooms_survey INT NOT NULL DEFAULT 0, -- Cabling cable_runs_no_conduit INT NOT NULL DEFAULT 0, cable_runs_flex_conduit INT NOT NULL DEFAULT 0, -- Territory routing territory VARCHAR(50), assigned_salesperson VARCHAR(255), assigned_salesperson_email VARCHAR(255), -- Submitter submitted_by_name VARCHAR(255), submitted_by_email VARCHAR(255), submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS installation_pricing_files ( id INT AUTO_INCREMENT PRIMARY KEY, submission_id INT NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, mime_type VARCHAR(100), file_size INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (submission_id) REFERENCES installation_pricing_submissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════ -- Email Marketing Module -- ══════════════════════════════════════════════════════════════ -- Reusable email templates CREATE TABLE IF NOT EXISTS email_templates ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, subject VARCHAR(255), html_body LONGTEXT, text_body TEXT, category ENUM('general','wholesale','announcement','newsletter') DEFAULT 'general', created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Campaign header CREATE TABLE IF NOT EXISTS email_campaigns ( id INT AUTO_INCREMENT PRIMARY KEY, campaign_number VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(200) NOT NULL, subject VARCHAR(255) NOT NULL, from_name VARCHAR(100) DEFAULT 'Tech to School', from_email VARCHAR(255) DEFAULT 'info@techtoschool.com', reply_to VARCHAR(255), html_body LONGTEXT, text_body TEXT, template_id INT, status ENUM('draft','scheduled','sending','paused','sent','cancelled','failed') DEFAULT 'draft', scheduled_at TIMESTAMP NULL, started_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, -- Targeting target_type ENUM('all_active','list','tag','segment') DEFAULT 'all_active', target_list_id INT, target_tag_id INT, target_segment JSON, -- Stats (denormalized for fast display) total_recipients INT DEFAULT 0, total_sent INT DEFAULT 0, total_delivered INT DEFAULT 0, total_opened INT DEFAULT 0, total_clicked INT DEFAULT 0, total_bounced INT DEFAULT 0, total_complained INT DEFAULT 0, total_unsubscribed INT DEFAULT 0, -- Audit created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (template_id) REFERENCES email_templates(id), FOREIGN KEY (target_list_id) REFERENCES customer_lists(id), FOREIGN KEY (target_tag_id) REFERENCES customer_tags(id), FOREIGN KEY (created_by) REFERENCES users(id), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Individual send records CREATE TABLE IF NOT EXISTS email_sends ( id BIGINT AUTO_INCREMENT PRIMARY KEY, campaign_id INT NOT NULL, customer_id INT NOT NULL, email VARCHAR(255) NOT NULL, ses_message_id VARCHAR(255), status ENUM('queued','sent','delivered','bounced','complained','failed') DEFAULT 'queued', sent_at TIMESTAMP NULL, delivered_at TIMESTAMP NULL, opened_at TIMESTAMP NULL, clicked_at TIMESTAMP NULL, open_count INT DEFAULT 0, click_count INT DEFAULT 0, error_message TEXT, FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id), FOREIGN KEY (customer_id) REFERENCES customers(id), INDEX idx_campaign (campaign_id), INDEX idx_customer (customer_id), INDEX idx_ses_message (ses_message_id), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- SNS event log CREATE TABLE IF NOT EXISTS email_send_events ( id BIGINT AUTO_INCREMENT PRIMARY KEY, send_id BIGINT, event_type ENUM('send','delivery','bounce','complaint','open','click','unsubscribe') NOT NULL, event_data JSON, event_timestamp TIMESTAMP NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (send_id) REFERENCES email_sends(id), INDEX idx_send (send_id), INDEX idx_type_time (event_type, event_timestamp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Permanent suppression list (hard bounces + complaints) CREATE TABLE IF NOT EXISTS email_suppression_list ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, reason ENUM('hard_bounce','complaint','manual','import') NOT NULL, source_campaign_id INT, added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One-click unsubscribe tokens CREATE TABLE IF NOT EXISTS email_unsubscribe_tokens ( id INT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(64) NOT NULL UNIQUE, customer_id INT NOT NULL, campaign_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, used_at TIMESTAMP NULL, FOREIGN KEY (customer_id) REFERENCES customers(id), INDEX idx_token (token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Amazon Business Reconciliation Transactions ───────────── CREATE TABLE IF NOT EXISTS amazon_transactions ( id INT AUTO_INCREMENT PRIMARY KEY, amazon_order_id VARCHAR(50) NOT NULL, transaction_id VARCHAR(100), charge_identifier VARCHAR(50), transaction_type VARCHAR(20), po_number VARCHAR(100), sp_number VARCHAR(50), mts_number VARCHAR(50), asin VARCHAR(20), product_title VARCHAR(500), quantity INT DEFAULT 1, unit_price DECIMAL(12,2), total_amount DECIMAL(12,2), tax_amount DECIMAL(12,2), shipping_amount DECIMAL(12,2), buyer_email VARCHAR(255), buyer_name VARCHAR(255), payer_email VARCHAR(255), merchant_name VARCHAR(255), shipment_id VARCHAR(100), shipment_date DATETIME, transaction_date DATETIME, feed_date DATETIME, payment_method VARCHAR(100), payment_last4 VARCHAR(4), raw_data JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_txn_line (transaction_id, amazon_order_id, asin), INDEX idx_po (po_number), INDEX idx_sp (sp_number), INDEX idx_mts (mts_number), INDEX idx_order (amazon_order_id), INDEX idx_txn_date (transaction_date), INDEX idx_asin (asin) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════ -- Repairs Module — Tickets + Repair Orders (RMA) -- ═══════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS repair_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 100001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Seed: INSERT IGNORE INTO repair_sequences (prefix, next_number) VALUES ('RMA', 100001), ('TKT', 100001); CREATE TABLE IF NOT EXISTS support_tickets ( id INT AUTO_INCREMENT PRIMARY KEY, ticket_number VARCHAR(20) NOT NULL, status ENUM('open','pending','closed','converted') DEFAULT 'open', customer_org_id INT DEFAULT NULL, contact_name VARCHAR(255), contact_email VARCHAR(255), contact_phone VARCHAR(50), subject VARCHAR(500) NOT NULL, description TEXT, category ENUM('hardware_repair','software_issue','warranty_claim','general_inquiry','other') DEFAULT 'general_inquiry', priority ENUM('low','normal','high','urgent') DEFAULT 'normal', assigned_to INT DEFAULT NULL, created_by INT DEFAULT NULL, resolved_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ticket_number (ticket_number), FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_customer_org (customer_org_id), INDEX idx_assigned (assigned_to), INDEX idx_priority (priority), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ticket_comments ( id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT NOT NULL, user_id INT DEFAULT NULL, content TEXT NOT NULL, is_internal TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ticket (ticket_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repairs ( id INT AUTO_INCREMENT PRIMARY KEY, rma_number VARCHAR(20) NOT NULL, status ENUM('new','inbound','in_process','pending','ready_to_ship','shipped','delivered','complete','cancelled') DEFAULT 'new', customer_org_id INT DEFAULT NULL, contact_name VARCHAR(255), contact_email VARCHAR(255), contact_phone VARCHAR(50), repair_type ENUM('warranty','paid','evaluation') DEFAULT 'evaluation', ship_to_street VARCHAR(500), ship_to_city VARCHAR(255), ship_to_state VARCHAR(100), ship_to_zip VARCHAR(20), ship_to_country VARCHAR(100) DEFAULT 'US', inbound_tracking VARCHAR(255), inbound_carrier VARCHAR(100), outbound_tracking VARCHAR(255), outbound_carrier VARCHAR(100), diagnosis TEXT, resolution TEXT, quoted_amount DECIMAL(12,2) DEFAULT NULL, final_amount DECIMAL(12,2) DEFAULT NULL, zoho_invoice_id VARCHAR(50) DEFAULT NULL, assigned_to INT DEFAULT NULL, created_by INT DEFAULT NULL, received_at TIMESTAMP NULL, diagnosed_at TIMESTAMP NULL, approved_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, shipped_at TIMESTAMP NULL, delivered_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_rma_number (rma_number), FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_customer_org (customer_org_id), INDEX idx_repair_type (repair_type), INDEX idx_assigned (assigned_to), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_tickets ( repair_id INT NOT NULL, ticket_id INT NOT NULL, linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (repair_id, ticket_id), FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_items ( id INT AUTO_INCREMENT PRIMARY KEY, repair_id INT NOT NULL, device_type VARCHAR(100), model VARCHAR(255), serial_number VARCHAR(100), condition_in VARCHAR(100), condition_out VARCHAR(100), issue_description TEXT, repair_description TEXT, parts_cost DECIMAL(10,2) DEFAULT 0, labor_cost DECIMAL(10,2) DEFAULT 0, line_total DECIMAL(12,2) DEFAULT 0, sort_order INT DEFAULT 0, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, INDEX idx_repair (repair_id), INDEX idx_serial (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_files ( id INT AUTO_INCREMENT PRIMARY KEY, repair_id INT NOT NULL, file_type ENUM('intake_photo','diagnosis_photo','repair_photo','shipping_label','invoice','other') NOT NULL DEFAULT 'other', filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, mime_type VARCHAR(100), file_size INT DEFAULT 0, uploaded_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_repair (repair_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_notes ( id INT AUTO_INCREMENT PRIMARY KEY, repair_id INT NOT NULL, user_id INT DEFAULT NULL, note TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_repair (repair_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_activity ( id INT AUTO_INCREMENT PRIMARY KEY, repair_id INT NOT NULL, user_id INT DEFAULT NULL, action VARCHAR(100) NOT NULL, details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_repair (repair_id), INDEX idx_action (action), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS repair_tags ( id INT AUTO_INCREMENT PRIMARY KEY, repair_id INT NOT NULL, tag VARCHAR(50) NOT NULL, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE CASCADE, UNIQUE KEY uq_repair_tag (repair_id, tag), INDEX idx_tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════ -- Support Center — Chat/Messaging (Phase 2) -- ═══════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS support_conversations ( id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT DEFAULT NULL, repair_id INT DEFAULT NULL, customer_email VARCHAR(255) NOT NULL, customer_name VARCHAR(255), gmail_thread_id VARCHAR(255) DEFAULT NULL, subject VARCHAR(500), status ENUM('active','resolved','archived') DEFAULT 'active', last_message_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE SET NULL, FOREIGN KEY (repair_id) REFERENCES repairs(id) ON DELETE SET NULL, INDEX idx_ticket (ticket_id), INDEX idx_repair (repair_id), INDEX idx_gmail_thread (gmail_thread_id), INDEX idx_customer_email (customer_email), INDEX idx_status (status), INDEX idx_last_message (last_message_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS support_chat_messages ( id INT AUTO_INCREMENT PRIMARY KEY, conversation_id INT NOT NULL, direction ENUM('outbound','inbound') NOT NULL, sender_email VARCHAR(255), sender_name VARCHAR(255), content TEXT NOT NULL, content_html TEXT, gmail_message_id VARCHAR(255) DEFAULT NULL, is_internal TINYINT(1) DEFAULT 0, read_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE, INDEX idx_conversation (conversation_id), INDEX idx_gmail_message (gmail_message_id), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════ -- NCES School Database — National school/district directory -- ═══════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS nces_schools ( nces_id VARCHAR(12) PRIMARY KEY, school_name VARCHAR(200) NOT NULL, lea_name VARCHAR(200), lea_id VARCHAR(7), street VARCHAR(200), city VARCHAR(100), state CHAR(2), zip VARCHAR(10), phone VARCHAR(20), county VARCHAR(100), school_level VARCHAR(20), school_type VARCHAR(30), is_charter TINYINT(1) DEFAULT 0, is_private TINYINT(1) DEFAULT 0, grade_low VARCHAR(4), grade_high VARCHAR(4), enrollment INT, locale VARCHAR(50), latitude DECIMAL(10,7), longitude DECIMAL(10,7), operational_status VARCHAR(20) DEFAULT 'Open', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FULLTEXT INDEX ft_school_search (school_name, lea_name, city), INDEX idx_state (state), INDEX idx_state_city (state, city) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS nces_districts ( lea_id VARCHAR(7) PRIMARY KEY, lea_name VARCHAR(200) NOT NULL, state CHAR(2), city VARCHAR(100), zip VARCHAR(10), enrollment INT, school_count INT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FULLTEXT INDEX ft_district_search (lea_name, city), INDEX idx_state (state) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════ -- Quote Requests — Build Your Quote form submissions -- ═══════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS quote_requests ( id INT AUTO_INCREMENT PRIMARY KEY, quote_number VARCHAR(20) NOT NULL, status ENUM('new','contacted','quoted','won','lost','expired') DEFAULT 'new', -- School identification nces_school_id VARCHAR(12) DEFAULT NULL, school_name VARCHAR(200), district_name VARCHAR(200), school_city VARCHAR(100), school_state CHAR(2), school_zip VARCHAR(10), school_type VARCHAR(30), enrollment INT, grade_low VARCHAR(4), grade_high VARCHAR(4), is_existing_customer TINYINT(1) DEFAULT 0, org_id INT DEFAULT NULL, -- Product selection device_categories TEXT, quantity_range VARCHAR(20), grade_levels TEXT, additional_services TEXT, -- Timeline timeline VARCHAR(30), budget_range VARCHAR(20), additional_notes TEXT, -- Contact contact_first_name VARCHAR(100), contact_last_name VARCHAR(100), contact_email VARCHAR(255), contact_phone VARCHAR(30), contact_role VARCHAR(50), referral_source VARCHAR(50), -- Territory routing territory VARCHAR(50), assigned_salesperson VARCHAR(100), assigned_salesperson_email VARCHAR(255), -- Form context form_mode VARCHAR(20) DEFAULT 'product', product_context TEXT, solution_answers TEXT, -- Integration zoho_account_id VARCHAR(50), zoho_deal_id VARCHAR(50), -- Audit submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, submitted_ip VARCHAR(45), user_agent TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_quote_number (quote_number), INDEX idx_status (status), INDEX idx_school_state (school_state), INDEX idx_salesperson (assigned_salesperson_email), INDEX idx_submitted (submitted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================= -- Support Analysis — thread-level metadata + AI analysis -- Email bodies stay in Gmail; only summaries stored here -- ============================================= CREATE TABLE IF NOT EXISTS support_threads ( id INT AUTO_INCREMENT PRIMARY KEY, gmail_thread_id VARCHAR(100) NOT NULL, source_email VARCHAR(255) NOT NULL, subject VARCHAR(500), customer_email VARCHAR(255), customer_name VARCHAR(255), first_message_at TIMESTAMP NULL, last_message_at TIMESTAMP NULL, message_count INT DEFAULT 0, direction_summary ENUM('conversation','inbound_only','outbound_only') DEFAULT 'inbound_only', analyzed_at TIMESTAMP NULL, analysis_version INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_thread_source (gmail_thread_id, source_email), INDEX idx_source_email (source_email), INDEX idx_customer_email (customer_email), INDEX idx_last_message (last_message_at), INDEX idx_analyzed (analyzed_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS support_analysis ( id INT AUTO_INCREMENT PRIMARY KEY, thread_id INT NOT NULL, analysis_version INT DEFAULT 1, category VARCHAR(50), subcategory VARCHAR(100), customer_intent TEXT, response_summary TEXT, response_pattern JSON, resolution VARCHAR(30) DEFAULT 'unknown', response_time_minutes INT, data_sources_used JSON, sentiment VARCHAR(20), could_automate TINYINT(1) DEFAULT 0, automation_notes TEXT, analysis_raw JSON, analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_thread_version (thread_id, analysis_version), INDEX idx_category (category), INDEX idx_resolution (resolution), INDEX idx_sentiment (sentiment), INDEX idx_automatable (could_automate), FOREIGN KEY (thread_id) REFERENCES support_threads(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================= -- Wufoo Service Requests — form submissions from tech-to-school-service-request -- ============================================= CREATE TABLE IF NOT EXISTS wufoo_service_requests ( id INT AUTO_INCREMENT PRIMARY KEY, entry_id INT NOT NULL, created_at DATETIME NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(255), phone VARCHAR(50), school_name VARCHAR(255), invoice_numbers VARCHAR(500), device_info TEXT, problem_description TEXT, spreadsheet_url VARCHAR(500), ship_to_school VARCHAR(255), ship_to_attention VARCHAR(255), ship_to_address VARCHAR(500), ship_to_city VARCHAR(100), ship_to_state VARCHAR(50), ship_to_zip VARCHAR(20), has_shipping_box VARCHAR(50), device_engraved VARCHAR(50), repair_or_replace VARCHAR(50), raw_json JSON, imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_entry_id (entry_id), INDEX idx_email (email), INDEX idx_created (created_at), INDEX idx_school (school_name), INDEX idx_invoice (invoice_numbers) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Dialpad Call Analytics ───────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS dialpad_calls ( id INT AUTO_INCREMENT PRIMARY KEY, call_id VARCHAR(100) UNIQUE NOT NULL, date_started DATETIME NULL, date_ended DATETIME NULL, duration_seconds INT DEFAULT 0, direction ENUM('inbound', 'outbound', 'internal') DEFAULT 'inbound', contact_name VARCHAR(255), contact_number VARCHAR(50), target_name VARCHAR(255), target_number VARCHAR(50), mos_score DECIMAL(3,1) DEFAULT NULL, was_recorded TINYINT(1) DEFAULT 0, recording_url VARCHAR(500) DEFAULT NULL, recap_summary TEXT, transcript TEXT, contact_id VARCHAR(100) DEFAULT NULL, contact_email VARCHAR(255) DEFAULT NULL, contact_type VARCHAR(50) DEFAULT NULL, enriched_at DATETIME DEFAULT NULL, labels TEXT, positive_sentiment INT DEFAULT 0, negative_sentiment INT DEFAULT 0, action_item_count INT DEFAULT 0, monologue_count INT DEFAULT 0, question_count INT DEFAULT 0, call_purpose VARCHAR(255) DEFAULT NULL, moments_json TEXT DEFAULT NULL, ai_sentiment DECIMAL(3,2) DEFAULT NULL, ai_summary TEXT DEFAULT NULL, ai_action_items TEXT DEFAULT NULL, ai_deal_signals TEXT DEFAULT NULL, ai_coaching TEXT DEFAULT NULL, ai_analyzed_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_date_started (date_started), INDEX idx_direction (direction), INDEX idx_target (target_name), INDEX idx_contact_number (contact_number), INDEX idx_enriched_at (enriched_at), INDEX idx_ai_analyzed (ai_analyzed_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Seamless.AI Lead Imports ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS seamless_leads ( id INT AUTO_INCREMENT PRIMARY KEY, research_date DATETIME NULL, first_name VARCHAR(100), last_name VARCHAR(100), full_name VARCHAR(200), title VARCHAR(255), department VARCHAR(100), seniority VARCHAR(100), company_name VARCHAR(255), company_domain VARCHAR(255), list_name VARCHAR(255), linkedin_url VARCHAR(500), email_1 VARCHAR(255), email_1_validation VARCHAR(20), email_2 VARCHAR(255), personal_email VARCHAR(255), phone VARCHAR(50), company_phone VARCHAR(50), mobile_phone VARCHAR(50), contact_city VARCHAR(100), contact_state VARCHAR(100), contact_state_abbr VARCHAR(10), contact_country VARCHAR(100), company_location VARCHAR(500), company_city VARCHAR(100), company_state VARCHAR(100), company_state_abbr VARCHAR(10), company_zip VARCHAR(20), company_country VARCHAR(100), company_revenue BIGINT NULL, company_revenue_range VARCHAR(100), company_description TEXT, company_website_domain VARCHAR(255), company_founded VARCHAR(20), company_industry VARCHAR(255), company_linkedin_url VARCHAR(500), company_staff_count INT NULL, company_staff_count_range VARCHAR(100), sic_code VARCHAR(20), naics_code VARCHAR(20), source_file VARCHAR(255), in_zoho TINYINT(1) DEFAULT NULL, in_activecampaign TINYINT(1) DEFAULT NULL, has_recent_email TINYINT(1) DEFAULT NULL, do_not_contact TINYINT(1) DEFAULT 0, suppression_checked_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_email (email_1), INDEX idx_company (company_name), INDEX idx_state (contact_state_abbr), INDEX idx_industry (company_industry), INDEX idx_list (list_name), INDEX idx_seniority (seniority), FULLTEXT idx_search (full_name, company_name, title, company_industry) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- Zoho CRM Data Warehouse — Full mirror of 11 Zoho modules -- Core typed columns for queries + zoho_data JSON blob for all fields -- ═══════════════════════════════════════════════════════════════════════════════ -- ─── Sync infrastructure ──────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_sync_state ( id INT AUTO_INCREMENT PRIMARY KEY, module VARCHAR(50) UNIQUE NOT NULL, last_sync_at DATETIME NULL, last_page_token VARCHAR(500) NULL, last_deleted_sync_at DATETIME NULL, status ENUM('idle','running','failed') DEFAULT 'idle', records_synced INT DEFAULT 0, records_deleted INT DEFAULT 0, error_message TEXT, details JSON, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS zoho_module_fields ( id INT AUTO_INCREMENT PRIMARY KEY, module VARCHAR(50) NOT NULL, api_name VARCHAR(255) NOT NULL, display_label VARCHAR(255), data_type VARCHAR(100), field_length INT, is_custom TINYINT(1) DEFAULT 0, first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_module_field (module, api_name), INDEX idx_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS zoho_attachments ( id INT AUTO_INCREMENT PRIMARY KEY, parent_module VARCHAR(50) NOT NULL, parent_zoho_id VARCHAR(50) NOT NULL, attachment_zoho_id VARCHAR(50) UNIQUE NOT NULL, file_name VARCHAR(500), size_bytes BIGINT, content_type VARCHAR(255), local_path VARCHAR(1000), downloaded_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_parent (parent_module, parent_zoho_id), INDEX idx_downloaded (downloaded_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_leads ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_leads ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), full_name VARCHAR(200), email VARCHAR(255), phone VARCHAR(50), mobile VARCHAR(50), company VARCHAR(255), title VARCHAR(255), lead_source VARCHAR(255), lead_status VARCHAR(100), industry VARCHAR(255), annual_revenue DECIMAL(14,2), no_of_employees INT, website VARCHAR(500), street VARCHAR(500), city VARCHAR(255), state VARCHAR(100), zip VARCHAR(20), country VARCHAR(100), rating VARCHAR(50), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_leads_email (email), INDEX idx_leads_company (company), INDEX idx_leads_status (lead_status), INDEX idx_leads_modified (modified_time), INDEX idx_leads_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_contacts ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_contacts ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), full_name VARCHAR(200), email VARCHAR(255), phone VARCHAR(50), mobile VARCHAR(50), title VARCHAR(255), department VARCHAR(255), account_id VARCHAR(50), account_name VARCHAR(500), vendor_id VARCHAR(50), lead_source VARCHAR(255), mailing_street VARCHAR(500), mailing_city VARCHAR(255), mailing_state VARCHAR(100), mailing_zip VARCHAR(20), mailing_country VARCHAR(100), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_contacts_email (email), INDEX idx_contacts_account (account_id), INDEX idx_contacts_modified (modified_time), INDEX idx_contacts_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_accounts ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_accounts ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, account_name VARCHAR(500) NOT NULL, phone VARCHAR(50), website VARCHAR(500), account_type VARCHAR(100), industry VARCHAR(255), employees INT, annual_revenue DECIMAL(14,2), rating VARCHAR(50), account_number VARCHAR(100), sic_code VARCHAR(50), ownership VARCHAR(100), billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_code VARCHAR(20), billing_country VARCHAR(100), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_code VARCHAR(20), shipping_country VARCHAR(100), parent_account_id VARCHAR(50), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_accounts_name (account_name(191)), INDEX idx_accounts_type (account_type), INDEX idx_accounts_modified (modified_time), INDEX idx_accounts_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_potentials (Zoho "Deals" module) ────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_potentials ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, deal_name VARCHAR(500), stage VARCHAR(100), probability INT, amount DECIMAL(14,2), closing_date DATE, account_id VARCHAR(50), account_name VARCHAR(500), contact_id VARCHAR(50), contact_name VARCHAR(255), type VARCHAR(100), lead_source VARCHAR(255), next_step VARCHAR(500), campaign_source_id VARCHAR(50), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_potentials_stage (stage), INDEX idx_potentials_account (account_id), INDEX idx_potentials_closing (closing_date), INDEX idx_potentials_modified (modified_time), INDEX idx_potentials_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_quotes ──────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_quotes ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, subject VARCHAR(500), quote_number VARCHAR(100), quote_stage VARCHAR(100), mts_quote_number VARCHAR(100), valid_till DATE, account_id VARCHAR(50), account_name VARCHAR(500), contact_id VARCHAR(50), contact_name VARCHAR(255), potential_id VARCHAR(50), sub_total DECIMAL(14,2), grand_total DECIMAL(14,2), discount DECIMAL(14,2), tax DECIMAL(14,2), adjustment DECIMAL(14,2), billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_code VARCHAR(20), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_code VARCHAR(20), carrier VARCHAR(100), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_quotes_number (quote_number), INDEX idx_quotes_mts (mts_quote_number), INDEX idx_quotes_account (account_id), INDEX idx_quotes_stage (quote_stage), INDEX idx_quotes_modified (modified_time), INDEX idx_quotes_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_invoices ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_invoices ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, subject VARCHAR(500), invoice_number VARCHAR(100), mts_quote_number VARCHAR(100), status VARCHAR(100), invoice_date DATE, due_date DATE, account_id VARCHAR(50), account_name VARCHAR(500), contact_id VARCHAR(50), contact_name VARCHAR(255), sales_order_id VARCHAR(50), sub_total DECIMAL(14,2), grand_total DECIMAL(14,2), discount DECIMAL(14,2), tax DECIMAL(14,2), adjustment DECIMAL(14,2), billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_code VARCHAR(20), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_code VARCHAR(20), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_invoices_number (invoice_number), INDEX idx_invoices_mts (mts_quote_number), INDEX idx_invoices_account (account_id), INDEX idx_invoices_status (status), INDEX idx_invoices_date (invoice_date), INDEX idx_invoices_modified (modified_time), INDEX idx_invoices_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_products ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_products ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, product_name VARCHAR(500), product_code VARCHAR(255), manufacturer VARCHAR(255), product_category VARCHAR(255), unit_price DECIMAL(14,2), commission_rate DECIMAL(5,2), product_active TINYINT(1) DEFAULT 1, qty_in_stock DECIMAL(10,2), qty_ordered DECIMAL(10,2), vendor_id VARCHAR(50), vendor_name VARCHAR(255), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_products_code (product_code), INDEX idx_products_category (product_category), INDEX idx_products_modified (modified_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_purchase_orders ─────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_purchase_orders ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, subject VARCHAR(500), po_number VARCHAR(100), status VARCHAR(100), vendor_id VARCHAR(50), vendor_name VARCHAR(500), contact_id VARCHAR(50), po_date DATE, due_date DATE, carrier VARCHAR(100), tracking_number VARCHAR(255), sub_total DECIMAL(14,2), grand_total DECIMAL(14,2), discount DECIMAL(14,2), tax DECIMAL(14,2), adjustment DECIMAL(14,2), billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_code VARCHAR(20), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_code VARCHAR(20), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_po_number (po_number), INDEX idx_po_vendor (vendor_id), INDEX idx_po_status (status), INDEX idx_po_modified (modified_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_tasks ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_tasks ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, subject VARCHAR(500), status VARCHAR(100), priority VARCHAR(50), due_date DATETIME NULL, closed_time DATETIME NULL, contact_id VARCHAR(50), contact_name VARCHAR(255), related_to_id VARCHAR(50), related_to_module VARCHAR(50), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_tasks_status (status), INDEX idx_tasks_due (due_date), INDEX idx_tasks_related (related_to_id), INDEX idx_tasks_modified (modified_time), INDEX idx_tasks_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_calls ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_calls ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, subject VARCHAR(500), call_type VARCHAR(50), call_purpose VARCHAR(100), call_start_time DATETIME NULL, call_duration VARCHAR(50), call_duration_seconds INT, call_result VARCHAR(100), contact_id VARCHAR(50), contact_name VARCHAR(255), related_to_id VARCHAR(50), related_to_module VARCHAR(50), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_calls_type (call_type), INDEX idx_calls_start (call_start_time), INDEX idx_calls_related (related_to_id), INDEX idx_calls_modified (modified_time), INDEX idx_calls_owner (owner_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── zoho_vendors ─────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS zoho_vendors ( id INT AUTO_INCREMENT PRIMARY KEY, zoho_id VARCHAR(50) UNIQUE NOT NULL, vendor_name VARCHAR(500) NOT NULL, email VARCHAR(255), phone VARCHAR(50), website VARCHAR(500), category VARCHAR(255), gl_account VARCHAR(255), street VARCHAR(500), city VARCHAR(255), state VARCHAR(100), zip VARCHAR(20), country VARCHAR(100), owner_id VARCHAR(50), owner_name VARCHAR(255), created_time DATETIME NULL, modified_time DATETIME NULL, zoho_data JSON, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_zoho_vendors_name (vendor_name(191)), INDEX idx_zoho_vendors_modified (modified_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- Warehouse Module — Purchase Orders -- ═══════════════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS purchase_order_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 78960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO purchase_order_sequences (prefix, next_number) VALUES ('SP', 78960); CREATE TABLE IF NOT EXISTS purchase_orders ( id INT AUTO_INCREMENT PRIMARY KEY, sp_number VARCHAR(20) UNIQUE NOT NULL, po_type ENUM('regular','rma') NOT NULL DEFAULT 'regular', parent_po_id INT NULL, mts_number VARCHAR(50), subject VARCHAR(500), vendor_id INT, vendor_name VARCHAR(500), vendor_account VARCHAR(50) DEFAULT NULL COMMENT 'Vendor account number used for this PO (e.g., D&H DLL vs standard)', zoho_vendor_id VARCHAR(50), status ENUM('ordered','in_transit','delivered','received','enrolled','return','cancelled') DEFAULT 'ordered', carrier ENUM('amazon','dhl','fedex','freight_ltl','ups','usps','vendor_supplied') DEFAULT NULL, tracking_number VARCHAR(500), additional_tracking JSON, vendor_order_number VARCHAR(255), vendor_po_number VARCHAR(50) DEFAULT NULL COMMENT 'Vendor-facing PO# when different from sp_number (alias for shipment matching)', vendor_quote_number VARCHAR(255) DEFAULT NULL, vendor_status VARCHAR(50) DEFAULT NULL, vendor_status_detail TEXT DEFAULT NULL, vendor_status_locked TINYINT(1) NOT NULL DEFAULT 0, expected_arrival DATE, expected_ship_date DATE DEFAULT NULL, ship_method ENUM('hq','dropship') DEFAULT 'hq', po_date DATE, due_date DATE, billing_street VARCHAR(500), billing_city VARCHAR(255), billing_state VARCHAR(100), billing_code VARCHAR(20), billing_country VARCHAR(100) DEFAULT 'United States', shipping_name VARCHAR(500), shipping_attention VARCHAR(500), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(100), shipping_code VARCHAR(20), shipping_country VARCHAR(100) DEFAULT 'United States', customer_po VARCHAR(255), sub_total DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0, tax DECIMAL(14,2) DEFAULT 0, adjustment DECIMAL(14,2) DEFAULT 0, grand_total DECIMAL(14,2) DEFAULT 0, zoho_po_id VARCHAR(50), zoho_synced_at TIMESTAMP NULL, myob_exported_at TIMESTAMP NULL, refunded_at TIMESTAMP NULL DEFAULT NULL, delivery_type ENUM('physical','digital') NOT NULL DEFAULT 'physical', license_status ENUM('pending','processed') NULL, enrollment_date DATE NULL, enrollment_end_date DATE NULL, last_polled_at TIMESTAMP NULL, vendor_submitted_at TIMESTAMP NULL, vendor_submitted_by INT NULL, asus_adid_draft_id VARCHAR(255) NULL COMMENT 'Gmail draft ID for ASUS ADID request to Billy@ASUS', asus_adid_draft_created_at TIMESTAMP NULL COMMENT 'When the ADID request draft was created', asus_adid_draft_serial_count INT NULL COMMENT 'Serial count at time of draft creation', created_by INT, updated_by INT, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, gcal_event_id VARCHAR(255) DEFAULT NULL, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (parent_po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, INDEX idx_po_sp_number (sp_number), INDEX idx_po_mts_number (mts_number), INDEX idx_po_status (status), INDEX idx_po_vendor (vendor_id), INDEX idx_po_date (po_date), INDEX idx_po_zoho (zoho_po_id), INDEX idx_po_ship_method (ship_method), INDEX idx_po_type_refunded (po_type, refunded_at), INDEX idx_po_parent (parent_po_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ───────────────────────────────────────────────────────── -- PO → Sales Order M:N link (stores ADDITIONAL orders beyond purchase_orders.mts_number primary) -- ───────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS purchase_order_orders ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, order_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_by INT DEFAULT NULL, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uq_po_order (po_id, order_id), INDEX idx_poo_order (order_id), INDEX idx_poo_po (po_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS purchase_order_items ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, line_number INT DEFAULT 1, product_name VARCHAR(500), part_number VARCHAR(255), description VARCHAR(500), vendor_sku VARCHAR(255), asin VARCHAR(20) DEFAULT NULL, quantity INT DEFAULT 1, quantity_received INT DEFAULT 0, unit_price DECIMAL(12,2) DEFAULT 0, quoted_unit_price DECIMAL(12,2) DEFAULT NULL, discount DECIMAL(12,2) DEFAULT 0, total DECIMAL(12,2) DEFAULT 0, zoho_product_id VARCHAR(50), notes TEXT, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, INDEX idx_poi_po (po_id), INDEX idx_poi_part (part_number), INDEX idx_poi_asin (asin) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- Warehouse: Device Tracking (purchase_order_tracking, purchase_order_devices) -- ═══════════════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS device_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO device_sequences (prefix, next_number) VALUES ('DI-A', 1); INSERT IGNORE INTO device_sequences (prefix, next_number) VALUES ('AUD', 1); INSERT IGNORE INTO device_sequences (prefix, next_number) VALUES ('HQ', 1); CREATE TABLE IF NOT EXISTS purchase_order_tracking ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, tracking_number VARCHAR(255) NOT NULL, carrier VARCHAR(100), ship_date DATE, eta_date DATE, source ENUM('vendor_api','shipstation','email','manual') DEFAULT 'manual', vendor_key VARCHAR(50), invoice_number VARCHAR(100), shipment_invoice_id INT, status ENUM('pre_transit','in_transit','out_for_delivery','delivered','returned','exception') DEFAULT 'in_transit', details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, UNIQUE KEY uq_po_tracking (po_id, tracking_number), INDEX idx_pot_tracking (tracking_number), INDEX idx_pot_source (source), INDEX idx_pot_vendor (vendor_key), INDEX idx_pot_invoice (shipment_invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS po_warranties ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT DEFAULT NULL, vendor_key VARCHAR(50) DEFAULT 'ebay', warranty_provider VARCHAR(100) DEFAULT 'SquareTrade', warranty_duration_months INT NOT NULL, purchase_date DATE NOT NULL, expiration_date DATE NOT NULL, ebay_order_number VARCHAR(100), ebay_item_id VARCHAR(50), item_title VARCHAR(500), quantity INT DEFAULT 1, source_id VARCHAR(150) NOT NULL, email_date TIMESTAMP NULL, raw_details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, UNIQUE KEY uq_warranty_source (source_id), INDEX idx_pw_po (po_id), INDEX idx_pw_ebay_order (ebay_order_number), INDEX idx_pw_expiration (expiration_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Per-device attested device IDs supplied by OEMs (e.g. ASUS "ADID" CSVs). -- Google's ZTE claim API requires the real chromeOsAttestedDeviceId for ASUS -- and any other OEM where serial != attestedId. When we receive a mapping -- CSV (from ASUS rep, Lenovo portal, etc.) we seed this table so subsequent -- claims can look up the correct attested ID by serial. See -- .claude/docs/zte-device-claiming.md and -- scripts/import-attested-device-ids.js. CREATE TABLE IF NOT EXISTS device_attested_ids ( serial_number VARCHAR(100) NOT NULL PRIMARY KEY, attested_device_id VARCHAR(100) NOT NULL, manufacturer VARCHAR(50), source VARCHAR(100), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_dai_attested (attested_device_id), INDEX idx_dai_manufacturer (manufacturer) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS purchase_order_devices ( id INT AUTO_INCREMENT PRIMARY KEY, device_id VARCHAR(20) NOT NULL UNIQUE, po_id INT, po_item_id INT, serial_number VARCHAR(100), mac_address VARCHAR(20), vendor_sku VARCHAR(100), part_number VARCHAR(255), product_name VARCHAR(500), tracking_number VARCHAR(255), status ENUM('expected','received','audited','graded','in_stock','shipped_out','rma') DEFAULT 'expected', condition_grade VARCHAR(10), received_at TIMESTAMP NULL, received_by INT, audited_at TIMESTAMP NULL, audited_by INT, source ENUM('vendor_api','macservice','shipstation','manual') DEFAULT 'manual', vendor_key VARCHAR(50) DEFAULT NULL, macservice_onum VARCHAR(30), mts_number VARCHAR(100), machine_type VARCHAR(100), po_reference VARCHAR(50), legacy_data JSON, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, FOREIGN KEY (po_item_id) REFERENCES purchase_order_items(id) ON DELETE SET NULL, FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (audited_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_pod_po (po_id), INDEX idx_pod_serial (serial_number), INDEX idx_pod_status (status), INDEX idx_pod_tracking (tracking_number), INDEX idx_pod_mts (mts_number), INDEX idx_pod_macservice (macservice_onum), INDEX idx_pod_po_ref (po_reference) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Device label templates — learned box label layouts for camera receiving CREATE TABLE IF NOT EXISTS device_label_templates ( id INT AUTO_INCREMENT PRIMARY KEY, part_number VARCHAR(255) NOT NULL, manufacturer VARCHAR(100) DEFAULT NULL, product_name VARCHAR(500) DEFAULT NULL, serial_has_barcode TINYINT(1) DEFAULT 0, serial_barcode_format VARCHAR(50) DEFAULT NULL, serial_format_regex VARCHAR(255) DEFAULT NULL, serial_label_text VARCHAR(100) DEFAULT NULL, mac_has_barcode TINYINT(1) DEFAULT 0, mac_barcode_format VARCHAR(50) DEFAULT NULL, mac_label_text VARCHAR(100) DEFAULT NULL, ignore_barcode_types JSON DEFAULT NULL, vision_prompt_hint TEXT DEFAULT NULL, label_layout_description TEXT DEFAULT NULL, reference_image_path VARCHAR(500) DEFAULT NULL, learned_from_image TINYINT(1) DEFAULT 0, learned_at TIMESTAMP NULL, confidence VARCHAR(20) DEFAULT 'low', times_used INT DEFAULT 0, last_used_at TIMESTAMP NULL, manually_verified TINYINT(1) DEFAULT 0, verified_by INT DEFAULT NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY idx_part_number (part_number), INDEX idx_manufacturer (manufacturer), FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Purchase order internal notes CREATE TABLE IF NOT EXISTS purchase_order_notes ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, user_id INT DEFAULT NULL, note TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_po (po_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Purchase order email summaries (AI-extracted from vendor + sales rep email threads) -- Dedupes processed messages and audits what Claude extracted so we can re-run safely. CREATE TABLE IF NOT EXISTS po_email_matches ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, gmail_thread_id VARCHAR(100) NOT NULL, gmail_message_id VARCHAR(100) NOT NULL, rfc_message_id VARCHAR(255) DEFAULT NULL, -- RFC 5322 Message-ID header; dedupes archive duplicates (same email, multiple recipients) note_id INT DEFAULT NULL, -- FK into purchase_order_notes (the system note created for this message) match_reason VARCHAR(64) NOT NULL, -- e.g. 'sp_number', 'vendor_po_number', 'vendor_order_number', 'vendor_quote_number', 'mts_number_plus_domain' match_confidence TINYINT NOT NULL, -- 0-100 from_address VARCHAR(255) DEFAULT NULL, is_rep_only TINYINT(1) NOT NULL DEFAULT 0, -- rep-to-rep message, no actionable content is_action_needed TINYINT(1) NOT NULL DEFAULT 0, ai_summary VARCHAR(500) DEFAULT NULL, ai_extracted_json LONGTEXT DEFAULT NULL, applied_updates_json LONGTEXT DEFAULT NULL, -- which PO fields got auto-updated internal_date BIGINT DEFAULT NULL, -- Gmail internalDate (ms since epoch) for ordering created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, FOREIGN KEY (note_id) REFERENCES purchase_order_notes(id) ON DELETE SET NULL, UNIQUE KEY uq_po_message (po_id, gmail_message_id), UNIQUE KEY uq_po_rfc_message (po_id, rfc_message_id), INDEX idx_po (po_id), INDEX idx_thread (gmail_thread_id), INDEX idx_internal_date (internal_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Purchase order daily audit log (tracks issues across runs for escalation + self-healing) CREATE TABLE IF NOT EXISTS po_audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, sp_number VARCHAR(20) NOT NULL, check_type ENUM( 'stale_ordered', 'missing_tracking', 'stale_in_transit', 'eta_overdue', 'ship_date_overdue', 'manual_vendor', 'tracking_exception', 'partial_receiving', 'dropship_stuck', 'no_vendor_status', 'vendor_status_anomaly' ) NOT NULL, severity ENUM('critical', 'warning', 'info') NOT NULL, detail TEXT, consecutive_count INT DEFAULT 1, resolved_at TIMESTAMP NULL, healing_action VARCHAR(255) DEFAULT NULL, audit_date DATE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, INDEX idx_pal_po (po_id), INDEX idx_pal_date (audit_date), INDEX idx_pal_type (check_type), INDEX idx_pal_unresolved (po_id, check_type, resolved_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- PO activity log (user + system actions for audit trail) CREATE TABLE IF NOT EXISTS po_activity ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, user_id INT DEFAULT NULL, action VARCHAR(100) NOT NULL, details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id), FOREIGN KEY (user_id) REFERENCES users(id), INDEX idx_pa_po (po_id), INDEX idx_pa_action (action), INDEX idx_pa_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Order daily audit log (tracks issues across runs for escalation + self-healing) CREATE TABLE IF NOT EXISTS order_audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, mts_number VARCHAR(20) NOT NULL, check_type ENUM( 'stale_new', 'stale_pending', 'stale_approved', 'stale_on_order', 'missing_tracking', 'stale_shipped', 'overdue_delivery', 'tracking_exception', 'stuck_shipment', 'unpaid_complete', 'wg_incomplete', 'attention_flagged', 'no_linked_po', 'serial_mismatch' ) NOT NULL, severity ENUM('critical', 'warning', 'info') NOT NULL, detail TEXT, consecutive_count INT DEFAULT 1, resolved_at TIMESTAMP NULL, healing_action VARCHAR(255) DEFAULT NULL, audit_date DATE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_oal_order (order_id), INDEX idx_oal_date (audit_date), INDEX idx_oal_type (check_type), INDEX idx_oal_unresolved (order_id, check_type, resolved_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- Finance Module — Accounts Receivable -- ═══════════════════════════════════════════════════════════════════════════════ -- AR invoice number sequences (INV prefix, atomic increment) CREATE TABLE IF NOT EXISTS ar_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 100001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Invoices — main invoice tracking CREATE TABLE IF NOT EXISTS ar_invoices ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_number VARCHAR(20) UNIQUE NOT NULL, -- Linkage order_id INT DEFAULT NULL, mts_number VARCHAR(20) DEFAULT NULL, zoho_invoice_id VARCHAR(50) DEFAULT NULL, customer_org_id INT DEFAULT NULL, -- Customer info (denormalized for display/email) customer_name VARCHAR(500) NOT NULL, district_name VARCHAR(500) DEFAULT NULL, ap_name VARCHAR(255) DEFAULT NULL, ap_email VARCHAR(255) DEFAULT NULL, ap_phone VARCHAR(50) DEFAULT NULL, po_number VARCHAR(100) DEFAULT NULL, -- Billing address billing_street VARCHAR(500) DEFAULT NULL, billing_city VARCHAR(255) DEFAULT NULL, billing_state VARCHAR(100) DEFAULT NULL, billing_zip VARCHAR(20) DEFAULT NULL, -- Shipping address shipping_street VARCHAR(500) DEFAULT NULL, shipping_city VARCHAR(255) DEFAULT NULL, shipping_state VARCHAR(100) DEFAULT NULL, shipping_zip VARCHAR(20) DEFAULT NULL, -- Amounts subtotal DECIMAL(14,2) DEFAULT 0, tax_amount DECIMAL(14,2) DEFAULT 0, shipping_amount DECIMAL(14,2) DEFAULT 0, cc_fee DECIMAL(14,2) DEFAULT 0, discount DECIMAL(14,2) DEFAULT 0, adjustment DECIMAL(14,2) DEFAULT 0, grand_total DECIMAL(14,2) DEFAULT 0, amount_paid DECIMAL(14,2) DEFAULT 0, balance_due DECIMAL(14,2) DEFAULT 0, -- Payment terms payment_method ENUM('net30','credit_card','ach_wire','check','other') DEFAULT 'net30', terms_days INT DEFAULT 30, -- Dates invoice_date DATE NOT NULL, due_date DATE NOT NULL, ship_date DATE DEFAULT NULL, paid_date DATE DEFAULT NULL, -- Status lifecycle: draft → pending → sent → overdue → collections → paid status ENUM('draft','pending','sent','viewed','partial','paid','overdue','collections','void','write_off') DEFAULT 'draft', -- Metadata serial_numbers TEXT DEFAULT NULL, internal_notes TEXT DEFAULT NULL, rep VARCHAR(100) DEFAULT NULL, source ENUM('auto_ingest','manual','zoho_import') DEFAULT 'auto_ingest', notifications_enabled TINYINT(1) NOT NULL DEFAULT 1, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL, FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ar_status (status), INDEX idx_ar_customer_org (customer_org_id), INDEX idx_ar_mts (mts_number), INDEX idx_ar_zoho (zoho_invoice_id), INDEX idx_ar_due_date (due_date), INDEX idx_ar_invoice_date (invoice_date), INDEX idx_ar_payment_method (payment_method), INDEX idx_ar_balance (balance_due) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Invoice Line Items CREATE TABLE IF NOT EXISTS ar_invoice_items ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, line_number INT DEFAULT 0, product_code VARCHAR(100) DEFAULT NULL, product_name VARCHAR(500) NOT NULL, description TEXT DEFAULT NULL, quantity DECIMAL(10,2) DEFAULT 1, unit_price DECIMAL(12,2) DEFAULT 0, discount DECIMAL(12,2) DEFAULT 0, tax DECIMAL(12,2) DEFAULT 0, line_total DECIMAL(12,2) DEFAULT 0, zoho_product_id VARCHAR(50) DEFAULT NULL, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, INDEX idx_ari_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Payments — track partial or full payments CREATE TABLE IF NOT EXISTS ar_payments ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL, payment_date DATE NOT NULL, payment_method ENUM('check','ach','wire','credit_card','cash','other') DEFAULT 'check', reference_number VARCHAR(100) DEFAULT NULL, notes TEXT DEFAULT NULL, recorded_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_arp_invoice (invoice_id), INDEX idx_arp_date (payment_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Notification Log — every email sent CREATE TABLE IF NOT EXISTS ar_notifications ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, notification_type ENUM('invoice','reminder_7','reminder_1','overdue_7','overdue_14','overdue_21','overdue_30','overdue_45','overdue_60','overdue_75','overdue_90_final','payment_received','custom') NOT NULL, recipient_email VARCHAR(255) NOT NULL, subject VARCHAR(500) DEFAULT NULL, ses_message_id VARCHAR(255) DEFAULT NULL, status ENUM('sent','failed','bounced','blocked') DEFAULT 'sent', error_message TEXT DEFAULT NULL, sent_by INT DEFAULT NULL, sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_arn_invoice (invoice_id), INDEX idx_arn_type (notification_type), INDEX idx_arn_sent (sent_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Notification Schedule — future emails to send CREATE TABLE IF NOT EXISTS ar_notification_schedule ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, notification_type ENUM('invoice','reminder_7','reminder_1','overdue_7','overdue_14','overdue_21','overdue_30','overdue_45','overdue_60','overdue_75','overdue_90_final','custom') NOT NULL, scheduled_for DATETIME NOT NULL, status ENUM('pending','sent','cancelled','failed') DEFAULT 'pending', processed_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, INDEX idx_arns_pending (status, scheduled_for), INDEX idx_arns_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR Activity Log — audit trail CREATE TABLE IF NOT EXISTS ar_activity ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, action VARCHAR(50) NOT NULL, detail TEXT DEFAULT NULL, performed_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ara_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Chase Deposit Auto-Matcher — log every Chase "direct deposit posted" alert -- we process. The Gmail message_id is the dedup key. `action` records what -- the matcher decided ('applied', 'no_match', 'ambiguous', 'skipped', -- 'manual'). When `action='applied'`, the Zoho Customer Payment id and the -- target invoice are denormalized for audit + UI. -- Despite the legacy name, this table now logs three payment-confirmation -- email sources, distinguished by `source`: -- chase_ach — "Your $X direct deposit posted" (wire / ACH) -- chase_check — "We received your check deposit" (QuickDeposit) -- zoho_braintree — "Received Online Payment from " (card via Zoho/Braintree) -- chase_message_id is reused as the gmail message id of the trigger email, -- regardless of sender. account_last4 / posted_date are nullable (Braintree -- has neither). gateway_transaction_id is populated for card payments. CREATE TABLE IF NOT EXISTS chase_deposit_matches ( id INT AUTO_INCREMENT PRIMARY KEY, chase_message_id VARCHAR(128) NOT NULL UNIQUE, source VARCHAR(32) NOT NULL DEFAULT 'chase_ach', received_at DATETIME NOT NULL, posted_date DATE DEFAULT NULL, amount DECIMAL(12,2) NOT NULL, account_last4 VARCHAR(4) DEFAULT NULL, match_method ENUM('billdotcom_memo','open_invoice_amount','invoice_number_email','manual','none') DEFAULT 'none', action ENUM('applied','already_applied','no_match','ambiguous','skipped','manual') NOT NULL, zoho_invoice_id VARCHAR(64) DEFAULT NULL, zoho_invoice_number VARCHAR(50) DEFAULT NULL, zoho_customer_id VARCHAR(64) DEFAULT NULL, zoho_customer_name VARCHAR(255) DEFAULT NULL, zoho_payment_id VARCHAR(64) DEFAULT NULL, gateway_transaction_id VARCHAR(128) DEFAULT NULL, billdotcom_message_id VARCHAR(128) DEFAULT NULL, candidates_json LONGTEXT DEFAULT NULL, -- JSON array of candidate invoice summaries when ambiguous notes TEXT DEFAULT NULL, applied_at DATETIME DEFAULT NULL, applied_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (applied_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_cdm_received (received_at), INDEX idx_cdm_action (action), INDEX idx_cdm_amount (amount), INDEX idx_cdm_invoice_number (zoho_invoice_number), INDEX idx_cdm_source (source) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- AR schema extensions (Phase 2+3) -- Add assigned_to for collections assignment -- ALTER TABLE ar_invoices ADD COLUMN IF NOT EXISTS assigned_to INT DEFAULT NULL; -- ALTER TABLE ar_invoices ADD CONSTRAINT fk_ar_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL; -- Extend ar_activity for collections notes -- ALTER TABLE ar_activity ADD COLUMN IF NOT EXISTS notes TEXT DEFAULT NULL; -- ALTER TABLE ar_activity ADD COLUMN IF NOT EXISTS contact_method ENUM('call','email','note','system') DEFAULT 'system'; -- Extend notification_type ENUMs to support new intervals -- (Run manually — ENUMs need ALTER TABLE MODIFY) -- ALTER TABLE ar_notification_schedule MODIFY notification_type VARCHAR(50) NOT NULL; -- ALTER TABLE ar_notifications MODIFY notification_type VARCHAR(50) NOT NULL; -- AR Reminder Configuration — configurable notification schedule CREATE TABLE IF NOT EXISTS ar_reminder_config ( id INT AUTO_INCREMENT PRIMARY KEY, label VARCHAR(100) NOT NULL, days_offset INT NOT NULL COMMENT 'Negative = before due, positive = after due', target ENUM('customer','internal') DEFAULT 'customer', enabled TINYINT(1) DEFAULT 1, sort_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Without this, the seed INSERT IGNORE below appended the same 12 rows on every -- mkl-core restart (caught at 16,680 rows / 1390 dups before being deduped). UNIQUE KEY uq_reminder_config (label, days_offset, target) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- For pre-existing tables created before the unique key was added. ALTER TABLE ar_reminder_config ADD UNIQUE KEY IF NOT EXISTS uq_reminder_config (label, days_offset, target); -- Seed default reminder schedule (matching Zoho config) INSERT IGNORE INTO ar_reminder_config (label, days_offset, target, enabled, sort_order) VALUES ('7 days before due date', -7, 'customer', 1, 1), ('On Due Date', -1, 'customer', 1, 2), ('1 week after due date', 7, 'customer', 1, 3), ('2 weeks overdue', 14, 'customer', 1, 4), ('AR Internal Call Reminder - 15 Days After', 15, 'internal', 1, 5), ('4 weeks overdue', 28, 'customer', 1, 6), ('AR Internal Call Reminder - 29 Days After', 29, 'internal', 1, 7), ('AR Internal Call Reminder - 40 Days After', 40, 'internal', 1, 8), ('6 weeks overdue', 42, 'customer', 1, 9), ('8 weeks overdue', 56, 'customer', 1, 10), ('10 weeks overdue', 70, 'customer', 1, 11), ('12 weeks overdue', 84, 'customer', 1, 12); -- AR Payment Tokens — for customer-facing payment links CREATE TABLE IF NOT EXISTS ar_payment_tokens ( id INT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(64) UNIQUE NOT NULL, invoice_id INT NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE, INDEX idx_arpt_token (token), INDEX idx_arpt_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Zoho Invoice → MKL AR sync (see scripts/migrations/add-zoho-invoice-sync.sql) ALTER TABLE ar_invoices ADD COLUMN IF NOT EXISTS zoho_invoice_app_id VARCHAR(50) DEFAULT NULL AFTER zoho_invoice_id, ADD COLUMN IF NOT EXISTS zoho_status VARCHAR(30) DEFAULT NULL AFTER status, ADD COLUMN IF NOT EXISTS zoho_last_modified DATETIME DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_synced_at DATETIME DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_last_payment_date DATE DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_payment_made DECIMAL(14,2) DEFAULT NULL; -- Per-invoice CC list (see scripts/migrations/add-ar-cc-emails.sql) ALTER TABLE ar_invoices ADD COLUMN IF NOT EXISTS cc_emails TEXT DEFAULT NULL AFTER ap_phone; CREATE UNIQUE INDEX IF NOT EXISTS uq_ar_zoho_app_id ON ar_invoices (zoho_invoice_app_id); CREATE INDEX IF NOT EXISTS idx_ar_zoho_modified ON ar_invoices (zoho_last_modified); ALTER TABLE ar_payments ADD COLUMN IF NOT EXISTS zoho_payment_id VARCHAR(50) DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_payment_number VARCHAR(40) DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_payment_mode VARCHAR(40) DEFAULT NULL, ADD COLUMN IF NOT EXISTS zoho_synced_at DATETIME DEFAULT NULL, ADD COLUMN IF NOT EXISTS source ENUM('local','zoho_sync','chase_matcher') DEFAULT 'local'; CREATE UNIQUE INDEX IF NOT EXISTS uq_arp_zoho_payment ON ar_payments (zoho_payment_id, invoice_id); CREATE INDEX IF NOT EXISTS idx_arp_zoho_payment_id ON ar_payments (zoho_payment_id); CREATE TABLE IF NOT EXISTS ar_sync_state ( job_name VARCHAR(50) PRIMARY KEY, last_run_at DATETIME DEFAULT NULL, last_success_at DATETIME DEFAULT NULL, cursor_value VARCHAR(100) DEFAULT NULL, last_result_json LONGTEXT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Bounce detector dedup ledger. One row per delivery-failure NDR ever processed -- by core/lib/ar-bounce-detector.js. Primary key is the Gmail message id of -- the NDR itself, so re-running the cron over the same NDR is a clean no-op. -- See ar-bounce-detector.js for write-order semantics (ledger row is written -- LAST so partial failures retry instead of silently mark "done"). CREATE TABLE IF NOT EXISTS ar_bounce_ndr_ledger ( ndr_message_id VARCHAR(100) PRIMARY KEY, processed_at DATETIME DEFAULT CURRENT_TIMESTAMP, matched_notification_id INT DEFAULT NULL, failed_recipient VARCHAR(255) DEFAULT NULL, original_message_id VARCHAR(100) DEFAULT NULL, reason TEXT DEFAULT NULL, INDEX idx_processed (processed_at), INDEX idx_recipient (failed_recipient) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════ -- CREDIT APPROVALS -- ══════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS credit_approval_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 100001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO credit_approval_sequences (prefix, next_number) VALUES ('MCA', 100001); CREATE TABLE IF NOT EXISTS credit_approvals ( id INT AUTO_INCREMENT PRIMARY KEY, approval_number VARCHAR(20) UNIQUE NOT NULL, quote_number VARCHAR(20) NOT NULL, application_id INT DEFAULT NULL, app_number VARCHAR(20) DEFAULT NULL, status ENUM('draft','active','used','expired','voided') DEFAULT 'draft', customer_name VARCHAR(500) NOT NULL, signer_name VARCHAR(255) DEFAULT NULL, signer_title VARCHAR(200) DEFAULT NULL, ap_name VARCHAR(255) DEFAULT NULL, ap_email VARCHAR(255) DEFAULT NULL, territory VARCHAR(50) DEFAULT NULL, assigned_salesperson VARCHAR(255) DEFAULT NULL, quote_total DECIMAL(12,2) NOT NULL DEFAULT 0, credit_pct DECIMAL(5,2) NOT NULL DEFAULT 50.00, credit_amount DECIMAL(12,2) NOT NULL DEFAULT 0, prepayment_amount DECIMAL(12,2) NOT NULL DEFAULT 0, terms_days INT NOT NULL DEFAULT 30, conditions TEXT DEFAULT NULL, internal_notes TEXT DEFAULT NULL, pdf_filename VARCHAR(255) DEFAULT NULL, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, expires_at DATE DEFAULT NULL, INDEX idx_ca_quote (quote_number), INDEX idx_ca_app (app_number), INDEX idx_ca_status (status), FOREIGN KEY (application_id) REFERENCES form_submissions(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════ -- SALES METRICS -- ══════════════════════════════════════════════════════════ -- SKUs excluded from sales metrics (tax, shipping, handling line items) -- is_pass_through = TRUE marks the subset that's never real revenue (sales tax, eWaste, shipping) -- so it can also be subtracted from the Shipped Orders display total. Other excluded rows -- (warranties, services, placeholders) are revenue but excluded from sales-metrics calculations. CREATE TABLE IF NOT EXISTS metrics_excluded_skus ( id INT AUTO_INCREMENT PRIMARY KEY, product_code VARCHAR(100) NOT NULL, reason VARCHAR(255) DEFAULT NULL, is_pass_through BOOLEAN NOT NULL DEFAULT FALSE, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_product_code (product_code), FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Monthly revenue targets per sales rep CREATE TABLE IF NOT EXISTS sales_targets ( id INT AUTO_INCREMENT PRIMARY KEY, rep VARCHAR(100) NOT NULL, year SMALLINT NOT NULL, month TINYINT NOT NULL, target_revenue DECIMAL(14,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_rep_year_month (rep, year, month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- White Glove tasks JSON ALTER TABLE orders ADD COLUMN IF NOT EXISTS wg_tasks_json JSON DEFAULT NULL; -- Indexes for metrics queries ALTER TABLE orders ADD INDEX IF NOT EXISTS idx_orders_ship_date (ship_date); ALTER TABLE orders ADD INDEX IF NOT EXISTS idx_orders_status_ship (status, ship_date); -- ─── Marketing Module ───────────────────────────────────────────────────────── -- Brand assets (logos, PDFs, mission statements, etc.) CREATE TABLE IF NOT EXISTS marketing_assets ( id INT AUTO_INCREMENT PRIMARY KEY, category ENUM('logo', 'pdf', 'mission_statement', 'brand_guide', 'photo', 'video', 'other') NOT NULL DEFAULT 'other', title VARCHAR(255) NOT NULL, description TEXT, original_name VARCHAR(500) NOT NULL, filename VARCHAR(500) NOT NULL, mimetype VARCHAR(100), size INT DEFAULT 0, uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_category (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trade shows CREATE TABLE IF NOT EXISTS marketing_tradeshows ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, location VARCHAR(500), venue VARCHAR(255), start_date DATE, end_date DATE, booth_number VARCHAR(50), booth_size VARCHAR(100), status ENUM('upcoming', 'active', 'completed', 'cancelled') DEFAULT 'upcoming', website VARCHAR(500), notes TEXT, map_filename VARCHAR(500), ship_to_date DATE, ship_back_date DATE, setup_date DATE, teardown_date DATE, created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_start_date (start_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trade show costs CREATE TABLE IF NOT EXISTS marketing_tradeshow_costs ( id INT AUTO_INCREMENT PRIMARY KEY, tradeshow_id INT NOT NULL, category ENUM('booth', 'logistics', 'travel', 'lodging', 'marketing_materials', 'food', 'sponsorship', 'other') NOT NULL, description VARCHAR(255), amount DECIMAL(10,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tradeshow_id) REFERENCES marketing_tradeshows(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trade show attendees CREATE TABLE IF NOT EXISTS marketing_tradeshow_attendees ( id INT AUTO_INCREMENT PRIMARY KEY, tradeshow_id INT NOT NULL, user_id INT, name VARCHAR(255) NOT NULL, role VARCHAR(100), flight_info TEXT, hotel_info TEXT, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tradeshow_id) REFERENCES marketing_tradeshows(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trade show items (flooring, tables, backdrops, demo devices, etc.) CREATE TABLE IF NOT EXISTS marketing_tradeshow_items ( id INT AUTO_INCREMENT PRIMARY KEY, tradeshow_id INT NOT NULL, item_name VARCHAR(255) NOT NULL, category ENUM('display', 'furniture', 'technology', 'signage', 'promotional', 'other') DEFAULT 'other', quantity INT DEFAULT 1, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tradeshow_id) REFERENCES marketing_tradeshows(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trade show files (invoices, receipts, maps, etc.) CREATE TABLE IF NOT EXISTS marketing_tradeshow_files ( id INT AUTO_INCREMENT PRIMARY KEY, tradeshow_id INT NOT NULL, file_type ENUM('invoice', 'receipt', 'map', 'contract', 'photo', 'other') DEFAULT 'other', original_name VARCHAR(500) NOT NULL, filename VARCHAR(500) NOT NULL, mimetype VARCHAR(100), size INT DEFAULT 0, uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tradeshow_id) REFERENCES marketing_tradeshows(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- Google License Purchases — D&H automated ordering with duplicate prevention -- ═══════════════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS google_license_purchases ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, line_item_id INT NOT NULL, mts_number VARCHAR(50) NOT NULL, status ENUM('dry_run', 'processing', 'success', 'failed') NOT NULL DEFAULT 'dry_run', quantity INT NOT NULL, unit_cost DECIMAL(10,2) NOT NULL DEFAULT 29.26, total_cost DECIMAL(10,2) NOT NULL, dh_sku VARCHAR(100), dh_order_number VARCHAR(100) DEFAULT NULL, sp_number VARCHAR(20) DEFAULT NULL, customer_po VARCHAR(255), customer_name VARCHAR(500), contact_email VARCHAR(255), domain_name VARCHAR(255), shipping_street VARCHAR(500), shipping_city VARCHAR(255), shipping_state VARCHAR(10), shipping_zip VARCHAR(20), end_user_contact VARCHAR(255), end_user_phone VARCHAR(50), request_payload JSON DEFAULT NULL, response_payload JSON DEFAULT NULL, error_message TEXT DEFAULT NULL, dry_run TINYINT(1) NOT NULL DEFAULT 1, created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (line_item_id) REFERENCES order_line_items(id) ON DELETE CASCADE, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_glp_order (order_id), INDEX idx_glp_mts (mts_number), INDEX idx_glp_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Draft Orders — tracks Shopify Draft Orders created via checkout gating CREATE TABLE IF NOT EXISTS draft_orders ( id INT AUTO_INCREMENT PRIMARY KEY, shopify_draft_order_id VARCHAR(100) NOT NULL, shopify_draft_order_number VARCHAR(50), invoice_url VARCHAR(2000), org_id INT NOT NULL, customer_email VARCHAR(255) NOT NULL, po_number VARCHAR(100), line_items_json JSON, subtotal DECIMAL(12,2), tax_exempt TINYINT(1) DEFAULT 0, status ENUM('open','invoice_sent','completed','cancelled') DEFAULT 'open', shopify_order_id VARCHAR(100) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (org_id) REFERENCES customer_organizations(id), INDEX idx_draft_shopify_id (shopify_draft_order_id), INDEX idx_draft_email (customer_email), INDEX idx_draft_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ --- PunchOut cXML Integration (OPS / Sage Oak) --- ═══════════════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS punchout_buyers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, identity_value VARCHAR(255) NOT NULL, shared_secret VARCHAR(255) NOT NULL, customer_org_id INT DEFAULT NULL, ship_to_name VARCHAR(255) DEFAULT NULL, ship_to_street VARCHAR(500) DEFAULT NULL, ship_to_city VARCHAR(255) DEFAULT NULL, ship_to_state VARCHAR(100) DEFAULT NULL, ship_to_zip VARCHAR(20) DEFAULT NULL, bill_to_name VARCHAR(255) DEFAULT NULL, bill_to_street VARCHAR(500) DEFAULT NULL, bill_to_city VARCHAR(255) DEFAULT NULL, bill_to_state VARCHAR(100) DEFAULT NULL, bill_to_zip VARCHAR(20) DEFAULT NULL, tax_exempt TINYINT(1) DEFAULT 0, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, UNIQUE KEY uq_buyer_identity (identity_value), INDEX idx_active (is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS punchout_sessions ( id INT AUTO_INCREMENT PRIMARY KEY, session_token VARCHAR(64) NOT NULL, buyer_id INT NOT NULL, buyer_cookie TEXT NOT NULL, browser_form_post_url TEXT NOT NULL, operation VARCHAR(20) DEFAULT 'create', ship_to_name VARCHAR(255) DEFAULT NULL, ship_to_street VARCHAR(500) DEFAULT NULL, ship_to_city VARCHAR(255) DEFAULT NULL, ship_to_state VARCHAR(100) DEFAULT NULL, ship_to_zip VARCHAR(20) DEFAULT NULL, cart_data JSON DEFAULT NULL, status ENUM('active','submitted','expired') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, submitted_at TIMESTAMP NULL, FOREIGN KEY (buyer_id) REFERENCES punchout_buyers(id) ON DELETE CASCADE, UNIQUE KEY uq_session_token (session_token), INDEX idx_status (status), INDEX idx_expires (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS punchout_order_log ( id INT AUTO_INCREMENT PRIMARY KEY, buyer_id INT NOT NULL, po_number VARCHAR(100) DEFAULT NULL, mts_number VARCHAR(20) DEFAULT NULL, order_id INT DEFAULT NULL, cxml_payload MEDIUMTEXT, line_item_count INT DEFAULT 0, total DECIMAL(12,2) DEFAULT 0, status ENUM('received','processed','error') DEFAULT 'received', error_message TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (buyer_id) REFERENCES punchout_buyers(id) ON DELETE CASCADE, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL, INDEX idx_po (po_number), INDEX idx_mts (mts_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO purchase_order_sequences (prefix, next_number) VALUES ('MTS', 200000); INSERT IGNORE INTO purchase_order_sequences (prefix, next_number) VALUES ('RMA', 100001); -- ─── Order-Ticket link table (repair orders linked to support tickets) ─── CREATE TABLE IF NOT EXISTS order_tickets ( order_id INT NOT NULL, ticket_id INT NOT NULL, linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (order_id, ticket_id), FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Opportunities (groups multiple quotes for the same customer/buying decision) ─── CREATE TABLE IF NOT EXISTS opportunity_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 10001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS opportunities ( id INT AUTO_INCREMENT PRIMARY KEY, opp_number VARCHAR(20) UNIQUE, name VARCHAR(255) DEFAULT NULL, account_name VARCHAR(255) NOT NULL, customer_org_id INT DEFAULT NULL, contact_name VARCHAR(255) DEFAULT NULL, contact_email VARCHAR(255) DEFAULT NULL, rep VARCHAR(100), status ENUM('open','won','lost','stale') DEFAULT 'open', lost_reason VARCHAR(255) DEFAULT NULL, competitor VARCHAR(255) DEFAULT NULL, close_date DATE DEFAULT NULL, expected_revenue DECIMAL(12,2) DEFAULT 0, stage ENUM('quoting','sent','negotiation','verbal_commit') DEFAULT 'quoting', probability TINYINT UNSIGNED DEFAULT 25, primary_quote_id INT DEFAULT NULL, revenue_override DECIMAL(12,2) DEFAULT NULL, notes TEXT, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (primary_quote_id) REFERENCES deals(id) ON DELETE SET NULL, INDEX idx_opp_status (status), INDEX idx_opp_rep (rep), INDEX idx_opp_customer (customer_org_id), INDEX idx_opp_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Migration: ALTER TABLE opportunities ADD COLUMN stage ENUM('quoting','sent','negotiation','verbal_commit') DEFAULT 'quoting' AFTER expected_revenue, ADD COLUMN probability TINYINT UNSIGNED DEFAULT 25 AFTER stage, ADD COLUMN primary_quote_id INT DEFAULT NULL AFTER probability, ADD COLUMN revenue_override DECIMAL(12,2) DEFAULT NULL AFTER primary_quote_id, ADD CONSTRAINT fk_opp_primary_quote FOREIGN KEY (primary_quote_id) REFERENCES deals(id) ON DELETE SET NULL; -- ─── Opportunity file attachments (mirrors deal_files) ──────────── CREATE TABLE IF NOT EXISTS opportunity_files ( id INT AUTO_INCREMENT PRIMARY KEY, opportunity_id INT NOT NULL, filename VARCHAR(255) NOT NULL, original_name VARCHAR(255), file_size INT, uploaded_by INT DEFAULT NULL, source VARCHAR(50) DEFAULT NULL COMMENT 'e.g. deal_registration, manual', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_opp_files (opportunity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Quote sequences (MKL-native quotes use Q-XXXXXX numbering) ─── CREATE TABLE IF NOT EXISTS quote_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 100001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Zoho discount repair audit log CREATE TABLE IF NOT EXISTS zoho_discount_repair_log ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, mts_number VARCHAR(20), product_code VARCHAR(100), old_unit_price DECIMAL(12,2), new_unit_price DECIMAL(12,2), old_line_total DECIMAL(12,2), new_line_total DECIMAL(12,2), old_discount DECIMAL(12,2), new_discount DECIMAL(12,2), repaired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_mts (mts_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Deal Desk Settings ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS deal_desk_settings ( setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Seed default Terms & Conditions INSERT IGNORE INTO deal_desk_settings (setting_key, setting_value) VALUES ('terms_and_conditions', 'Payment Terms: Net 30 days from invoice date.\nPricing: All prices are in USD and valid for 30 days from quote date.\nDelivery: Estimated delivery times are subject to product availability.\nWarranty: All products include manufacturer warranty unless otherwise stated.\nReturns: Returns must be authorized within 14 days of delivery. A restocking fee may apply.\nTax: Sales tax will be applied where required by law.\nAcceptance: This quote constitutes an offer and is subject to Tech to School standard terms of sale.'); -- ── Coretek Deal Desk ────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS coretek_deal_sequences ( prefix VARCHAR(10) PRIMARY KEY, next_number INT NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO coretek_deal_sequences (prefix, next_number) VALUES ('CT', 79); -- Coretek deals — one row per opportunity CREATE TABLE IF NOT EXISTS coretek_deals ( id INT AUTO_INCREMENT PRIMARY KEY, deal_number VARCHAR(20) UNIQUE NOT NULL COMMENT 'e.g. CT079-CA', status ENUM('draft','need_pricing','pricing_received','quoted','submitted','approved','in_progress','completed','paid','lost','cancelled') DEFAULT 'draft', customer_name VARCHAR(255), customer_org VARCHAR(500), customer_email VARCHAR(255), customer_phone VARCHAR(50), customer_state VARCHAR(10) COMMENT 'US state abbreviation for pickup location', customer_address TEXT COMMENT 'Full pickup/ship address', pickup_locations INT DEFAULT 1 COMMENT 'Number of pickup sites', pickup_notes TEXT COMMENT 'Logistics notes — palletizing, white-glove, access, etc.', tts_quote_number VARCHAR(30) COMMENT 'Linked MTS/MTQ number if applicable', total_devices INT DEFAULT 0, total_value DECIMAL(12,2) DEFAULT 0 COMMENT 'Expected value from Coretek pricing', tts_commission DECIMAL(12,2) DEFAULT 0 COMMENT 'TTS share of the deal', coretek_contact VARCHAR(100) DEFAULT 'Neil Rodrigues' COMMENT 'Primary Coretek contact', notes TEXT COMMENT 'General deal notes', internal_notes TEXT COMMENT 'TTS-only internal notes', zoho_account_id VARCHAR(50) NULL, created_by INT, submitted_at TIMESTAMP NULL, priced_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ct_status (status), INDEX idx_ct_state (customer_state), INDEX idx_ct_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Coretek deal line items — one row per SKU/device model per deal CREATE TABLE IF NOT EXISTS coretek_deal_items ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, sku VARCHAR(255) COMMENT 'Part number / MPN if available', description VARCHAR(500) NOT NULL, device_category ENUM('chromebook','laptop','desktop','tablet','monitor','phone','networking','accessory','other') DEFAULT 'chromebook', quantity INT DEFAULT 0, condition_notes TEXT COMMENT 'Known condition info — % working, cases, etching, etc.', price_grade_a DECIMAL(10,2) DEFAULT NULL COMMENT 'Coretek A-grade price per unit', price_grade_b DECIMAL(10,2) DEFAULT NULL COMMENT 'Coretek B-grade price per unit', price_grade_c DECIMAL(10,2) DEFAULT NULL COMMENT 'Coretek C-grade price per unit', price_grade_d DECIMAL(10,2) DEFAULT NULL COMMENT 'Coretek D-grade price per unit', flat_price DECIMAL(10,2) DEFAULT NULL COMMENT 'Flat per-unit price (alternative to graded)', line_total DECIMAL(12,2) DEFAULT 0 COMMENT 'Estimated total for this line', sort_order INT DEFAULT 0, FOREIGN KEY (deal_id) REFERENCES coretek_deals(id) ON DELETE CASCADE, INDEX idx_ct_item_deal (deal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Coretek deal comments — threaded communication (TTS + Coretek) CREATE TABLE IF NOT EXISTS coretek_deal_comments ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, user_id INT NULL COMMENT 'NULL for external (Coretek) comments', author_name VARCHAR(255) NOT NULL, author_type ENUM('tts','coretek') DEFAULT 'tts', content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (deal_id) REFERENCES coretek_deals(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ct_comment_deal (deal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Coretek deal files — attachments (inventory lists, audit reports, pricing sheets) CREATE TABLE IF NOT EXISTS coretek_deal_files ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, file_type ENUM('inventory_list','pricing_sheet','audit_report','invoice','other') NOT NULL DEFAULT 'other', filename VARCHAR(255) NOT NULL, original_name VARCHAR(500) NOT NULL, file_size INT DEFAULT 0, uploaded_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (deal_id) REFERENCES coretek_deals(id) ON DELETE CASCADE, FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Tech Module: Autopilot Hashes ─────────────────────────────── CREATE TABLE IF NOT EXISTS autopilot_hashes ( id INT AUTO_INCREMENT PRIMARY KEY, serial_number VARCHAR(100) NOT NULL, hardware_hash TEXT NOT NULL, device_model VARCHAR(255) DEFAULT NULL, order_id VARCHAR(50) DEFAULT NULL, group_tag VARCHAR(100) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_serial (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Default group_tag and/or order_id auto-applied to the next N new autopilot_hashes -- (singleton row: id=1). Decremented atomically when a new device posts its hash -- without an explicit value. Either tag or order_id (or both) may be set. CREATE TABLE IF NOT EXISTS autopilot_default_tag ( id TINYINT NOT NULL PRIMARY KEY DEFAULT 1, tag VARCHAR(64) NULL, order_id VARCHAR(50) NULL, remaining INT NOT NULL DEFAULT 0, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Google Admin Console device management audit log CREATE TABLE IF NOT EXISTS google_admin_actions ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, org_id INT DEFAULT NULL, action_type ENUM('set_asset_id', 'move_to_ou', 'list_devices') NOT NULL, device_id VARCHAR(255) DEFAULT NULL, serial_number VARCHAR(255) DEFAULT NULL, details JSON DEFAULT NULL, performed_by VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_order (order_id), INDEX idx_org (org_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── Chrome License Data Collection ───────────────────────────── -- Tokens for public Chrome License form access (maps token → org + order) CREATE TABLE IF NOT EXISTS chrome_license_tokens ( id INT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(64) NOT NULL UNIQUE, customer_org_id INT NOT NULL, order_id INT DEFAULT NULL, created_by INT DEFAULT NULL, expires_at TIMESTAMP NOT NULL, used_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_token (token), FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE CASCADE, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Chrome License form submissions (full history preserved) CREATE TABLE IF NOT EXISTS chrome_license_submissions ( id INT AUTO_INCREMENT PRIMARY KEY, customer_org_id INT NOT NULL, order_id INT DEFAULT NULL, token_id INT DEFAULT NULL, end_user_company_name VARCHAR(500) DEFAULT NULL, domain VARCHAR(255) NOT NULL, primary_email VARCHAR(255) NOT NULL, tech_contact_name VARCHAR(255) DEFAULT NULL, tech_contact_email VARCHAR(255) DEFAULT NULL, tech_contact_phone VARCHAR(100) DEFAULT NULL, secondary_email VARCHAR(255) DEFAULT NULL, enrollment_email_name VARCHAR(255) DEFAULT NULL, enrollment_email_password VARCHAR(255) DEFAULT NULL, wifi_ssid VARCHAR(255) DEFAULT NULL, wifi_password VARCHAR(255) DEFAULT NULL, submitted_by_ip VARCHAR(45) DEFAULT NULL, submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, source ENUM('form','csv_import','manual') DEFAULT 'form', INDEX idx_org (customer_org_id), INDEX idx_order (order_id), FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE CASCADE, FOREIGN KEY (token_id) REFERENCES chrome_license_tokens(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════════════ -- Accounts Payable — Bill.com ACH payments -- ══════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS ap_payments ( id INT AUTO_INCREMENT PRIMARY KEY, po_id INT DEFAULT NULL, sp_number VARCHAR(20) DEFAULT NULL, vendor_id INT NOT NULL, vendor_name VARCHAR(500), amount DECIMAL(14,2) NOT NULL, description TEXT, billcom_vendor_id VARCHAR(100), billcom_bill_id VARCHAR(100), billcom_payment_id VARCHAR(100), process_date DATE, status ENUM('pending','processing','completed','failed','cancelled') DEFAULT 'pending', error_message TEXT, initiated_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (vendor_id) REFERENCES vendors(id), FOREIGN KEY (initiated_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_ap_po (po_id), INDEX idx_ap_vendor (vendor_id), INDEX idx_ap_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════════════ -- DLL Group financing — 60-day net credit-line transactions -- Ingested from MICHAEL.VIZZARRI@DLLGROUP.COM emails: -- APPROVAL ALERT (per-invoice approval, kicks off 60-day countdown) -- Consolidated Transaction Document (daily PDF statement) -- ONLINE PAYMENT STATUS (scheduled / actual debits) -- One row per dll_approval_number; subsequent emails UPDATE same row. -- ══════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS dll_transactions ( id INT AUTO_INCREMENT PRIMARY KEY, dll_approval_number VARCHAR(40) NOT NULL, dll_account_number VARCHAR(20) DEFAULT '29937', vendor_name VARCHAR(120) DEFAULT NULL, vendor_id INT DEFAULT NULL, vendor_dll_code VARCHAR(20) DEFAULT NULL, amount DECIMAL(14,2) NOT NULL, currency CHAR(3) DEFAULT 'USD', status ENUM('approved','scheduled','paid','cancelled','unknown') DEFAULT 'approved', approval_date DATETIME NOT NULL, due_date DATE NOT NULL, scheduled_payment_date DATE DEFAULT NULL, actual_payment_date DATE DEFAULT NULL, sp_number VARCHAR(20) DEFAULT NULL, mts_number VARCHAR(20) DEFAULT NULL, vendor_invoice_number VARCHAR(40) DEFAULT NULL, -- e.g. D&H invoice # (1393270044), enriched from Consolidated PDF po_id INT DEFAULT NULL, match_method ENUM('sp_number','mts_number','fuzzy_vad','manual','unmatched') DEFAULT 'unmatched', match_confidence TINYINT DEFAULT 0, chase_match_message_id VARCHAR(128) DEFAULT NULL, -- Gmail msg ID of the Chase withdrawal that confirmed this payment cx_paid TINYINT(1) DEFAULT 0, -- 1 if customer fully paid us per Zoho Invoice (lookup by mts_number) cx_paid_balance DECIMAL(14,2) DEFAULT NULL, -- remaining balance on the Zoho invoice cx_paid_checked_at DATETIME DEFAULT NULL, source_message_ids LONGTEXT DEFAULT NULL, -- JSON array of Gmail message IDs parsed_data LONGTEXT DEFAULT NULL, -- JSON: per-source-type extracted fields last_email_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, UNIQUE KEY uq_dll_approval (dll_approval_number), INDEX idx_dll_status (status), INDEX idx_dll_due (due_date), INDEX idx_dll_vendor (vendor_id), INDEX idx_dll_po (po_id), INDEX idx_dll_sp (sp_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ══════════════════════════════════════════════════════════════════════ -- Deal Registration — Manufacturer deal reg tracking (Lenovo, etc.) -- ══════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS deal_registrations ( id INT AUTO_INCREMENT PRIMARY KEY, manufacturer VARCHAR(50) NOT NULL DEFAULT 'lenovo', drpn VARCHAR(50) DEFAULT NULL, brpn VARCHAR(50) DEFAULT NULL, deal_name VARCHAR(255), description TEXT, end_customer VARCHAR(255), customer_crm_id VARCHAR(50), external_opp_id VARCHAR(50) DEFAULT NULL, -- HP OPD-..., Google opportunity ID (e.g. SDHXZKSerf1Dvw) tax_id VARCHAR(100), reseller VARCHAR(100), product_type VARCHAR(50), country VARCHAR(10) DEFAULT 'US', status ENUM('submitted','approved','declined','expiring','expired','bid_approved','price_confirmed') NOT NULL DEFAULT 'submitted', decline_reason TEXT, lenovo_comments TEXT, registration_date DATE, expiry_date DATE, bid_expiry_date DATE, bid_valid_days INT, distributors VARCHAR(255), price_pdf_message_id VARCHAR(100), rep_email VARCHAR(100), deal_id INT DEFAULT NULL, opportunity_id INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_drpn (drpn), INDEX idx_dr_status (status), INDEX idx_dr_manufacturer (manufacturer), INDEX idx_dr_rep (rep_email), INDEX idx_dr_customer (end_customer), INDEX idx_dr_deal (deal_id), INDEX idx_dr_opp (opportunity_id), INDEX idx_dr_brpn (brpn) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_registration_events ( id INT AUTO_INCREMENT PRIMARY KEY, deal_reg_id INT NOT NULL, event_type ENUM('submitted','account_created','approved','declined','expiring','expired','bid_submitted','bid_approved','bid_declined','price_confirmed','review_submitted','review_declined') NOT NULL, event_date DATETIME NOT NULL, gmail_message_id VARCHAR(100), subject VARCHAR(500), to_email VARCHAR(100), cc_emails TEXT, body_text TEXT, details JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (deal_reg_id) REFERENCES deal_registrations(id) ON DELETE CASCADE, INDEX idx_dre_reg_date (deal_reg_id, event_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_registration_items ( id INT AUTO_INCREMENT PRIMARY KEY, deal_reg_id INT NOT NULL, distributor VARCHAR(100), contract_number VARCHAR(50), mcn VARCHAR(50), line_item INT, part_number VARCHAR(50) NOT NULL, description VARCHAR(255), quantity INT, unit_price DECIMAL(10,2), total_price DECIMAL(12,2), discount_pct VARCHAR(20), currency VARCHAR(10) DEFAULT 'USD', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (deal_reg_id) REFERENCES deal_registrations(id) ON DELETE CASCADE, INDEX idx_dri_reg (deal_reg_id), INDEX idx_dri_part (part_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_registration_contacts ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL, name VARCHAR(100), title VARCHAR(200), manufacturer VARCHAR(50) DEFAULT 'lenovo', frequency INT DEFAULT 1, last_seen DATETIME, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_drc_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_registration_contact_links ( id INT AUTO_INCREMENT PRIMARY KEY, deal_reg_id INT NOT NULL, contact_id INT NOT NULL, FOREIGN KEY (deal_reg_id) REFERENCES deal_registrations(id) ON DELETE CASCADE, FOREIGN KEY (contact_id) REFERENCES deal_registration_contacts(id) ON DELETE CASCADE, UNIQUE KEY uq_drcl (deal_reg_id, contact_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── White Glove Enrollment ────────────────────────────────────── CREATE TABLE IF NOT EXISTS white_glove_sessions ( id INT AUTO_INCREMENT PRIMARY KEY, session_number VARCHAR(20) UNIQUE NOT NULL, po_id INT NOT NULL, mts_number VARCHAR(50), customer_name VARCHAR(500), status ENUM('active','paused','completed') DEFAULT 'active', started_by INT DEFAULT NULL, completed_at TIMESTAMP NULL, total_expected INT DEFAULT 0, total_enrolled INT DEFAULT 0, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE, FOREIGN KEY (started_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_wgs_po (po_id), INDEX idx_wgs_status (status), INDEX idx_wgs_user (started_by) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS white_glove_devices ( id INT AUTO_INCREMENT PRIMARY KEY, session_id INT NOT NULL, po_device_id INT DEFAULT NULL, serial_number VARCHAR(100) NOT NULL, mac_address VARCHAR(20), asset_tag VARCHAR(100), status ENUM('scanned','enrolled','label_printed','complete') DEFAULT 'scanned', scanned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, enrolled_at TIMESTAMP NULL, label_printed_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, scanned_by INT, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (session_id) REFERENCES white_glove_sessions(id) ON DELETE CASCADE, FOREIGN KEY (po_device_id) REFERENCES purchase_order_devices(id) ON DELETE SET NULL, FOREIGN KEY (scanned_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_wgd_session (session_id), INDEX idx_wgd_serial (serial_number), INDEX idx_wgd_asset (asset_tag), INDEX idx_wgd_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================= -- Audit Module — Device cosmetic/functional inspection -- Printers come from the existing HQ CUPS server (see core/routes/printing-routes.js). -- We store CUPS queue names directly — no separate printer table. -- ============================================= CREATE TABLE IF NOT EXISTS audit_user_prefs ( user_id INT PRIMARY KEY, printer_queue VARCHAR(100), auto_print TINYINT(1) DEFAULT 1, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS audit_sessions ( id INT AUTO_INCREMENT PRIMARY KEY, session_number VARCHAR(20) UNIQUE NOT NULL, user_id INT NOT NULL, printer_queue VARCHAR(100), status ENUM('active','completed') DEFAULT 'active', device_count INT DEFAULT 0, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_as_user (user_id), INDEX idx_as_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS audit_devices ( id INT AUTO_INCREMENT PRIMARY KEY, session_id INT, po_device_id INT, serial_number VARCHAR(100) NOT NULL, device_type ENUM('chromebook','ipad','macbook') NOT NULL, model VARCHAR(300), -- PO context po_number VARCHAR(50), po_id INT, -- Pre-populated system data (Go-Box / Wilbur) storage_gb VARCHAR(20), ram_gb VARCHAR(20), battery_health VARCHAR(20), battery_cycles VARCHAR(20), os_version VARCHAR(100), -- Grading cosmetic_grade CHAR(1), functional_grade CHAR(1), final_grade VARCHAR(3) NOT NULL, -- Detailed audit data (device-type-specific) cosmetic_data JSON NOT NULL, functional_data JSON NOT NULL, failure_summary TEXT, auditor_notes TEXT, -- Flags hd_failure TINYINT(1) DEFAULT 0, logic_failure TINYINT(1) DEFAULT 0, mdm_lock TINYINT(1) DEFAULT 0, activation_lock TINYINT(1) DEFAULT 0, -- Label label_printed TINYINT(1) DEFAULT 0, label_printed_at TIMESTAMP NULL, -- Audit metadata audit_start TIMESTAMP NULL, audited_by INT NOT NULL, audited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, audit_duration_seconds INT, FOREIGN KEY (session_id) REFERENCES audit_sessions(id) ON DELETE SET NULL, FOREIGN KEY (po_device_id) REFERENCES purchase_order_devices(id) ON DELETE SET NULL, FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, FOREIGN KEY (audited_by) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_ad_serial (serial_number), INDEX idx_ad_session (session_id), INDEX idx_ad_po (po_id), INDEX idx_ad_grade (final_grade), INDEX idx_ad_date (audited_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS audit_gobox_data ( id INT AUTO_INCREMENT PRIMARY KEY, serial_number VARCHAR(100) NOT NULL, device_model VARCHAR(300), storage_gb VARCHAR(20), ram_gb VARCHAR(20), os_version VARCHAR(100), board_name VARCHAR(100), cpu VARCHAR(200), battery_health VARCHAR(20), battery_cycles VARCHAR(20), raw_data JSON, consumed TINYINT(1) DEFAULT 0, consumed_by_audit_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE INDEX idx_agb_serial (serial_number), INDEX idx_agb_consumed (consumed) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- HR Module — Time Clock, Timesheets, PTO, Payroll -- ============================================================ CREATE TABLE IF NOT EXISTS hr_employees ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, employee_type ENUM('hq', 'remote') NOT NULL DEFAULT 'hq', hourly_rate DECIMAL(10,2) DEFAULT NULL, pto_accrual_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00 COMMENT 'Hours accrued per pay period', pto_balance DECIMAL(8,2) NOT NULL DEFAULT 0.00, hire_date DATE DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uq_hr_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_pay_periods ( id INT AUTO_INCREMENT PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL, pay_date DATE NOT NULL, status ENUM('open', 'closed') NOT NULL DEFAULT 'open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_pp_dates (start_date, end_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_time_punches ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, punch_type ENUM('clock_in', 'clock_out') NOT NULL, raw_time DATETIME NOT NULL, rounded_time DATETIME NOT NULL, source ENUM('employee', 'admin_edit') NOT NULL DEFAULT 'employee', notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE, INDEX idx_htp_emp_time (employee_id, raw_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_time_edit_requests ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, punch_id INT DEFAULT NULL, requested_date DATE NOT NULL, requested_time TIME NOT NULL, punch_type ENUM('clock_in', 'clock_out') NOT NULL, reason TEXT, status ENUM('pending', 'approved', 'denied') NOT NULL DEFAULT 'pending', reviewed_by INT DEFAULT NULL, reviewed_at TIMESTAMP NULL, admin_notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE, FOREIGN KEY (punch_id) REFERENCES hr_time_punches(id) ON DELETE SET NULL, FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_hter_emp (employee_id), INDEX idx_hter_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_timesheets ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, work_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, break_minutes INT NOT NULL DEFAULT 0, total_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00, notes TEXT, status ENUM('draft', 'submitted', 'approved', 'rejected') NOT NULL DEFAULT 'draft', reviewed_by INT DEFAULT NULL, reviewed_at TIMESTAMP NULL, admin_notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE, FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uq_ts_emp_date (employee_id, work_date), INDEX idx_hts_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_pto_requests ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, hours_requested DECIMAL(6,2) NOT NULL, reason TEXT, status ENUM('pending', 'approved', 'denied') NOT NULL DEFAULT 'pending', reviewed_by INT DEFAULT NULL, reviewed_at TIMESTAMP NULL, admin_notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE, FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_hpr_emp (employee_id), INDEX idx_hpr_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_pto_ledger ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, change_type ENUM('accrual', 'used', 'adjustment') NOT NULL, hours DECIMAL(8,2) NOT NULL COMMENT 'Positive for accrual/adjustment, negative for used', balance_after DECIMAL(8,2) NOT NULL, reference_id INT DEFAULT NULL COMMENT 'Links to pto_request id or pay_period id', notes TEXT, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_hpl_emp (employee_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_calendar_sources ( id INT AUTO_INCREMENT PRIMARY KEY, calendar_id VARCHAR(255) NOT NULL UNIQUE, label VARCHAR(100) NOT NULL, color VARCHAR(20) DEFAULT '#3b82f6', is_active TINYINT(1) DEFAULT 1, added_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ═══════════════════════════════════════════════════════════════════════════════ -- HQ Inventory — Serial-tracked physical inventory at HQ -- ═══════════════════════════════════════════════════════════════════════════════ CREATE TABLE IF NOT EXISTS hq_inventory ( id INT AUTO_INCREMENT PRIMARY KEY, device_id VARCHAR(20) NOT NULL UNIQUE, serial_number VARCHAR(100) NOT NULL, mac_address VARCHAR(20), shopify_variant_id BIGINT, sku VARCHAR(100), product_name VARCHAR(500), manufacturer VARCHAR(100), part_number VARCHAR(255), config VARCHAR(100) DEFAULT NULL, color VARCHAR(50) DEFAULT NULL, condition_grade ENUM('new','refurbished','first_stock','second_stock') NOT NULL DEFAULT 'new', device_type ENUM('chrome','windows','mac','ipad','accessory','other') DEFAULT NULL, unit_cost DECIMAL(12,2) DEFAULT 0, unit_price DECIMAL(12,2) DEFAULT 0, intake_source ENUM('return','buyback','over_order','demo','audit','manual','csv_upload') NOT NULL DEFAULT 'manual', source_reference VARCHAR(255), po_device_id INT DEFAULT NULL, status ENUM('available','shipped','wholesale','broken','locked') NOT NULL DEFAULT 'available', reserved_by INT DEFAULT NULL, reserved_at TIMESTAMP NULL, sale_reference VARCHAR(255), sold_at TIMESTAMP NULL, intake_by INT, intake_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (intake_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (reserved_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_hqi_serial (serial_number), INDEX idx_hqi_status (status), INDEX idx_hqi_variant (shopify_variant_id), INDEX idx_hqi_sku (sku), INDEX idx_hqi_condition (condition_grade), INDEX idx_hqi_source (intake_source), INDEX idx_hqi_manufacturer (manufacturer) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Competitive Intelligence ───────────────────────────────── CREATE TABLE IF NOT EXISTS ci_vendors ( id INT AUTO_INCREMENT PRIMARY KEY, canonical_name VARCHAR(255) NOT NULL UNIQUE, display_name VARCHAR(255) NOT NULL, aliases JSON, website VARCHAR(500), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ci_districts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(500) NOT NULL, state VARCHAR(100), city VARCHAR(255), ben VARCHAR(20) DEFAULT NULL, boarddocs_path VARCHAR(100) DEFAULT NULL, customer_org_id INT DEFAULT NULL, portal_url VARCHAR(500), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL, UNIQUE KEY uq_ci_district_name_state (name(255), state), INDEX idx_ci_districts_ben (ben) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ci_sources ( id INT AUTO_INCREMENT PRIMARY KEY, source_key VARCHAR(100) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, source_type ENUM('excel','pdf','api','manual') NOT NULL, base_url VARCHAR(500), last_sync_at TIMESTAMP NULL, last_sync_status ENUM('success','error','partial') DEFAULT NULL, last_sync_message TEXT, config JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ci_contracts ( id INT AUTO_INCREMENT PRIMARY KEY, source_id INT NOT NULL, source_ref VARCHAR(255), vendor_id INT DEFAULT NULL, vendor_name_raw VARCHAR(500), district_id INT DEFAULT NULL, district_name_raw VARCHAR(500), contract_number VARCHAR(255), contract_name TEXT, category VARCHAR(255), amount DECIMAL(14,2) DEFAULT NULL, commitment_start DATE DEFAULT NULL, commitment_end DATE DEFAULT NULL, purchasing_method VARCHAR(255), coop_contract VARCHAR(255), budget_codes VARCHAR(500), department VARCHAR(255), discount_terms TEXT, funding_year VARCHAR(10) DEFAULT NULL, status ENUM('active','expired','pending','unknown') DEFAULT 'unknown', raw_data JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (source_id) REFERENCES ci_sources(id) ON DELETE CASCADE, FOREIGN KEY (vendor_id) REFERENCES ci_vendors(id) ON DELETE SET NULL, FOREIGN KEY (district_id) REFERENCES ci_districts(id) ON DELETE SET NULL, INDEX idx_ci_contracts_vendor (vendor_id), INDEX idx_ci_contracts_district (district_id), INDEX idx_ci_contracts_source (source_id), INDEX idx_ci_contracts_category (category), INDEX idx_ci_contracts_funding_year (funding_year), UNIQUE KEY uq_ci_contract_source_ref (source_id, source_ref) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ci_opportunities ( id INT AUTO_INCREMENT PRIMARY KEY, source_id INT NOT NULL, source_ref VARCHAR(255), project_id VARCHAR(100), reference_id VARCHAR(100), name TEXT, district_id INT DEFAULT NULL, district_name_raw VARCHAR(500), status ENUM('open','closed','awarded','cancelled','unknown') DEFAULT 'unknown', open_date DATE DEFAULT NULL, close_date DATE DEFAULT NULL, awarded_vendor_id INT DEFAULT NULL, awarded_vendor_raw VARCHAR(500), awarded_amount DECIMAL(14,2) DEFAULT NULL, category VARCHAR(255), raw_data JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (source_id) REFERENCES ci_sources(id) ON DELETE CASCADE, FOREIGN KEY (district_id) REFERENCES ci_districts(id) ON DELETE SET NULL, FOREIGN KEY (awarded_vendor_id) REFERENCES ci_vendors(id) ON DELETE SET NULL, INDEX idx_ci_opp_status (status), INDEX idx_ci_opp_district (district_id), UNIQUE KEY uq_ci_opp_source_ref (source_id, source_ref) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ci_import_log ( id INT AUTO_INCREMENT PRIMARY KEY, source_id INT NOT NULL, imported_by INT NOT NULL, filename VARCHAR(255), records_total INT DEFAULT 0, records_inserted INT DEFAULT 0, records_updated INT DEFAULT 0, records_skipped INT DEFAULT 0, errors JSON, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP NULL, FOREIGN KEY (source_id) REFERENCES ci_sources(id) ON DELETE CASCADE, FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Migration: Per-quote shipping/billing address (overrides customer_organizations) ALTER TABLE deals ADD COLUMN IF NOT EXISTS billing_name VARCHAR(500) DEFAULT NULL AFTER pdf_path, ADD COLUMN IF NOT EXISTS billing_attn VARCHAR(255) DEFAULT NULL AFTER billing_name, ADD COLUMN IF NOT EXISTS billing_street VARCHAR(500) DEFAULT NULL AFTER billing_attn, ADD COLUMN IF NOT EXISTS billing_street2 VARCHAR(500) DEFAULT NULL AFTER billing_street, ADD COLUMN IF NOT EXISTS billing_city VARCHAR(255) DEFAULT NULL AFTER billing_street2, ADD COLUMN IF NOT EXISTS billing_state VARCHAR(100) DEFAULT NULL AFTER billing_city, ADD COLUMN IF NOT EXISTS billing_zip VARCHAR(20) DEFAULT NULL AFTER billing_state, ADD COLUMN IF NOT EXISTS shipping_name VARCHAR(500) DEFAULT NULL AFTER billing_zip, ADD COLUMN IF NOT EXISTS shipping_attn VARCHAR(255) DEFAULT NULL AFTER shipping_name, ADD COLUMN IF NOT EXISTS shipping_street VARCHAR(500) DEFAULT NULL AFTER shipping_attn, ADD COLUMN IF NOT EXISTS shipping_street2 VARCHAR(500) DEFAULT NULL AFTER shipping_street, ADD COLUMN IF NOT EXISTS shipping_city VARCHAR(255) DEFAULT NULL AFTER shipping_street2, ADD COLUMN IF NOT EXISTS shipping_state VARCHAR(100) DEFAULT NULL AFTER shipping_city, ADD COLUMN IF NOT EXISTS shipping_zip VARCHAR(20) DEFAULT NULL AFTER shipping_state; -- Public ZTE submission tokens — one per order, used to let customers -- submit their Customer ID + Enrollment Token without logging in. CREATE TABLE IF NOT EXISTS zte_submission_tokens ( id INT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(64) NOT NULL UNIQUE, -- Token may be tied to an order (post-PO send) OR a deal/org (pre-PO send from Deal Desk). -- At least one of (order_id, deal_id) should be set. order_id INT NULL, deal_id INT NULL, customer_org_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, submitted_at TIMESTAMP NULL, submitted_ip VARCHAR(45) NULL, INDEX idx_zte_tokens_order (order_id), INDEX idx_zte_tokens_submitted (submitted_at), CONSTRAINT fk_zte_tokens_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_zte_tokens_deal FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, CONSTRAINT fk_zte_tokens_org FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ZTE Model Registry — vendor part number → Google ZTE canonical model name. -- See scripts/migrations/add-zte-model-registry.sql and -- .claude/docs/zte-device-claiming.md. CREATE TABLE IF NOT EXISTS zte_model_registry ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_part_number VARCHAR(100) NOT NULL, manufacturer_part VARCHAR(100) NOT NULL, vendor VARCHAR(50) DEFAULT NULL, manufacturer VARCHAR(50) NOT NULL, google_model_name VARCHAR(255) NOT NULL, attested_id_source ENUM('serial','barcode_8char','asus_billy','none') NOT NULL DEFAULT 'serial', is_zte_compatible TINYINT(1) NOT NULL DEFAULT 1, product_family VARCHAR(100) DEFAULT NULL, notes TEXT DEFAULT NULL, source ENUM('seed_extract_regex','seed_google_list','live_claim_success','manual') NOT NULL DEFAULT 'manual', verified_at TIMESTAMP NULL DEFAULT NULL, verified_by INT DEFAULT NULL, claim_success_count INT NOT NULL DEFAULT 0, claim_failure_count INT NOT NULL DEFAULT 0, last_failure_reason TEXT DEFAULT NULL, last_claim_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_part_vendor (vendor_part_number, vendor), INDEX idx_zmr_mfg_part (manufacturer_part), INDEX idx_zmr_manufacturer (manufacturer), INDEX idx_zmr_compatible (is_zte_compatible), INDEX idx_zmr_failures (claim_failure_count), FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS zte_unmapped_parts ( id INT AUTO_INCREMENT PRIMARY KEY, vendor_part_number VARCHAR(100) NOT NULL, vendor VARCHAR(50) DEFAULT NULL, sample_product_name VARCHAR(500) DEFAULT NULL, last_po_id INT DEFAULT NULL, last_po_item_id INT DEFAULT NULL, occurrence_count INT NOT NULL DEFAULT 1, status ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open', resolved_at TIMESTAMP NULL DEFAULT NULL, resolved_by INT DEFAULT NULL, first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_unmapped_part_vendor (vendor_part_number, vendor), INDEX idx_zup_status (status), FOREIGN KEY (last_po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL, FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS zte_lookup_audit ( id INT AUTO_INCREMENT PRIMARY KEY, part_number VARCHAR(100) DEFAULT NULL, vendor VARCHAR(50) DEFAULT NULL, product_name VARCHAR(500) DEFAULT NULL, db_manufacturer VARCHAR(50) DEFAULT NULL, db_model VARCHAR(255) DEFAULT NULL, db_attested_id_source VARCHAR(50) DEFAULT NULL, regex_manufacturer VARCHAR(50) DEFAULT NULL, regex_model VARCHAR(255) DEFAULT NULL, verdict VARCHAR(20) NOT NULL, used_source ENUM('regex','db') NOT NULL, po_id INT DEFAULT NULL, serial_number VARCHAR(100) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_zla_verdict (verdict), INDEX idx_zla_part (part_number), INDEX idx_zla_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Per-user, per-module activity pings for the admin "Usage" modal. -- Each row = ~30 seconds of focused attention. Aggregation: COUNT(*) * 30. -- Retention 90 days; batched cleanup runs hourly (core/lib/usage-cleanup.js). CREATE TABLE IF NOT EXISTS usage_pings ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, module_id VARCHAR(50) NOT NULL, ping_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, url_path VARCHAR(255) DEFAULT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, KEY idx_user_module_at (user_id, module_id, ping_at), KEY idx_user_at (user_id, ping_at), KEY idx_at (ping_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Ask Mkl prompt bar ───────────────────────────────────────── -- Cmd-K modal where staff ask questions about MKL data. Backed by an -- agentic tool loop (core/lib/claude-agent.js) that calls a registry of -- tools (core/lib/claude-tools/) wrapping existing query helpers. -- Per-user access toggled via users.claude_access_enabled (default 0) -- and budget-capped via users.claude_daily_cap_usd (default $5/day). -- See .claude/docs/ask-mkl.md for the full architecture. CREATE TABLE IF NOT EXISTS claude_conversations ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, title VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, KEY idx_cc_user_updated (user_id, updated_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One row per turn segment from the model or user. The full Anthropic -- content array is stored as JSON in `content_json` so tool_use / tool_result -- blocks round-trip exactly. `role` matches the SDK: 'user' | 'assistant'. -- Tool-result turns are stored as role='user' with tool_result blocks inside, -- per Anthropic's message format. CREATE TABLE IF NOT EXISTS claude_messages ( id INT AUTO_INCREMENT PRIMARY KEY, conversation_id INT NOT NULL, role ENUM('user','assistant') NOT NULL, content_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (conversation_id) REFERENCES claude_conversations(id) ON DELETE CASCADE, KEY idx_cm_conv (conversation_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- TTS-curated "standard attach" products — items we sell on most orders that -- live under a TTS-internal name rather than a single clean vendor SKU. -- Examples: Google Chrome Management Console (Education Perpetual), White -- Glove Chromebook provisioning, etc. Survives the daily vendor catalog -- refresh because it's edited manually, not synced. CREATE TABLE IF NOT EXISTS curated_products ( id INT AUTO_INCREMENT PRIMARY KEY, category ENUM('license','warranty','service','accessory','other') NOT NULL, name VARCHAR(255) NOT NULL, aliases JSON DEFAULT NULL, manufacturer VARCHAR(100) DEFAULT NULL, primary_vendor VARCHAR(100) DEFAULT NULL, primary_vendor_sku VARCHAR(100) DEFAULT NULL, primary_mfg_part VARCHAR(100) DEFAULT NULL, cost_usd DECIMAL(10,2) NOT NULL, price_usd DECIMAL(10,2) NOT NULL, notes TEXT, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_curated_name (name), KEY idx_curated_category (category), KEY idx_curated_active (is_active), FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One row per Anthropic API call (so a single user turn that triggers 3 tool -- iterations writes 3 rows). Used for cost tracking + per-user daily caps. CREATE TABLE IF NOT EXISTS claude_usage_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, conversation_id INT DEFAULT NULL, model VARCHAR(64) NOT NULL, input_tokens INT NOT NULL DEFAULT 0, output_tokens INT NOT NULL DEFAULT 0, cache_creation_tokens INT NOT NULL DEFAULT 0, cache_read_tokens INT NOT NULL DEFAULT 0, cost_usd DECIMAL(10,6) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (conversation_id) REFERENCES claude_conversations(id) ON DELETE SET NULL, KEY idx_cul_user_at (user_id, created_at), KEY idx_cul_conv (conversation_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Ask Mkl learning signals ────────────────────────────────────── -- Captured automatically during/after each user turn. Three signal types: -- correction — the next user message read as a correction of the prior -- assistant turn ("no", "actually", "wrong", "I meant"…). -- no_result — a tool returned _summary indicating zero rows or an error. -- hedge — the assistant final reply contained a hedge phrase -- ("I don't have", "I couldn't find", "I'm not sure"…). -- These feed the admin Insights page so prompt/tool gaps are visible. CREATE TABLE IF NOT EXISTS claude_signals ( id BIGINT AUTO_INCREMENT PRIMARY KEY, conversation_id INT NOT NULL, signal_type ENUM('correction','no_result','hedge') NOT NULL, detail VARCHAR(500) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (conversation_id) REFERENCES claude_conversations(id) ON DELETE CASCADE, KEY idx_cs_type_at (signal_type, created_at), KEY idx_cs_conv (conversation_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Per-user memory for Ask Mkl ────────────────────────────────────── -- Auto-rolled-up bullet list of "what this user cares about" — frequent -- customers, product categories, terms they use. Generated weekly by -- /api/claude/regenerate-memory-cron from their recent conversations. -- Injected into the user-turn context so the agent has rep-specific -- background without any manual upkeep. CREATE TABLE IF NOT EXISTS claude_user_memory ( user_id INT PRIMARY KEY, content TEXT, generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, generated_from_conv_count INT NOT NULL DEFAULT 0, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Ask Mkl golden regression set ───────────────────────────────────── -- Hand-curated canonical questions Ask Mkl should always answer well. -- A nightly cron runs each one against the live agent and records the -- reply + tools called, comparing against expected_keywords / expected_tools. -- Slack pings #mkl on regression. Lets us edit the system prompt without -- worrying about silently breaking the most important questions. CREATE TABLE IF NOT EXISTS claude_goldset ( id INT AUTO_INCREMENT PRIMARY KEY, question TEXT NOT NULL, expected_keywords_json JSON DEFAULT NULL, expected_tools_json JSON DEFAULT NULL, notes TEXT, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_cgs_active (is_active), FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS claude_goldset_runs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, goldset_id INT NOT NULL, reply TEXT, tools_called_json JSON DEFAULT NULL, passed_keywords TINYINT(1) NOT NULL DEFAULT 0, passed_tools TINYINT(1) NOT NULL DEFAULT 0, passed_overall TINYINT(1) NOT NULL DEFAULT 0, notes TEXT, cost_usd DECIMAL(10,6) DEFAULT 0, iterations INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (goldset_id) REFERENCES claude_goldset(id) ON DELETE CASCADE, KEY idx_cgr_gold_at (goldset_id, created_at), KEY idx_cgr_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Google Partner Network Hub opportunity queue ────────────────────── -- Auto-creates Partner Hub opportunities for Chrome Edu license quotes -- (qty >= 200 on a single line). Two-phase puppeteer flow: -- pending → filling → awaiting_approval → approved → submitting → submitted -- See .claude/docs/partner-hub-automation.md for full architecture. CREATE TABLE IF NOT EXISTS google_partner_opportunities ( id INT AUTO_INCREMENT PRIMARY KEY, deal_id INT NOT NULL, deal_number VARCHAR(30) NOT NULL, customer_org_id INT DEFAULT NULL, license_qty INT NOT NULL DEFAULT 0, license_revenue DECIMAL(12,2) NOT NULL DEFAULT 0, license_sku VARCHAR(100) DEFAULT NULL, distributor VARCHAR(50) DEFAULT NULL, status ENUM('pending','filling','awaiting_approval','approved','submitting','submitted','failed','cancelled','skipped') DEFAULT 'pending', review_screenshot_path VARCHAR(500) DEFAULT NULL, google_opportunity_id VARCHAR(50) DEFAULT NULL, google_opportunity_url VARCHAR(500) DEFAULT NULL, skip_reason VARCHAR(255) DEFAULT NULL, error_message TEXT DEFAULT NULL, retry_count INT DEFAULT 0, last_attempted_at TIMESTAMP NULL, approved_at TIMESTAMP NULL, approved_by INT DEFAULT NULL, submitted_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_deal (deal_id), INDEX idx_status (status), INDEX idx_created (created_at), FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (customer_org_id) REFERENCES customer_organizations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;