-- View joining case_hours with case titles and user initials
CREATE OR REPLACE VIEW case_hour_summary AS
SELECT h.*, c.title AS case_name,
       CONCAT(LEFT(u.full_name,1), LEFT(split_part(u.full_name,' ',2),1)) AS lawyer_initials
FROM case_hours h
LEFT JOIN cases c ON h.case_id=c.id
LEFT JOIN users u ON h.user_id=u.id;

-- Register view for sync
INSERT INTO tenant_tables (tenant_id, user_id, table_name, definition)
SELECT NULL, NULL, 'case_hour_summary', '{}'
WHERE NOT EXISTS (SELECT 1 FROM tenant_tables WHERE table_name = 'case_hour_summary');

-- Default view for summarizing case hours
INSERT INTO table_views (table_name, name, columns, sort_by, sort_dir)
SELECT 'case_hour_summary', 'Default',
       '["case_name","lawyer_initials","hours","rate",{"field":"cost","formula":"hours * rate"}]',
       'updatedAt', 'DESC'
WHERE NOT EXISTS (
    SELECT 1 FROM table_views
    WHERE table_name = 'case_hour_summary' AND name = 'Default'
);
