ALTER TABLE tblinvoicepaymentrecords
ADD category_id BIGINT(20) DEFAULT NULL COMMENT 'Payment Category Id' AFTER bulk_client_invoice_payments_id;

CREATE INDEX category_id ON tblinvoicepaymentrecords (category_id);

CREATE TABLE IF NOT EXISTS tblpayments_categories_static (
    id          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    name        varchar(191)        NOT NULL,
    description text DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


CREATE TABLE IF NOT EXISTS tblpayments_categories (
    tenant_id   bigint(20) unsigned NOT NULL DEFAULT 1,
    id          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    name       varchar(191) NOT NULL,
    description text DEFAULT NULL,
    PRIMARY KEY (id),
    KEY tenant_id (tenant_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO tblpayments_categories_static(name, description)

VALUES ('Labor', 'Payments for manual work or services rendered by the contractors or their teams.'),
       ('Materials', 'Costs associated with materials purchased or used for a specific job or project.'),
       ('Equipment Rental', 'Fees for renting equipment necessary to complete a job.'),
       ('Subcontractors', 'Payments made to subcontracted workers or other service providers hired to complete a portion of the work.'),
       ('Travel Expenses', 'Reimbursements or payments for travel-related expenses incurred while reaching or working at a job site.'),
       ('Permits and Licenses', 'Fees associated with obtaining the necessary permits or licenses required to legally perform the work.'),
       ('Insurance', 'Payments for any insurance costs that are directly billed to the client, such as project-specific insurance.'),
       ('Overhead', 'A portion of the general business overhead costs allocated to the project. This might include a fixed rate or percentage covering administrative costs, utilities, etc.'),
       ('Consultation Fees', 'Fees charged for expert advice or consultations provided as part of the project.'),
       ('Emergency or After-Hours Services', 'Additional charges for work performed outside of normal business hours, on weekends, or under emergency conditions.');

insert into tblpayments_categories(tenant_id, name, description)
select b.id, a.name, a.description
from tblpayments_categories_static a
cross join tbltenants b;

ALTER TABLE tblbulk_client_invoice_payments
ADD category_id BIGINT(20) DEFAULT NULL COMMENT 'Payment Category Id' AFTER payment_mode;

CREATE INDEX category_id ON tblbulk_client_invoice_payments (category_id);