-- ============================================================ -- KopierSFlow ERP — 005_purchasing_imports.sql -- Importar QUINTO en phpMyAdmin -- ============================================================ CREATE TABLE vendors ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, vendor_number VARCHAR(30), name VARCHAR(200) NOT NULL, legal_name VARCHAR(200), type ENUM('domestic','international') DEFAULT 'domestic', country CHAR(3) DEFAULT 'USA', address TEXT, city VARCHAR(100), state_province VARCHAR(100), zip VARCHAR(20), phone VARCHAR(30), email VARCHAR(150), contact_name VARCHAR(150), payment_terms TINYINT UNSIGNED DEFAULT 30, currency CHAR(3) DEFAULT 'USD', ein_tax_id VARCHAR(40), is_fda_registered TINYINT(1) DEFAULT 0, fda_registration VARCHAR(30), food_safety_cert VARCHAR(100), is_active TINYINT(1) DEFAULT 1, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchase_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, po_number VARCHAR(30) NOT NULL, vendor_id BIGINT UNSIGNED NOT NULL, order_date DATE NOT NULL, expected_date DATE, status ENUM('draft','sent','acknowledged','partial','received','cancelled','closed') DEFAULT 'draft', warehouse_id BIGINT UNSIGNED, incoterm VARCHAR(10), currency CHAR(3) DEFAULT 'USD', exchange_rate DECIMAL(14,6) DEFAULT 1.0, subtotal DECIMAL(18,4) DEFAULT 0, tax_amount DECIMAL(18,4) DEFAULT 0, total DECIMAL(18,4) DEFAULT 0, notes TEXT, is_import TINYINT(1) DEFAULT 0, import_shipment_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (vendor_id) REFERENCES vendors(id), UNIQUE KEY uq_po_number (company_id, po_number), INDEX idx_status (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE purchase_order_lines ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, purchase_order_id BIGINT UNSIGNED NOT NULL, company_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, description VARCHAR(500), quantity_ordered DECIMAL(14,4) NOT NULL, quantity_received DECIMAL(14,4) DEFAULT 0, unit_cost DECIMAL(14,4) NOT NULL, line_total DECIMAL(18,4) NOT NULL, unit_of_measure VARCHAR(20) DEFAULT 'EA', FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE import_shipments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, shipment_number VARCHAR(60) NOT NULL, vendor_id BIGINT UNSIGNED NOT NULL, country_of_origin CHAR(3), incoterm VARCHAR(10), transport_mode ENUM('sea','air','truck','rail') DEFAULT 'sea', carrier VARCHAR(150), vessel_flight VARCHAR(100), bill_of_lading VARCHAR(100), airway_bill VARCHAR(100), etd DATE, eta DATE, actual_arrival DATE, port_of_loading VARCHAR(100), port_of_entry VARCHAR(100), status ENUM('booked','in_transit','arrived','customs','released','delivered') DEFAULT 'booked', customs_broker VARCHAR(150), fda_prior_notice_id VARCHAR(100), entry_number VARCHAR(30), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (vendor_id) REFERENCES vendors(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE containers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, import_shipment_id BIGINT UNSIGNED NOT NULL, container_number VARCHAR(30) NOT NULL, size ENUM('20ft','40ft','40ft_hc','reefer_20','reefer_40') NOT NULL, seal_number VARCHAR(30), set_temp_f DECIMAL(6,2), cargo_weight_lbs DECIMAL(10,2), status ENUM('booked','loaded','in_transit','discharged','available','returned') DEFAULT 'booked', FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (import_shipment_id) REFERENCES import_shipments(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customs_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, import_shipment_id BIGINT UNSIGNED NOT NULL, doc_type ENUM('commercial_invoice','packing_list','bill_of_lading','airway_bill','certificate_of_origin','phytosanitary','fda_prior_notice','cbp_entry','other') NOT NULL, doc_number VARCHAR(100), issued_date DATE, file_path VARCHAR(500), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (import_shipment_id) REFERENCES import_shipments(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE landed_costs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, import_shipment_id BIGINT UNSIGNED, purchase_order_id BIGINT UNSIGNED, lc_number VARCHAR(30) NOT NULL, status ENUM('draft','posted') DEFAULT 'draft', distribution_method ENUM('by_qty','by_weight','by_volume','by_value') DEFAULT 'by_value', notes TEXT, journal_entry_id BIGINT UNSIGNED, created_by BIGINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE landed_cost_charges ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, landed_cost_id BIGINT UNSIGNED NOT NULL, charge_type ENUM('freight','customs_duty','broker_fee','insurance','port_fee','drayage','inspection','other') NOT NULL, vendor_id BIGINT UNSIGNED, amount DECIMAL(18,4) NOT NULL, currency CHAR(3) DEFAULT 'USD', exchange_rate DECIMAL(14,6) DEFAULT 1.0, amount_usd DECIMAL(18,4) DEFAULT 0, account_id BIGINT UNSIGNED, FOREIGN KEY (landed_cost_id) REFERENCES landed_costs(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE landed_cost_lines ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, landed_cost_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, lot_id BIGINT UNSIGNED, quantity DECIMAL(14,4) NOT NULL, product_value DECIMAL(18,4) NOT NULL, allocated_cost DECIMAL(18,4) DEFAULT 0, FOREIGN KEY (landed_cost_id) REFERENCES landed_costs(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE edi_partners ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, partner_name VARCHAR(200) NOT NULL, isa_id VARCHAR(15), gs_id VARCHAR(15), qualifier VARCHAR(3) DEFAULT 'ZZ', protocol ENUM('as2','sftp','ftps','van') DEFAULT 'sftp', is_active TINYINT(1) DEFAULT 1, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE edi_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, partner_id BIGINT UNSIGNED NOT NULL, transaction_set VARCHAR(10) NOT NULL COMMENT '850|855|856|810|997', direction ENUM('inbound','outbound') NOT NULL, isa_control VARCHAR(20), status ENUM('received','processing','processed','error','acknowledged') DEFAULT 'received', raw_content LONGTEXT, parsed_data JSON, reference_id BIGINT UNSIGNED, error_message TEXT, received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, processed_at TIMESTAMP NULL, FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, FOREIGN KEY (partner_id) REFERENCES edi_partners(id), INDEX idx_transaction (company_id, transaction_set, direction, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;