-- 创建短信发送日志表
CREATE TABLE T_sms_send_log (
    F_ID INT IDENTITY(1,1) PRIMARY KEY,
    F_phone VARCHAR(20) NOT NULL,
    F_code VARCHAR(10) NOT NULL,
    F_success BIT NOT NULL DEFAULT 0,
    F_create_time DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE INDEX IX_sms_send_log_phone ON T_sms_send_log(F_phone);
CREATE INDEX IX_sms_send_log_time ON T_sms_send_log(F_create_time);

-- 创建第三方点检任务表
CREATE TABLE T_thirdparty_inspection_tasks (
    F_ID INT IDENTITY(1,1) PRIMARY KEY,
    F_task_no VARCHAR(50) NOT NULL UNIQUE,
    F_user_id INT NOT NULL,
    F_device_name VARCHAR(200) NOT NULL,
    F_spec_model VARCHAR(100),
    F_operator VARCHAR(50),
    F_date DATE NOT NULL,
    F_status INT NOT NULL DEFAULT 0,
    F_run_time DECIMAL(10,2),
    F_remark TEXT,
    F_create_time DATETIME NOT NULL DEFAULT GETDATE(),
    F_complete_time DATETIME,
    CONSTRAINT FK_tasks_user FOREIGN KEY (F_user_id) REFERENCES T_thirdparty_users(F_ID)
);

-- 创建第三方点检记录表
CREATE TABLE T_thirdparty_inspection_records (
    F_ID INT IDENTITY(1,1) PRIMARY KEY,
    F_task_id INT NOT NULL,
    F_item_name VARCHAR(200) NOT NULL,
    F_result VARCHAR(20) NOT NULL,
    F_note TEXT,
    F_create_time DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT FK_records_task FOREIGN KEY (F_task_id) REFERENCES T_thirdparty_inspection_tasks(F_ID)
);
CREATE INDEX IX_thirdparty_tasks_user ON T_thirdparty_inspection_tasks(F_user_id);
CREATE INDEX IX_thirdparty_tasks_status ON T_thirdparty_inspection_tasks(F_status);
CREATE INDEX IX_thirdparty_records_task ON T_thirdparty_inspection_records(F_task_id);

INSERT INTO T_thirdparty_inspection_tasks (F_task_no, F_user_id, F_device_name, F_spec_model, F_operator, F_date, F_status)
VALUES 
('RI20260604284963', 1, '高空作业车 (36#) 日点检', 'GTBZ26', '袁朝阳', '2026-06-04', 0),
('RI20260604265862', 1, '高空作业车 (36#) 日点检', 'GTBZ26', '王士水', '2026-06-03', 1),
('RI20260604272742', 2, '高空作业车 (37#) 日点检', 'GTBZ30', '姜国辉(涂装车间)', '2026-06-04', 0),
('RI2026060204027', 2, '高空作业车 (37#) 日点检', 'GTBZ30', '姜国辉(涂装车间)', '2026-06-02', 1);

UPDATE T_thirdparty_inspection_tasks 
SET F_complete_time = '2026-06-03 09:07:34', F_run_time = 8.5 
WHERE F_task_no = 'RI20260604265862';

UPDATE T_thirdparty_inspection_tasks 
SET F_complete_time = '2026-06-02 07:45:36', F_run_time = 6.2 
WHERE F_task_no = 'RI2026060204027';
GO