-- Run once on the main application database (company table).
-- Adds letterhead / bank fields and seeds hardcoded invoice text for Nuricon companies.

ALTER TABLE `company`
    ADD COLUMN IF NOT EXISTS `fax` VARCHAR(100) NULL AFTER `contact_no`,
    ADD COLUMN IF NOT EXISTS `email` VARCHAR(150) NULL AFTER `fax`,
    ADD COLUMN IF NOT EXISTS `city` VARCHAR(100) NULL AFTER `address`,
    ADD COLUMN IF NOT EXISTS `ntn` VARCHAR(50) NULL,
    ADD COLUMN IF NOT EXISTS `strn` VARCHAR(50) NULL,
    ADD COLUMN IF NOT EXISTS `website` VARCHAR(150) NULL,
    ADD COLUMN IF NOT EXISTS `office_label` VARCHAR(80) NULL COMMENT 'Primary office label e.g. Islamabad Office',
    ADD COLUMN IF NOT EXISTS `secondary_office_label` VARCHAR(80) NULL COMMENT 'Second office label e.g. Head Office',
    ADD COLUMN IF NOT EXISTS `secondary_address` TEXT NULL,
    ADD COLUMN IF NOT EXISTS `secondary_contact_no` VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS `secondary_fax` VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS `secondary_email` VARCHAR(150) NULL,
    ADD COLUMN IF NOT EXISTS `footer_text` TEXT NULL,
    ADD COLUMN IF NOT EXISTS `invoice_brand_color` VARCHAR(20) NULL DEFAULT '#003399',
    ADD COLUMN IF NOT EXISTS `default_bank_id` INT UNSIGNED NULL,
    ADD COLUMN IF NOT EXISTS `secondary_bank_id` INT UNSIGNED NULL;

-- MySQL 5.7 / MariaDB without IF NOT EXISTS on ADD COLUMN: run each line separately and ignore duplicate-column errors.

-- Company 9: NESL (Nuricon Energy Services Private Limited)
UPDATE `company` SET
    `name` = 'NURICON ENERGY SERVICES PRIVATE LIMITED',
    `address` = 'Plot 33, Street No. 1, Sector I-10/3, Islamabad - 44000, Pakistan.',
    `contact_no` = '+92 51 410 2001-4 Lines',
    `fax` = '+92 51 444 3749',
    `email` = 'khalid.khan@nuricon.com',
    `office_label` = 'ISLAMABAD OFFICE',
    `secondary_office_label` = 'HEAD OFFICE',
    `secondary_address` = '4th Floor, Mubarak Manzil, 39, Garden Road, Karachi - 74400, Pakistan.',
    `secondary_contact_no` = '+92 21 3273 5051-4 Lines',
    `secondary_fax` = '+92 21 3273 4162',
    `secondary_email` = 'ameen.hussain@nuricon.com',
    `invoice_brand_color` = '#003399'
WHERE `id` = 9;

-- Company 10: NPPL
UPDATE `company` SET
    `name` = 'NURICON PETROSERVICES PVT. LTD.',
    `address` = 'Plot No. 39, Street No. 1, Sector I-10/3, Islamabad - 44000, PAKISTAN.',
    `contact_no` = '051-4102001-4 Lines',
    `fax` = '051-4443749',
    `email` = 'islamabad@nuricon.com',
    `invoice_brand_color` = '#003399'
WHERE `id` = 10;

-- Company 11: NWSL
UPDATE `company` SET
    `name` = 'NURICON WIRELINE SERVICES (PVT) LIMITED',
    `address` = 'Plot 39, Street No. 1, Sector I-10/3, Islamabad - 44000, Pakistan.',
    `contact_no` = '(051) 4102001-4 Lines',
    `fax` = '(051) 4443749',
    `email` = 'khalid.khan@nuricon.com',
    `office_label` = 'Islamabad Office',
    `secondary_office_label` = 'Head Office',
    `secondary_address` = '4th Floor, Mubarak Manzil, 39, Garden Road, Karachi - 74400, Pakistan.',
    `secondary_contact_no` = '(21) 32735051-4',
    `secondary_fax` = '(21) 32734162',
    `secondary_email` = 'ameen.hussain@nuricon.com',
    `invoice_brand_color` = '#2e7d32'
WHERE `id` = 11;

-- Company 12: PSPB (PetroServices)
UPDATE `company` SET
    `name` = 'PetroServices GmbH - Pakistan Branch',
    `address` = 'Plot No. 39, Street No. 1, Islamabad, Pakistan',
    `contact_no` = '+92 (51) 4102001-4',
    `fax` = '+92 (51) 4443749',
    `email` = 'khalid.khan@nuricon.com',
    `website` = 'www.petroservices.de',
    `secondary_address` = '4th Floor, Mubarak Manzil, 39, Garden Road, Karachi - 74400',
    `secondary_contact_no` = '+92 21 32735051-4',
    `secondary_fax` = '+92 21 2734162',
    `footer_text` = 'PetroServices GmbH - Magdeburg (Head Office)\nBahnhofstrasse 35, 39104 Magdeburg - Germany | Tel: +49 391 5353 734 | Fax: +49 391 5353 794\nEmail: info@petroservices.de | Website: www.petroservices.de',
    `invoice_brand_color` = '#cc3300'
WHERE `id` = 12;

-- Company 13: Siprion
UPDATE `company` SET
    `name` = 'SIPRION PETROTOOLS (PVT.) LTD.',
    `address` = '4th Floor, Mubarak Manzil, 33, Garden Road, KARACHI - 74400',
    `contact_no` = '(21) 32735051 (4 Lines)',
    `fax` = '(21) 32734162',
    `email` = 'karachi@nuricon.com',
    `invoice_brand_color` = '#000000'
WHERE `id` = 13;

-- ---------------------------------------------------------------------------
-- Run on mysql2 (bank_detail). Adjust IDs to match your environment.
-- ---------------------------------------------------------------------------
-- INSERT INTO `bank_detail` (`bank_name`,`account_title`,`account_no`,`branch_code`,`iban_no`,`swift_code`,`bank_address`,`status`) VALUES
-- ('Habib Metropolitan Bank','SIPRION PETROTOOLS (PVT.) LTD.','0101027140647636','1','PK87 MPBL 0101 0271 4064 7636','MPBLPKKA','Karachi, Pakistan',1),
-- ('Faysal Bank Limited','PetroServices GmbH Pakistan Branch','0332000325667004','','','FAYSPKKA','I-10 Markaz Branch, Islamabad-Pakistan',1),
-- ('Faysal Bank Limited','PetroServices GmbH Pakistan Branch (USD)','0332100325667012','','','FAYSPKKA','I-10 Markaz Branch, Islamabad-Pakistan',1);

-- Link banks on main DB after bank_detail rows exist:
-- UPDATE `company` SET `default_bank_id` = 1 WHERE `id` IN (9,10,11,13);
-- UPDATE `company` SET `default_bank_id` = 2, `secondary_bank_id` = 3 WHERE `id` = 12;

-- Siprion STRN/NTN example (set real values in company master):
-- UPDATE `company` SET `ntn` = 'YOUR_NTN', `strn` = 'YOUR_STRN' WHERE `id` = 13;
