-- Migration 1007: Invoice Error Finder module fixes and permissions

-- Add stable Simply CRM source record id to issues so detection survives import-run re-imports
ALTER TABLE invoice_error_finder_issues
    ADD COLUMN IF NOT EXISTS simply_source_record_id VARCHAR(80);

CREATE INDEX IF NOT EXISTS idx_ief_issues_simply_source
    ON invoice_error_finder_issues(simply_source_record_id);

-- Module permissions
INSERT INTO permissions (code, description, category) VALUES
('invoice_error_finder.view', 'View invoice error finder dashboard and issues', 'invoice_error_finder'),
('invoice_error_finder.run_import', 'Trigger invoice/error data imports', 'invoice_error_finder'),
('invoice_error_finder.analyze', 'Run invoice error detection analysis', 'invoice_error_finder'),
('invoice_error_finder.update_status', 'Update issue status and assignee', 'invoice_error_finder'),
('invoice_error_finder.create_sag', 'Create/link sag from invoice error issue', 'invoice_error_finder'),
('invoice_error_finder.create_ordre_draft', 'Create ordre draft from invoice error issue', 'invoice_error_finder'),
('invoice_error_finder.ignore', 'Ignore invoice error issues', 'invoice_error_finder'),
('invoice_error_finder.admin', 'Administer invoice error finder settings', 'invoice_error_finder')
ON CONFLICT (code) DO NOTHING;

-- Assign permissions to groups
INSERT INTO group_permissions (group_id, permission_id)
SELECT g.id, p.id
FROM groups g
CROSS JOIN permissions p
WHERE g.name = 'Administrators'
  AND p.category = 'invoice_error_finder'
ON CONFLICT DO NOTHING;

INSERT INTO group_permissions (group_id, permission_id)
SELECT g.id, p.id
FROM groups g
CROSS JOIN permissions p
WHERE g.name = 'Managers'
  AND p.category = 'invoice_error_finder'
ON CONFLICT DO NOTHING;

INSERT INTO group_permissions (group_id, permission_id)
SELECT g.id, p.id
FROM groups g
CROSS JOIN permissions p
WHERE g.name = 'Technicians'
  AND p.code IN (
      'invoice_error_finder.view',
      'invoice_error_finder.update_status',
      'invoice_error_finder.create_sag',
      'invoice_error_finder.create_ordre_draft'
  )
ON CONFLICT DO NOTHING;

INSERT INTO group_permissions (group_id, permission_id)
SELECT g.id, p.id
FROM groups g
CROSS JOIN permissions p
WHERE g.name = 'Viewers'
  AND p.code = 'invoice_error_finder.view'
ON CONFLICT DO NOTHING;