<?php

/**
 * Migration 0041 — salon CRM (client profiles: before/after + color formulas)
 *
 * The client PROFILE itself reuses the existing instinctor_customers row (id,
 * name, email, phone, meta JSON, total_spent, order_count) and its visit
 * HISTORY is derived from the bookings + orders that already carry customer_id
 * — no duplication. Per-client soft fields (notes, tags, birthday, allergies,
 * preferences) live in customers.meta, so this migration touches no core table.
 *
 * What IS net-new is the salon differentiator: a per-client before/after photo
 * log and a color-formula log. Two additive tables, both keyed on customer_id.
 * A store that never opens a client profile is unaffected.
 *
 * Photos reuse the media pipeline (full + thumb URLs stored here); nothing is
 * duplicated into these tables beyond the reference URLs.
 */

if ( ! defined( 'ABSPATH' ) ) exit;

return [
    'description' => 'Create salon-CRM tables: client before/after photos + color formulas',

    'up' => [
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_client_photos (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            customer_id bigint(20) unsigned NOT NULL,
            booking_id bigint(20) unsigned NULL DEFAULT NULL,
            staff_id bigint(20) unsigned NULL DEFAULT NULL,
            service varchar(191) NOT NULL DEFAULT '',
            before_url varchar(500) NOT NULL DEFAULT '',
            before_thumb varchar(500) NOT NULL DEFAULT '',
            after_url varchar(500) NOT NULL DEFAULT '',
            after_thumb varchar(500) NOT NULL DEFAULT '',
            taken_at date NULL DEFAULT NULL,
            note text NULL DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY customer_id (customer_id),
            KEY taken_at (taken_at)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_client_formulas (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            customer_id bigint(20) unsigned NOT NULL,
            staff_id bigint(20) unsigned NULL DEFAULT NULL,
            title varchar(191) NOT NULL DEFAULT '',
            formula text NULL DEFAULT NULL,
            brand varchar(191) NOT NULL DEFAULT '',
            service varchar(191) NOT NULL DEFAULT '',
            used_at date NULL DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY customer_id (customer_id),
            KEY used_at (used_at)
        ) {charset}",
    ],

    'down' => [
        "DROP TABLE IF EXISTS {prefix}instinctor_client_photos",
        "DROP TABLE IF EXISTS {prefix}instinctor_client_formulas",
    ],
];
