USE BeezifiIOM;

CREATE TABLE IF NOT EXISTS connector_certifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    integration_scope ENUM('commerce','accounting') NOT NULL,
    integration_id INT UNSIGNED NOT NULL,
    platform VARCHAR(50) NOT NULL,
    certification_level ENUM('sandbox','partner_validated','production_certified') NOT NULL DEFAULT 'sandbox',
    checklist JSON NULL,
    notes TEXT NULL,
    last_certified_at TIMESTAMP NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_scope_integration (tenant_id, integration_scope, integration_id),
    INDEX idx_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS onboarding_diagnostics (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    integration_scope ENUM('commerce','accounting') NOT NULL,
    integration_id INT UNSIGNED NULL,
    platform VARCHAR(50) NOT NULL,
    severity ENUM('info','warning','error') NOT NULL DEFAULT 'info',
    diagnostic_code VARCHAR(80) NOT NULL,
    message VARCHAR(500) NOT NULL,
    payload JSON NULL,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_created (tenant_id, created_at),
    INDEX idx_tenant_platform (tenant_id, platform),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS demand_forecasts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NULL,
    forecast_window_days INT UNSIGNED NOT NULL DEFAULT 30,
    horizon_days INT UNSIGNED NOT NULL DEFAULT 30,
    avg_daily_demand DECIMAL(12,4) NOT NULL DEFAULT 0,
    projected_demand DECIMAL(12,4) NOT NULL DEFAULT 0,
    confidence_score DECIMAL(5,2) NOT NULL DEFAULT 0,
    model_version VARCHAR(30) NOT NULL DEFAULT 'moving_avg_v1',
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_product_wh_window (tenant_id, product_id, warehouse_id, forecast_window_days, horizon_days),
    INDEX idx_tenant_generated (tenant_id, generated_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS supplier_lead_time_stats (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    supplier_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NULL,
    avg_lead_time_days DECIMAL(10,2) NOT NULL DEFAULT 0,
    p90_lead_time_days DECIMAL(10,2) NOT NULL DEFAULT 0,
    sample_size INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_supplier_product (tenant_id, supplier_id, product_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS replenishment_recommendations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NULL,
    recommended_qty DECIMAL(12,3) NOT NULL,
    projected_stockout_date DATE NULL,
    target_service_level DECIMAL(5,2) NOT NULL DEFAULT 95.00,
    rationale JSON NULL,
    status ENUM('open','approved','converted','dismissed') NOT NULL DEFAULT 'open',
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_by INT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    INDEX idx_tenant_status_generated (tenant_id, status, generated_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS workflow_automation_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    conditions JSON NULL,
    actions JSON NOT NULL,
    priority INT NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_event_active (tenant_id, event_type, is_active),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS workflow_automation_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    rule_id INT UNSIGNED NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    context JSON NULL,
    outcome ENUM('triggered','skipped','failed') NOT NULL,
    error_message TEXT NULL,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_executed (tenant_id, executed_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (rule_id) REFERENCES workflow_automation_rules(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS approval_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    source_type VARCHAR(50) NOT NULL,
    source_id INT UNSIGNED NOT NULL,
    reason VARCHAR(255) NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    requested_by INT UNSIGNED NULL,
    resolved_by INT UNSIGNED NULL,
    resolution_note VARCHAR(500) NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    INDEX idx_tenant_status_requested (tenant_id, status, requested_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    role ENUM('admin','manager','staff') NOT NULL,
    permission_key VARCHAR(120) NOT NULL,
    granted TINYINT(1) NOT NULL DEFAULT 1,
    updated_by INT UNSIGNED NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_role_permission (tenant_id, role, permission_key),
    INDEX idx_tenant_role (tenant_id, role),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NULL,
    event_type VARCHAR(120) NOT NULL,
    entity_type VARCHAR(120) NULL,
    entity_id VARCHAR(120) NULL,
    payload JSON NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_created (tenant_id, created_at),
    INDEX idx_tenant_event (tenant_id, event_type),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sso_configs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    provider ENUM('oidc','saml') NOT NULL,
    issuer VARCHAR(255) NULL,
    client_id VARCHAR(255) NULL,
    client_secret TEXT NULL,
    metadata_url VARCHAR(500) NULL,
    domain_hint VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_provider (tenant_id, provider),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS import_jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    import_type ENUM('products','customers','orders','inventory') NOT NULL,
    status ENUM('uploaded','validated','processing','completed','failed') NOT NULL DEFAULT 'uploaded',
    source_file VARCHAR(500) NULL,
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    valid_rows INT UNSIGNED NOT NULL DEFAULT 0,
    invalid_rows INT UNSIGNED NOT NULL DEFAULT 0,
    summary JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_type_created (tenant_id, import_type, created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS import_job_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    import_job_id INT UNSIGNED NOT NULL,
    row_no INT UNSIGNED NOT NULL,
    status ENUM('valid','invalid','imported') NOT NULL,
    error_message VARCHAR(500) NULL,
    row_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_job_status (tenant_id, import_job_id, status),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (import_job_id) REFERENCES import_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cutover_playbooks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    status ENUM('draft','in_progress','completed') NOT NULL DEFAULT 'draft',
    target_go_live_at TIMESTAMP NULL,
    owner_user_id INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tenant_status (tenant_id, status),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cutover_steps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    playbook_id INT UNSIGNED NOT NULL,
    step_order INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    owner_role ENUM('admin','manager','staff') NOT NULL DEFAULT 'manager',
    status ENUM('todo','in_progress','done','blocked') NOT NULL DEFAULT 'todo',
    checklist JSON NULL,
    notes VARCHAR(500) NULL,
    completed_by INT UNSIGNED NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_playbook_step_order (playbook_id, step_order),
    INDEX idx_tenant_playbook_status (tenant_id, playbook_id, status),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (playbook_id) REFERENCES cutover_playbooks(id) ON DELETE CASCADE
) ENGINE=InnoDB;