-- Slice 8Z-y · Credit Notes for issued invoices
--
-- When a customer disputes / returns / gets over-charged after an invoice has been issued
-- (and possibly VAT-fiscalised), we can't just delete the invoice — EFD/VFMS + accounting
-- requires a reversing document with reference to the original.
--
-- Adds:
--  1) credit_notes header
--  2) credit_note_lines detail (link to sales_items where applicable)
--  3) Perms for Accountant + Admin

CREATE TABLE IF NOT EXISTS credit_notes (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cn_no VARCHAR(30) NOT NULL,
    sale_id INT UNSIGNED NOT NULL COMMENT 'Original invoice being credited',
    project_id INT UNSIGNED NULL,
    customer_id INT UNSIGNED NULL,
    customer_name VARCHAR(200) NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
    reason ENUM('RETURN','DISPUTE','OVERCHARGE','CANCELLATION','ADJUSTMENT','OTHER') NOT NULL DEFAULT 'RETURN',
    subtotal DECIMAL(20,2) NOT NULL DEFAULT 0,
    vat_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    total DECIMAL(20,2) NOT NULL DEFAULT 0,
    status ENUM('DRAFT','ISSUED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    original_efd_receipt_no VARCHAR(50) NULL COMMENT 'Reversal EFD requirement — link the CN to original fiscal receipt',
    efd_receipt_no VARCHAR(50) NULL COMMENT 'Own EFD ref when the CN itself is fiscalised',
    efd_date DATE NULL,
    tra_verification_code VARCHAR(60) NULL,
    narrative TEXT NULL,
    attachment_path VARCHAR(500) NULL COMMENT 'Signed CN scan / return goods photo',
    attachment_original_name VARCHAR(300) NULL,
    attachment_mime VARCHAR(120) NULL,
    journal_entry_id INT UNSIGNED NULL,
    drafted_by INT UNSIGNED NULL,
    drafted_by_name VARCHAR(160) NULL,
    drafted_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    issued_by INT UNSIGNED NULL,
    issued_by_name VARCHAR(160) NULL,
    issued_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_sale (sale_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_note_lines (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    credit_note_id INT UNSIGNED NOT NULL,
    line_no SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    sales_item_id INT UNSIGNED NULL,
    description VARCHAR(500) NOT NULL,
    qty DECIMAL(14,4) NOT NULL DEFAULT 0,
    unit VARCHAR(20) NULL,
    unit_price DECIMAL(20,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(20,2) NOT NULL DEFAULT 0,
    KEY idx_cn (credit_note_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Perms
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'CreditNotes', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac
            UNION ALL SELECT 'draft'
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'saveDraftAj'
            UNION ALL SELECT 'issueAj'
            UNION ALL SELECT 'cancelAj') actions
WHERE g.user_group_name IN ('Accountant','Finance Manager','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'CreditNotes' AND p.action_allowed = actions.ac);

-- Read-only for PM/PC/Exec so they can see credited invoices
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'CreditNotes', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view') actions
WHERE g.user_group_name IN ('Project Manager','Projects Coordinator','Executive')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'CreditNotes' AND p.action_allowed = actions.ac);
