-- IWZ Billing multi-branch
-- Installazione manuale alternativa alla creazione automatica del modulo.
-- Eseguire sul database CIWZ dopo avere effettuato un backup.
-- Prefisso previsto: iwz_


CREATE TABLE IF NOT EXISTS `iwz_invoice_customer_profiles` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `organization_id` int UNSIGNED NOT NULL,
    `billing_name` varchar(255) DEFAULT NULL,
    `billing_address` varchar(255) DEFAULT NULL,
    `postal_code` varchar(20) DEFAULT NULL,
    `city` varchar(100) DEFAULT NULL,
    `province` varchar(100) DEFAULT NULL,
    `country` varchar(100) DEFAULT NULL,
    `country_code` char(2) DEFAULT NULL,
    `tax_code` varchar(50) DEFAULT NULL,
    `vat_number` varchar(50) DEFAULT NULL,
    `pec` varchar(255) DEFAULT NULL,
    `sdi_code` varchar(20) DEFAULT NULL,
    `billing_email` varchar(255) DEFAULT NULL,
    `phone` varchar(80) DEFAULT NULL,
    `contact_name` varchar(255) DEFAULT NULL,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`), UNIQUE KEY `uk_org` (`organization_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iwz_branch_invoice_items` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_id` int UNSIGNED NOT NULL,
    `line_no` int UNSIGNED NOT NULL,
    `item_code` varchar(100) DEFAULT NULL,
    `description` text NOT NULL,
    `quantity` decimal(14,4) NOT NULL DEFAULT 1,
    `unit` varchar(30) DEFAULT NULL,
    `unit_price` decimal(14,4) NOT NULL DEFAULT 0,
    `discount_pct` decimal(7,4) NOT NULL DEFAULT 0,
    `discount_amount` decimal(14,2) NOT NULL DEFAULT 0,
    `vat_rate` decimal(7,2) NOT NULL DEFAULT 0,
    `nature_code` varchar(10) DEFAULT NULL,
    `legal_reference` varchar(255) DEFAULT NULL,
    `line_total` decimal(14,2) NOT NULL DEFAULT 0,
    `taxable_total` decimal(14,2) NOT NULL DEFAULT 0,
    `vat_amount` decimal(14,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`), KEY `idx_invoice` (`invoice_id`,`line_no`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iwz_branch_invoice_payments` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_id` int UNSIGNED NOT NULL,
    `payment_date` date NOT NULL,
    `paid_amount` decimal(14,2) NOT NULL,
    `currency` char(3) NOT NULL DEFAULT 'EUR',
    `method` varchar(80) DEFAULT NULL,
    `transaction_id` varchar(255) DEFAULT NULL,
    `status` varchar(30) NOT NULL DEFAULT 'confirmed',
    `notes` text DEFAULT NULL,
    `proof_path` varchar(500) DEFAULT NULL,
    `created_by` int UNSIGNED DEFAULT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`), KEY `idx_invoice_payment` (`invoice_id`,`payment_date`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iwz_branch_invoice_events` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_id` int UNSIGNED NOT NULL,
    `event_type` varchar(50) NOT NULL,
    `old_status` varchar(40) DEFAULT NULL,
    `new_status` varchar(40) DEFAULT NULL,
    `note` text DEFAULT NULL,
    `payload_json` longtext DEFAULT NULL,
    `created_by` int UNSIGNED DEFAULT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`), KEY `idx_invoice_event` (`invoice_id`,`created_at`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- ESTENSIONE V2: catalogo servizi, note fiscali, QR pubblico,
-- riapertura Pro-forma, clonazione, pagamenti allegati.
-- =========================================================

CREATE TABLE IF NOT EXISTS `iwz_invoice_service_catalog` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` int UNSIGNED NOT NULL DEFAULT 0,
    `service_code` varchar(80) NOT NULL,
    `description` text NOT NULL,
    `unit` varchar(30) NOT NULL DEFAULT 'servizio',
    `unit_price` decimal(14,2) NOT NULL DEFAULT 0,
    `currency` char(3) NOT NULL DEFAULT 'EUR',
    `vat_rate` decimal(7,2) NOT NULL DEFAULT 22,
    `nature_code` varchar(10) DEFAULT NULL,
    `legal_reference` varchar(255) DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `sort_order` int NOT NULL DEFAULT 0,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_service_scope_code` (`branch_id`,`service_code`),
    KEY `idx_service_active` (`active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `iwz_invoice_fiscal_notes` (
    `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` int UNSIGNED NOT NULL DEFAULT 0,
    `note_code` varchar(80) NOT NULL,
    `title` varchar(190) NOT NULL,
    `note_text` text NOT NULL,
    `nature_code` varchar(10) DEFAULT NULL,
    `legal_reference` varchar(255) DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `sort_order` int NOT NULL DEFAULT 0,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_fiscal_scope_code` (`branch_id`,`note_code`),
    KEY `idx_fiscal_active` (`active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Aggiornamento sicuro delle installazioni precedenti.
DROP PROCEDURE IF EXISTS `iwz_billing_add_column_if_missing`;
DELIMITER //
CREATE PROCEDURE `iwz_billing_add_column_if_missing`(
    IN p_table varchar(128),
    IN p_column varchar(128),
    IN p_definition text
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @sql_statement = CONCAT(
            'ALTER TABLE `', REPLACE(p_table,'`','``'),
            '` ADD COLUMN `', REPLACE(p_column,'`','``'),
            '` ', p_definition
        );
        PREPARE iwz_stmt FROM @sql_statement;
        EXECUTE iwz_stmt;
        DEALLOCATE PREPARE iwz_stmt;
    END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS `iwz_billing_add_column_if_missing`;

DROP PROCEDURE IF EXISTS `iwz_billing_add_index_if_missing`;
DELIMITER //
CREATE PROCEDURE `iwz_billing_add_index_if_missing`(
    IN p_table varchar(128),
    IN p_index varchar(128),
    IN p_columns varchar(500),
    IN p_unique tinyint
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND INDEX_NAME = p_index
    ) THEN
        SET @sql_statement = CONCAT(
            'ALTER TABLE `', REPLACE(p_table,'`','``'),
            '` ADD ', IF(p_unique=1,'UNIQUE ', ''),
            'INDEX `', REPLACE(p_index,'`','``'), '` (', p_columns, ')'
        );
        PREPARE iwz_stmt FROM @sql_statement;
        EXECUTE iwz_stmt;
        DEALLOCATE PREPARE iwz_stmt;
    END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS `iwz_billing_add_index_if_missing`;

-- Catalogo iniziale di prestazioni tipiche di un Organismo di certificazione.
-- INSERT IGNORE preserva le eventuali personalizzazioni già presenti.
INSERT IGNORE INTO `iwz_invoice_service_catalog`
(`branch_id`,`service_code`,`description`,`unit`,`unit_price`,`currency`,`vat_rate`,`nature_code`,`legal_reference`,`active`,`sort_order`) VALUES
(0,'CERT-S1','Audit iniziale di certificazione – Stage 1: esame documentale, verifica del livello di preparazione e pianificazione dello Stage 2','audit',900.00,'EUR',22.00,NULL,NULL,1,10),
(0,'CERT-S2','Audit iniziale di certificazione – Stage 2: verifica di conformità e valutazione dell’efficace attuazione del sistema di gestione','audit',1800.00,'EUR',22.00,NULL,NULL,1,20),
(0,'SURV-1','Audit di sorveglianza – prima annualità del ciclo di certificazione','audit',1200.00,'EUR',22.00,NULL,NULL,1,30),
(0,'SURV-2','Audit di sorveglianza – seconda annualità del ciclo di certificazione','audit',1200.00,'EUR',22.00,NULL,NULL,1,40),
(0,'RECERT','Audit di rinnovo / ricertificazione del sistema di gestione','audit',1800.00,'EUR',22.00,NULL,NULL,1,50),
(0,'CERT-ISS','Emissione, registrazione e gestione del certificato','certificato',350.00,'EUR',22.00,NULL,NULL,1,60),
(0,'TECH-REV','Riesame tecnico indipendente del fascicolo di certificazione','pratica',450.00,'EUR',22.00,NULL,NULL,1,70),
(0,'TRANSFER','Riesame preliminare per trasferimento della certificazione','pratica',500.00,'EUR',22.00,NULL,NULL,1,80),
(0,'SCOPE-EXT','Audit per estensione, riduzione o modifica del campo di applicazione','audit',750.00,'EUR',22.00,NULL,NULL,1,90),
(0,'SPECIAL-AUDIT','Audit speciale, supplementare o con breve preavviso','giornata',800.00,'EUR',22.00,NULL,NULL,1,100),
(0,'AUDITOR-DAY','Giornata professionale Lead Auditor / Auditor','giornata',650.00,'EUR',22.00,NULL,NULL,1,110),
(0,'REMOTE-AUDIT','Giornata di audit da remoto mediante strumenti ICT','giornata',600.00,'EUR',22.00,NULL,NULL,1,120),
(0,'WITNESS-AUDIT','Witness audit / valutazione in accompagnamento','giornata',900.00,'EUR',22.00,NULL,NULL,1,130),
(0,'NC-REVIEW','Riesame delle non conformità e delle azioni correttive','pratica',250.00,'EUR',22.00,NULL,NULL,1,140),
(0,'TRAVEL-DAY','Giornata di trasferta del gruppo di audit','giornata',350.00,'EUR',22.00,NULL,NULL,1,150),
(0,'EXPENSES','Rimborso spese documentate di viaggio, vitto e alloggio','rimborso',0.00,'EUR',22.00,NULL,NULL,1,160),
(0,'DUP-CERT','Duplicato, ristampa o aggiornamento formale del certificato','certificato',150.00,'EUR',22.00,NULL,NULL,1,170);

-- Note fiscali iniziali. La loro applicabilità deve essere verificata
-- dal responsabile amministrativo o dal consulente fiscale prima dell’uso.
INSERT IGNORE INTO `iwz_invoice_fiscal_notes`
(`branch_id`,`note_code`,`title`,`note_text`,`nature_code`,`legal_reference`,`active`,`sort_order`) VALUES
(0,'IVA22','IVA ordinaria 22%','Operazione imponibile soggetta ad IVA con aliquota ordinaria del 22%.',NULL,'D.P.R. 26 ottobre 1972, n. 633',1,10),
(0,'N1-ART15','Esclusa IVA – art. 15','Somme escluse dalla base imponibile ai sensi dell’art. 15 del D.P.R. 633/1972.','N1','Art. 15, D.P.R. 633/1972',1,20),
(0,'N2.1-ESTERO','Non soggetta – territorialità','Operazione non soggetta ad IVA in Italia per carenza del requisito territoriale.','N2.1','Artt. 7–7-septies, D.P.R. 633/1972',1,30),
(0,'N2.2-ALTRO','Non soggetta – altri casi','Operazione non soggetta ad IVA per disposizione normativa diversa dalle regole di territorialità.','N2.2','D.P.R. 633/1972',1,40),
(0,'N3.2-INTRA','Non imponibile – cessione intracomunitaria','Operazione non imponibile relativa a cessione intracomunitaria, ove ne ricorrano i presupposti.','N3.2','Art. 41, D.L. 331/1993',1,50),
(0,'N3.4-SERVIZI','Non imponibile – servizi internazionali','Operazione non imponibile relativa a servizi internazionali o connessi agli scambi internazionali.','N3.4','Art. 9, D.P.R. 633/1972',1,60),
(0,'N4-ESENTE','Operazione esente','Operazione esente da IVA nei limiti e alle condizioni previste dalla norma richiamata.','N4','Art. 10, D.P.R. 633/1972',1,70),
(0,'N6.9-RC','Inversione contabile','Operazione soggetta a inversione contabile; imposta assolta dal committente secondo la disciplina applicabile.','N6.9','Art. 17, D.P.R. 633/1972 / art. 196 Direttiva 2006/112/CE',1,80),
(0,'SPLIT','Scissione dei pagamenti','IVA assolta dal cessionario/committente mediante scissione dei pagamenti, ove applicabile.',NULL,'Art. 17-ter, D.P.R. 633/1972',1,90),
(0,'BOLLO','Imposta di bollo virtuale','Imposta di bollo assolta in modalità virtuale secondo la normativa applicabile.',NULL,'Art. 6, D.M. 17 giugno 2014',1,100),
(0,'COURTESY','Copia di cortesia','Il presente PDF è una copia leggibile. Per le operazioni soggette alla fatturazione elettronica italiana, l’originale fiscale è il file XML trasmesso tramite Sistema di Interscambio.',NULL,'D.Lgs. 127/2015 e normativa attuativa',1,110);
