-- Petstore Phase 0: split customer / creator / service staff / report author identity.
-- Target: MySQL. One-time migration; back up the database before execution.
-- Existing legacy columns remain for compatibility and are not dropped here.

ALTER TABLE t_appointment
    ADD COLUMN customer_user_id BIGINT NULL COMMENT '被服务客户账号',
    ADD COLUMN created_by_user_id BIGINT NULL COMMENT '预约创建账号';

CREATE INDEX idx_appt_customer_status_time
    ON t_appointment (customer_user_id, status, appointment_time);

ALTER TABLE t_report
    ADD COLUMN customer_user_id BIGINT NULL COMMENT '报告归属客户账号',
    ADD COLUMN author_staff_id BIGINT NULL COMMENT '报告提交门店账号';

CREATE INDEX idx_report_customer_time
    ON t_report (customer_user_id, create_time);

CREATE INDEX idx_report_author_time
    ON t_report (author_staff_id, create_time);

-- Appointment backfill priority:
-- 1. pet.owner_user_id (strongest customer ownership evidence)
-- 2. legacy user_id only when that account is a customer
-- Legacy staff-created rows without pet ownership remain NULL for manual correction.
UPDATE t_appointment a
LEFT JOIN t_pet p
    ON p.id = a.pet_id AND p.deleted = 0
LEFT JOIN t_user legacy_user
    ON legacy_user.id = a.user_id AND legacy_user.deleted = 0
SET a.customer_user_id = COALESCE(
        a.customer_user_id,
        p.owner_user_id,
        CASE WHEN legacy_user.role = 'customer' THEN a.user_id ELSE NULL END
    ),
    a.created_by_user_id = COALESCE(a.created_by_user_id, a.user_id)
WHERE a.customer_user_id IS NULL OR a.created_by_user_id IS NULL;

-- Report user_id historically means report author/service staff.
-- Customer ownership must come from the linked appointment, never from report.user_id.
UPDATE t_report r
LEFT JOIN t_appointment a
    ON a.id = r.appointment_id AND a.deleted = 0
SET r.customer_user_id = COALESCE(r.customer_user_id, a.customer_user_id),
    r.author_staff_id = COALESCE(r.author_staff_id, r.user_id)
WHERE r.customer_user_id IS NULL OR r.author_staff_id IS NULL;

-- Verification queries. unresolved_assisted_appointments must be reviewed manually.
SELECT COUNT(*) AS unresolved_assisted_appointments
FROM t_appointment
WHERE deleted = 0 AND customer_user_id IS NULL;

SELECT COUNT(*) AS reports_without_customer
FROM t_report
WHERE deleted = 0 AND customer_user_id IS NULL;

SELECT COUNT(*) AS reports_without_author
FROM t_report
WHERE deleted = 0 AND author_staff_id IS NULL;