Codex
35 lines
1.9 KiB
SQL
35 lines
1.9 KiB
SQL
CREATE TABLE IF NOT EXISTS qipai_cleaning_task_members (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id BIGINT UNSIGNED NOT NULL,
|
|
task_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
member_role VARCHAR(16) NOT NULL DEFAULT 'ASSIST',
|
|
reward_cents INT UNSIGNED NOT NULL DEFAULT 0,
|
|
joined_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
removed_at DATETIME(3) NULL,
|
|
settled_at DATETIME(3) NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
CONSTRAINT fk_qipai_cleaning_task_members_tenant FOREIGN KEY (tenant_id)
|
|
REFERENCES qipai_tenants(id),
|
|
CONSTRAINT fk_qipai_cleaning_task_members_task FOREIGN KEY (task_id)
|
|
REFERENCES qipai_cleaning_tasks(id),
|
|
CONSTRAINT fk_qipai_cleaning_task_members_user FOREIGN KEY (user_id)
|
|
REFERENCES qipai_users(id),
|
|
UNIQUE KEY uq_qipai_cleaning_task_member (tenant_id, task_id, user_id),
|
|
KEY idx_qipai_cleaning_task_member_user (tenant_id, user_id, removed_at, settled_at),
|
|
KEY idx_qipai_cleaning_task_member_task (tenant_id, task_id, removed_at),
|
|
CONSTRAINT chk_qipai_cleaning_task_member_role CHECK (member_role IN ('LEAD', 'ASSIST'))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE qipai_cleaning_settlement_items
|
|
DROP INDEX uq_qipai_cleaning_settlement_item_task,
|
|
ADD COLUMN cleaner_user_id BIGINT UNSIGNED NOT NULL AFTER task_id,
|
|
ADD CONSTRAINT fk_qipai_cleaning_settlement_items_cleaner FOREIGN KEY (cleaner_user_id)
|
|
REFERENCES qipai_users(id),
|
|
ADD UNIQUE KEY uq_qipai_cleaning_settlement_item_task_user (tenant_id, task_id, cleaner_user_id),
|
|
ADD KEY idx_qipai_cleaning_settlement_items_cleaner (tenant_id, cleaner_user_id);
|
|
|
|
INSERT IGNORE INTO qipai_schema_migrations (version, name)
|
|
VALUES ('2026062627', 'm08b_cleaning_collaboration');
|