-- SQL Script to create customer_po and customer_po_item tables
-- Run this in phpMyAdmin SQL tab

-- Create customer_po table
CREATE TABLE IF NOT EXISTS `customer_po` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_id` INT(11) NOT NULL,
  `plant_id` INT(11) NOT NULL,
  `fiscal_year_id` INT(11) NOT NULL,
  `po_number` VARCHAR(50) NOT NULL,
  `po_date` DATE NOT NULL,
  `customer_id` INT(11) NOT NULL,
  `customer_address_id` INT(11) NULL DEFAULT NULL,
  `cq_id` INT(11) NULL DEFAULT NULL,
  `currency_id` INT(11) NULL DEFAULT NULL,
  `payment_terms` VARCHAR(200) NULL DEFAULT NULL,
  `delivery_terms` VARCHAR(200) NULL DEFAULT NULL,
  `delivery_date` DATE NULL DEFAULT NULL,
  `sub_total` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'RELEASED', 'PARTIALLY_DELIVERED', 'DELIVERED', 'CLOSED') NOT NULL DEFAULT 'DRAFT',
  `remarks` TEXT NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_customer_po_number` (`company_id`, `plant_id`, `fiscal_year_id`, `po_number`),
  KEY `customer_id` (`customer_id`),
  KEY `status` (`status`),
  CONSTRAINT `fk_cpo_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpo_plant` FOREIGN KEY (`plant_id`) REFERENCES `plant` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpo_fiscal_year` FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_year` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpo_customer` FOREIGN KEY (`customer_id`) REFERENCES `business_partner` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpo_customer_address` FOREIGN KEY (`customer_address_id`) REFERENCES `bp_address` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_cpo_cq` FOREIGN KEY (`cq_id`) REFERENCES `customer_quotation` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_cpo_currency` FOREIGN KEY (`currency_id`) REFERENCES `currency` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_cpo_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_cpo_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create customer_po_item table
CREATE TABLE IF NOT EXISTS `customer_po_item` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `po_id` INT(11) NOT NULL,
  `item_number` INT(11) NOT NULL,
  `material_id` INT(11) NOT NULL,
  `description` VARCHAR(500) NULL DEFAULT NULL,
  `quantity` DECIMAL(15,3) NOT NULL DEFAULT 0.000,
  `delivered_quantity` DECIMAL(15,3) NOT NULL DEFAULT 0.000,
  `uom_id` INT(11) NOT NULL,
  `unit_price` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `discount_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `hsn_code` VARCHAR(50) NULL DEFAULT NULL,
  `storage_location_id` INT(11) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `material_id` (`material_id`),
  CONSTRAINT `fk_cpoi_po` FOREIGN KEY (`po_id`) REFERENCES `customer_po` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_cpoi_material` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpoi_uom` FOREIGN KEY (`uom_id`) REFERENCES `uom` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_cpoi_storage_location` FOREIGN KEY (`storage_location_id`) REFERENCES `storage_location` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

