-- portfoliokg-lakehouse-federation.sql
-- Consolidated batch for the PortfolioKG estate used by the Databricks Lakehouse Federation meshup.
-- Run:  isql <host>:1111 <user> <password> < portfoliokg-lakehouse-federation.sql
--       or paste into Virtuoso Conductor > Database > Interactive SQL.
-- Order: tables, rows, IRI classes, ontology, the RDF View (quad map), grants, URL rewrite rules, memory graph.
-- Every statement below was executed on demo.openlinksw.com as user kidehen; the data is synthetic and illustrative.
-- Part 1 (tables, rows, grants) is standard SQL. Parts 2 and 3 (CREATE IRI CLASS and ALTER QUAD STORAGE) are
-- Virtuoso-specific and need the SPARQL keyword in front when sent through isql.

-- ── Part 1a: tables (first-party rows, then the marketplace-style share) ──
CREATE TABLE PortfolioKG.kidehen.client (client_id INTEGER NOT NULL PRIMARY KEY, client_name VARCHAR(48) NOT NULL, segment VARCHAR(16) NOT NULL);
CREATE TABLE PortfolioKG.kidehen.portfolio (portfolio_id INTEGER NOT NULL PRIMARY KEY, client_id INTEGER NOT NULL, portfolio_name VARCHAR(48) NOT NULL, base_currency VARCHAR(3) NOT NULL, FOREIGN KEY (client_id) REFERENCES PortfolioKG.kidehen.client (client_id));
CREATE TABLE PortfolioKG.kidehen.equity_holdings (holding_id INTEGER NOT NULL PRIMARY KEY, portfolio_id INTEGER NOT NULL, symbol VARCHAR(12) NOT NULL, instrument_type VARCHAR(12) NOT NULL, number_of_shares INTEGER NOT NULL, avg_cost DECIMAL(10,2) NOT NULL, acquired_date DATE NOT NULL, FOREIGN KEY (portfolio_id) REFERENCES PortfolioKG.kidehen.portfolio (portfolio_id));
CREATE TABLE PortfolioKG.kidehen.fs_company (fsym_company_id VARCHAR(12) NOT NULL PRIMARY KEY, company_name VARCHAR(64) NOT NULL, sector VARCHAR(24) NOT NULL);
CREATE TABLE PortfolioKG.kidehen.fs_sym_ticker_region (fsym_id VARCHAR(12) NOT NULL PRIMARY KEY, ticker_region VARCHAR(12) NOT NULL);
CREATE TABLE PortfolioKG.kidehen.fs_ff_sec_map (fsym_id VARCHAR(12) NOT NULL PRIMARY KEY, fsym_company_id VARCHAR(12) NOT NULL, FOREIGN KEY (fsym_id) REFERENCES PortfolioKG.kidehen.fs_sym_ticker_region (fsym_id), FOREIGN KEY (fsym_company_id) REFERENCES PortfolioKG.kidehen.fs_company (fsym_company_id));
CREATE TABLE PortfolioKG.kidehen.fs_fe_sec_map (fsym_id VARCHAR(12) NOT NULL PRIMARY KEY, fsym_company_id VARCHAR(12) NOT NULL, FOREIGN KEY (fsym_id) REFERENCES PortfolioKG.kidehen.fs_sym_ticker_region (fsym_id), FOREIGN KEY (fsym_company_id) REFERENCES PortfolioKG.kidehen.fs_company (fsym_company_id));
CREATE TABLE PortfolioKG.kidehen.fs_ff_basic_af (fsym_id VARCHAR(12) NOT NULL, fiscal_year INTEGER NOT NULL, fiscal_date DATE NOT NULL, ff_sales INTEGER NOT NULL, ff_net_income INTEGER NOT NULL, ff_eps_basic DECIMAL(8,2), ff_debt_st INTEGER NOT NULL, ff_debt_lt INTEGER NOT NULL, ff_com_eq INTEGER NOT NULL, PRIMARY KEY (fsym_id, fiscal_year), FOREIGN KEY (fsym_id) REFERENCES PortfolioKG.kidehen.fs_company (fsym_company_id));
CREATE TABLE PortfolioKG.kidehen.fs_fe_basic_conh_af (fsym_id VARCHAR(12) NOT NULL, fiscal_year INTEGER NOT NULL, fe_item VARCHAR(8) NOT NULL, fe_fp_end DATE NOT NULL, fe_mean DECIMAL(8,2), fe_high DECIMAL(8,2), fe_low DECIMAL(8,2), fe_num_est INTEGER NOT NULL, cons_end_date DATE, PRIMARY KEY (fsym_id, fiscal_year), FOREIGN KEY (fsym_id) REFERENCES PortfolioKG.kidehen.fs_company (fsym_company_id));

-- ── Part 1b: rows ──
INSERT INTO PortfolioKG.kidehen.client VALUES (1, 'Calder Family Trust', 'Trust');
INSERT INTO PortfolioKG.kidehen.client VALUES (2, 'Wren Foundation', 'Foundation');
INSERT INTO PortfolioKG.kidehen.client VALUES (3, 'Okafor Household', 'Individual');
INSERT INTO PortfolioKG.kidehen.client VALUES (4, 'Lindqvist Retirement Fund', 'Pension');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (1, 1, 'Calder Growth', 'USD');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (2, 1, 'Calder Income', 'USD');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (3, 2, 'Wren Endowment', 'USD');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (4, 3, 'Okafor Core', 'USD');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (5, 4, 'Lindqvist Balanced', 'USD');
INSERT INTO PortfolioKG.kidehen.portfolio VALUES (6, 4, 'Lindqvist Reserve', 'USD');
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (1, 1, 'ALDM-US', 'Equity', 1200, 31.4, stringdate('2023-03-14'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (2, 1, 'HBLS-US', 'Equity', 2500, 18.75, stringdate('2022-11-02'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (3, 1, 'DNMP-US', 'Equity', 800, 41.2, stringdate('2024-01-22'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (4, 1, 'KSTA-US', 'Equity', 600, 39.8, stringdate('2023-08-09'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (5, 2, 'BWUT-US', 'Equity', 3000, 19.1, stringdate('2021-06-30'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (6, 2, 'FHVB-US', 'Equity', 2200, 24.65, stringdate('2022-02-17'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (7, 2, 'JRGF-US', 'Equity', 1800, 22.3, stringdate('2023-05-11'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (8, 3, 'ALDM-US', 'Equity', 4000, 28.9, stringdate('2022-09-05'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (9, 3, 'DNMP-US', 'Equity', 2100, 36.75, stringdate('2023-10-19'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (10, 3, 'LKSH-US', 'Equity', 1500, 20.1, stringdate('2024-04-03'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (11, 3, 'GRWE-US', 'Equity', 2600, 21.85, stringdate('2021-12-14'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (12, 4, 'CLWY-US', 'Equity', 900, 24.5, stringdate('2022-07-26'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (13, 4, 'EGLG-US', 'Equity', 700, 27.15, stringdate('2023-02-08'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (14, 4, 'FHVB-US', 'Equity', 1100, 23.9, stringdate('2024-02-29'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (15, 4, 'ZZZX-US', 'Equity', 500, 12.0, stringdate('2020-05-18'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (16, 5, 'BWUT-US', 'Equity', 2400, 18.4, stringdate('2021-03-22'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (17, 5, 'IRVM-US', 'Equity', 1300, 26.6, stringdate('2022-10-31'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (18, 5, 'JRGF-US', 'Equity', 900, 21.75, stringdate('2023-12-12'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (19, 5, 'KSTA-US', 'Equity', 450, 41.05, stringdate('2024-05-20'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (20, 6, 'HBLS-US', 'Equity', 1600, 19.3, stringdate('2023-06-06'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (21, 6, 'EGLG-US', 'Equity', 1000, 26.8, stringdate('2022-04-12'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (22, 6, 'CLWY-US', 'Equity', 1400, 23.1, stringdate('2021-09-27'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (23, 6, 'GRWE-US', 'Equity', 1900, 22.6, stringdate('2023-01-30'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (24, 6, 'LKSH-US', 'Equity', 800, 19.55, stringdate('2024-06-17'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (25, 2, 'IRVM-US', 'Equity', 1000, 25.9, stringdate('2023-07-24'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (26, 3, 'MRDT-US', 'Equity', 700, 15.2, stringdate('2024-08-05'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (27, 3, 'NVTC-US', 'Equity', 300, 33.1, stringdate('2024-09-09'));
INSERT INTO PortfolioKG.kidehen.equity_holdings VALUES (28, 5, 'TRSY-US', 'Bond', 100000, 0.99, stringdate('2024-03-01'));
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1001-E', 'Aldermoor Semiconductors', 'Technology');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1002-E', 'Brightwater Utilities', 'Utilities');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1003-E', 'Calloway Retail Group', 'Consumer');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1004-E', 'Dunmore Pharma', 'Healthcare');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1005-E', 'Eastgate Logistics', 'Industrials');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1006-E', 'Fairhaven Bancorp', 'Financials');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1007-E', 'Granitewood Energy', 'Energy');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1008-E', 'Harborline Software', 'Technology');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1009-E', 'Ironvale Materials', 'Materials');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1010-E', 'Juniper Ridge Foods', 'Consumer');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1011-E', 'Kestrel Aerospace', 'Industrials');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1012-E', 'Larkspur Health Systems', 'Healthcare');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1013-E', 'Meridian Telecom', 'Communication');
INSERT INTO PortfolioKG.kidehen.fs_company VALUES ('C1014-E', 'Novatec Instruments', 'Technology');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1001-R', 'ALDM-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1002-R', 'BWUT-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1003-R', 'CLWY-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1004-R', 'DNMP-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1005-R', 'EGLG-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1006-R', 'FHVB-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1007-R', 'GRWE-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1008-R', 'HBLS-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1009-R', 'IRVM-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1010-R', 'JRGF-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1011-R', 'KSTA-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1012-R', 'LKSH-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1013-R', 'MRDT-US');
INSERT INTO PortfolioKG.kidehen.fs_sym_ticker_region VALUES ('S1014-R', 'NVTC-US');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1001-R', 'C1001-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1002-R', 'C1002-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1003-R', 'C1003-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1004-R', 'C1004-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1005-R', 'C1005-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1006-R', 'C1006-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1007-R', 'C1007-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1008-R', 'C1008-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1009-R', 'C1009-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1010-R', 'C1010-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1011-R', 'C1011-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1012-R', 'C1012-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1013-R', 'C1013-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_sec_map VALUES ('S1014-R', 'C1014-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1001-R', 'C1001-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1002-R', 'C1002-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1003-R', 'C1003-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1004-R', 'C1004-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1005-R', 'C1005-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1006-R', 'C1006-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1007-R', 'C1007-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1008-R', 'C1008-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1009-R', 'C1009-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1010-R', 'C1010-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1011-R', 'C1011-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1012-R', 'C1012-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1013-R', 'C1013-E');
INSERT INTO PortfolioKG.kidehen.fs_fe_sec_map VALUES ('S1014-R', 'C1014-E');
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1001-E', 2023, stringdate('2023-12-31'), 3600, 500, 1.22, 100, 700, 2600);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1001-E', 2024, stringdate('2024-12-31'), 4200, 640, 1.56, 150, 700, 2900);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1001-E', 2025, stringdate('2025-12-31'), 4900, 820, 2.00, 200, 700, 3100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1002-E', 2023, stringdate('2023-12-31'), 6000, 500, 1.67, 900, 7000, 3300);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1002-E', 2024, stringdate('2024-12-31'), 6100, 520, 1.73, 900, 7400, 3300);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1002-E', 2025, stringdate('2025-12-31'), 6300, 540, 1.80, 1000, 7600, 3300);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1003-E', 2023, stringdate('2023-12-31'), 10100, 360, 2.00, 300, 2200, 2500);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1003-E', 2024, stringdate('2024-12-31'), 9800, 310, 1.72, 300, 2300, 2400);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1003-E', 2025, stringdate('2025-12-31'), 9500, 210, 1.17, 350, 2250, 2400);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1004-E', 2023, stringdate('2023-12-31'), 3000, 640, 2.46, 50, 500, 4800);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1004-E', 2024, stringdate('2024-12-31'), 3300, 700, 2.69, 50, 550, 5000);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1004-E', 2025, stringdate('2025-12-31'), 3700, 790, 3.04, 60, 540, 5200);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1005-E', 2023, stringdate('2023-12-31'), 5000, 360, 2.40, 200, 1800, 2400);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1005-E', 2024, stringdate('2024-12-31'), 5200, 380, 2.53, 200, 1900, 2450);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1005-E', 2025, stringdate('2025-12-31'), 5600, 420, 2.80, 200, 1900, 2500);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1006-E', 2023, stringdate('2023-12-31'), 2300, 490, 2.23, 100, 1200, 4100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1006-E', 2024, stringdate('2024-12-31'), 2400, 520, 2.36, 100, 1250, 4200);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1006-E', 2025, stringdate('2025-12-31'), 2550, 560, 2.55, 100, 1300, 4300);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1007-E', 2023, stringdate('2023-12-31'), 8100, 1100, 3.24, 300, 3000, 4000);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1007-E', 2024, stringdate('2024-12-31'), 7700, 900, 2.65, 300, 3100, 4100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1007-E', 2025, stringdate('2025-12-31'), 6900, 610, 1.79, 300, 3000, 4100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1008-E', 2023, stringdate('2023-12-31'), 1500, 140, 1.17, 20, 130, 1500);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1008-E', 2024, stringdate('2024-12-31'), 1800, 190, 1.58, 20, 130, 1600);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1008-E', 2025, stringdate('2025-12-31'), 2300, 320, 2.67, 20, 130, 1700);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1009-E', 2023, stringdate('2023-12-31'), 3900, 150, 1.67, 300, 2000, 1200);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1009-E', 2024, stringdate('2024-12-31'), 3900, 60, 0.67, 350, 2050, 1150);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1009-E', 2025, stringdate('2025-12-31'), 3800, -40, -0.44, 400, 2000, 1100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1010-E', 2023, stringdate('2023-12-31'), 5200, 390, 1.86, 200, 1700, 2500);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1010-E', 2024, stringdate('2024-12-31'), 5400, 410, 1.95, 200, 1700, 2550);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1010-E', 2025, stringdate('2025-12-31'), 5600, 450, 2.14, 200, 1700, 2600);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1011-E', 2023, stringdate('2023-12-31'), 8200, 640, 2.78, 500, 3500, 2800);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1011-E', 2024, stringdate('2024-12-31'), 8800, 700, 3.04, 500, 3600, 2850);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1011-E', 2025, stringdate('2025-12-31'), 9600, 880, 3.83, 500, 3600, 2900);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1012-E', 2023, stringdate('2023-12-31'), 3900, 310, 1.63, 100, 1300, 2200);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1012-E', 2024, stringdate('2024-12-31'), 4100, 330, 1.74, 100, 1350, 2250);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1012-E', 2025, stringdate('2025-12-31'), 4400, 360, 1.89, 100, 1400, 2300);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1013-E', 2023, stringdate('2023-12-31'), 6500, 780, 1.56, 500, 4600, 6000);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1013-E', 2024, stringdate('2024-12-31'), 6600, 800, 1.60, 500, 4700, 6050);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1013-E', 2025, stringdate('2025-12-31'), 6700, 790, 1.58, 500, 4700, 6100);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1014-E', 2023, stringdate('2023-12-31'), 1300, 5400, 77.14, 30, 260, 850);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1014-E', 2024, stringdate('2024-12-31'), 1500, 5320, 76.00, 30, 270, 880);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1014-E', 2025, stringdate('2025-12-31'), 1650, 5250, 75.00, 30, 270, 900);
INSERT INTO PortfolioKG.kidehen.fs_ff_basic_af VALUES ('C1001-E', 2022, stringdate('2022-12-31'), 3000, 400, 0.98, 100, 700, 2300);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1001-E', 2026, 'EPS', stringdate('2026-12-31'), 2.50, 2.75, 2.25, 11, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1001-E', 2027, 'EPS', stringdate('2027-12-31'), 3.05, 3.40, 2.70, 9, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1002-E', 2026, 'EPS', stringdate('2026-12-31'), 1.85, 1.95, 1.70, 8, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1003-E', 2026, 'EPS', stringdate('2026-12-31'), 1.05, 1.25, 0.80, 14, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1004-E', 2026, 'EPS', stringdate('2026-12-31'), 3.40, 3.65, 3.10, 9, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1004-E', 2027, 'EPS', stringdate('2027-12-31'), 3.75, 4.10, 3.40, 7, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1005-E', 2026, 'EPS', stringdate('2026-12-31'), 2.95, 3.20, 2.70, 7, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1006-E', 2026, 'EPS', stringdate('2026-12-31'), 2.70, 2.85, 2.55, 12, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1007-E', 2026, 'EPS', stringdate('2026-12-31'), 1.45, 1.70, 1.20, 10, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1008-E', 2026, 'EPS', stringdate('2026-12-31'), 3.10, 3.50, 2.80, 6, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1008-E', 2027, 'EPS', stringdate('2027-12-31'), 3.60, 4.10, 3.10, 5, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1009-E', 2026, 'EPS', stringdate('2026-12-31'), -0.20, 0.10, -0.50, 5, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1010-E', 2026, 'EPS', stringdate('2026-12-31'), 2.25, 2.40, 2.10, 9, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1011-E', 2026, 'EPS', stringdate('2026-12-31'), 4.30, 4.70, 3.90, 13, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1011-E', 2027, 'EPS', stringdate('2027-12-31'), 4.75, 5.30, 4.20, 10, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1012-E', 2026, 'EPS', stringdate('2026-12-31'), 2.05, 2.30, 1.85, 3, NULL);
INSERT INTO PortfolioKG.kidehen.fs_fe_basic_conh_af VALUES ('C1013-E', 2026, 'EPS', stringdate('2026-12-31'), 1.55, 1.70, 1.40, 2, stringdate('2026-06-30'));

-- ── Part 2: IRI classes (one per entity; each can take minutes on a busy server) ──
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/client_iri> "http://demo.openlinksw.com/PortfolioKG/client/%d#this" (in client_id integer not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri> "http://demo.openlinksw.com/PortfolioKG/portfolio/%d#this" (in portfolio_id integer not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri> "http://demo.openlinksw.com/PortfolioKG/holding/%d#this" (in holding_id integer not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/security_iri> "http://demo.openlinksw.com/PortfolioKG/security/%s#this" (in entity_id varchar not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri> "http://demo.openlinksw.com/PortfolioKG/company/%s#this" (in company_id varchar not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri> "http://demo.openlinksw.com/PortfolioKG/financials/%s/%d#this" (in entity_id varchar not null, in fiscal_year integer not null);
SPARQL CREATE IRI CLASS <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri> "http://demo.openlinksw.com/PortfolioKG/estimate/%s/%d#this" (in entity_id varchar not null, in fiscal_year integer not null);

-- ── Part 2b: the ontology (TBox) and the inference rule set derived from it ──
DB.DBA.TTLP('@prefix pf:     <http://demo.openlinksw.com/schemas/PortfolioKG/> .
@prefix schema: <http://schema.org/> .
@prefix xsd:    <http://www.w3.org/2001/XMLSchema#> .
@prefix rdf:    <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix rdfs:   <http://www.w3.org/2000/01/rdf-schema#> .
@prefix owl:    <http://www.w3.org/2002/07/owl#> .
@prefix skos:   <http://www.w3.org/2004/02/skos/core#> .
<http://demo.openlinksw.com/schemas/PortfolioKG/> a owl:Ontology ; rdfs:label "PortfolioKG vocabulary"@en ; rdfs:comment "Terms for a synthetic wealth-management estate: holdings kept on premises and a marketplace-style share of symbology, fundamentals and consensus estimates. Every record is invented."@en .
pf:OnPremRecord a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "on-premises record"@en ; rdfs:comment "A row that lives in the first-party database; the stand-in for the foreign catalog."@en ; rdfs:seeAlso <http://dbpedia.org/resource/Federated_database_system> .
pf:MarketplaceRecord a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "marketplace record"@en ; rdfs:comment "A row that arrives in a third-party share; the stand-in for the shared catalog."@en ; rdfs:seeAlso <http://dbpedia.org/resource/Federated_database_system> .
pf:Client a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "client"@en ; rdfs:comment "A person or institution whose portfolios are managed."@en ; rdfs:subClassOf pf:OnPremRecord , schema:Organization ; rdfs:seeAlso schema:Organization .
pf:Portfolio a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "portfolio"@en ; rdfs:comment "A named collection of holdings belonging to one client."@en ; rdfs:subClassOf pf:OnPremRecord ; skos:closeMatch schema:Collection ; rdfs:seeAlso <http://dbpedia.org/resource/Portfolio_(finance)> .
pf:Holding a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "holding"@en ; rdfs:comment "A position: a number of shares or units of one symbol in one portfolio."@en ; rdfs:subClassOf pf:OnPremRecord ; rdfs:seeAlso schema:InvestmentOrDeposit , <http://dbpedia.org/resource/Stock> .
pf:Security a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "security"@en ; rdfs:comment "A listed instrument known to symbology; the hyperlink that carries a ticker to its company."@en ; rdfs:subClassOf pf:MarketplaceRecord ; skos:closeMatch schema:InvestmentOrDeposit ; rdfs:seeAlso <http://dbpedia.org/resource/Security_(finance)> .
pf:Company a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "company"@en ; rdfs:comment "A listed company, the level at which fundamentals and estimates are kept."@en ; rdfs:subClassOf pf:MarketplaceRecord , schema:Organization ; skos:closeMatch schema:Corporation .
pf:AnnualFinancials a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "annual financials"@en ; rdfs:comment "One fiscal year of reported fundamentals for a company."@en ; rdfs:subClassOf pf:MarketplaceRecord ; skos:closeMatch schema:Observation ; rdfs:seeAlso <http://dbpedia.org/resource/Financial_statement> .
pf:EarningsEstimate a owl:Class ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "earnings estimate"@en ; rdfs:comment "Analyst consensus earnings per share for one fiscal period of a company."@en ; rdfs:subClassOf pf:MarketplaceRecord ; skos:closeMatch schema:Observation ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:segment a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "segment"@en ; rdfs:comment "Trust, Foundation, Individual or Pension."@en ; rdfs:domain pf:Client ; rdfs:range xsd:string ; rdfs:seeAlso schema:category .
pf:ofClient a owl:ObjectProperty , owl:FunctionalProperty , owl:AsymmetricProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "of client"@en ; rdfs:comment "The client a portfolio belongs to."@en ; rdfs:domain pf:Portfolio ; rdfs:range pf:Client ; rdfs:seeAlso schema:customer .
pf:baseCurrency a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "base currency"@en ; rdfs:comment "Reporting currency of a portfolio."@en ; rdfs:domain pf:Portfolio ; rdfs:range xsd:string ; rdfs:seeAlso schema:currency .
pf:inPortfolio a owl:ObjectProperty , owl:FunctionalProperty , owl:AsymmetricProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "in portfolio"@en ; rdfs:comment "The portfolio a holding sits in."@en ; rdfs:domain pf:Holding ; rdfs:range pf:Portfolio ; rdfs:seeAlso schema:isPartOf .
pf:symbol a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "symbol as held"@en ; rdfs:comment "The ticker-and-region symbol recorded in the first-party system."@en ; rdfs:domain pf:Holding ; rdfs:range xsd:string ; rdfs:seeAlso schema:tickerSymbol .
pf:instrumentType a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "instrument type"@en ; rdfs:comment "Equity or Bond."@en ; rdfs:domain pf:Holding ; rdfs:range xsd:string ; rdfs:seeAlso schema:additionalType .
pf:shares a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "shares"@en ; rdfs:comment "Number of shares or units held."@en ; rdfs:domain pf:Holding ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Stock> .
pf:avgCost a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "average cost"@en ; rdfs:comment "Average cost per unit in the base currency."@en ; rdfs:domain pf:Holding ; rdfs:range xsd:decimal ; rdfs:seeAlso schema:price .
pf:acquiredDate a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "acquired date"@en ; rdfs:comment "Date the position was opened."@en ; rdfs:domain pf:Holding ; rdfs:range xsd:date ; rdfs:seeAlso schema:startDate .
pf:ofSecurity a owl:ObjectProperty , owl:FunctionalProperty , owl:AsymmetricProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "of security"@en ; rdfs:comment "The security a holding is a position in, resolved through symbology."@en ; rdfs:domain pf:Holding ; rdfs:range pf:Security ; rdfs:seeAlso <http://dbpedia.org/resource/Security_(finance)> .
pf:tickerRegion a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "ticker and region"@en ; rdfs:comment "Symbol of a security in symbology."@en ; rdfs:domain pf:Security ; rdfs:range xsd:string ; rdfs:seeAlso schema:tickerSymbol .
pf:ofCompany a owl:ObjectProperty , owl:FunctionalProperty , owl:AsymmetricProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "of company"@en ; rdfs:comment "The company that issued a security."@en ; rdfs:domain pf:Security ; rdfs:range pf:Company ; rdfs:seeAlso <http://dbpedia.org/resource/Security_(finance)> .
pf:sector a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "sector"@en ; rdfs:comment "Industry sector of a company."@en ; rdfs:domain pf:Company ; rdfs:range xsd:string ; rdfs:seeAlso schema:industry .
pf:forCompany a owl:ObjectProperty , owl:FunctionalProperty , owl:AsymmetricProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "for company"@en ; rdfs:comment "The company a fundamentals or estimate record describes."@en ; rdfs:range pf:Company ; rdfs:seeAlso schema:about .
pf:fiscalYear a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "fiscal year"@en ; rdfs:comment "Fiscal year of a financials or estimate record."@en ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Fiscal_year> .
pf:fiscalDate a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "fiscal date"@en ; rdfs:comment "Closing date of the fiscal period."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:date ; rdfs:seeAlso <http://dbpedia.org/resource/Fiscal_year> .
pf:sales a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "sales"@en ; rdfs:comment "Reported sales in millions of dollars."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Revenue> .
pf:netIncome a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "net income"@en ; rdfs:comment "Reported net income in millions of dollars; negative for a loss."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Net_income> .
pf:epsBasic a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "basic EPS"@en ; rdfs:comment "Reported basic earnings per share."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:decimal ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:debtShortTerm a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "short-term debt"@en ; rdfs:comment "Short-term debt in millions of dollars."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Debt-to-equity_ratio> .
pf:debtLongTerm a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "long-term debt"@en ; rdfs:comment "Long-term debt in millions of dollars."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Debt-to-equity_ratio> .
pf:commonEquity a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "common equity"@en ; rdfs:comment "Common equity in millions of dollars."@en ; rdfs:domain pf:AnnualFinancials ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Debt-to-equity_ratio> .
pf:estimateItem a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "estimate item"@en ; rdfs:comment "The estimated measure; EPS here."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:string ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:periodEnd a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "period end"@en ; rdfs:comment "Closing date of the estimated fiscal period."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:date ; rdfs:seeAlso <http://dbpedia.org/resource/Fiscal_year> .
pf:epsMean a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "mean EPS estimate"@en ; rdfs:comment "Analyst consensus mean earnings per share."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:decimal ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:epsHigh a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "high EPS estimate"@en ; rdfs:comment "Highest analyst estimate."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:decimal ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:epsLow a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "low EPS estimate"@en ; rdfs:comment "Lowest analyst estimate."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:decimal ; rdfs:seeAlso <http://dbpedia.org/resource/Earnings_per_share> .
pf:analystCount a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "analyst count"@en ; rdfs:comment "Number of analysts behind the consensus."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:integer ; rdfs:seeAlso <http://dbpedia.org/resource/Financial_analyst> .
pf:consensusEnded a owl:DatatypeProperty , owl:FunctionalProperty ; rdfs:isDefinedBy <http://demo.openlinksw.com/schemas/PortfolioKG/> ; rdfs:label "consensus ended"@en ; rdfs:comment "Date a consensus stopped being current; absent while current."@en ; rdfs:domain pf:EarningsEstimate ; rdfs:range xsd:date ; rdfs:seeAlso schema:endDate .', 'http://demo.openlinksw.com/schemas/PortfolioKG/', 'http://demo.openlinksw.com/schemas/PortfolioKG/');
DB.DBA.rdfs_rule_set('urn:portfoliokg:inference', 'http://demo.openlinksw.com/schemas/PortfolioKG/');

-- ── Part 3: the RDF View. One statement per target graph: a second ALTER QUAD STORAGE for the same graph REPLACES the first. Nullable columns carry an IS NOT NULL guard. ──
SPARQL
ALTER QUAD STORAGE virtrdf:DefaultQuadStorage
  FROM PortfolioKG.kidehen.client AS c
  FROM PortfolioKG.kidehen.portfolio AS p
  FROM PortfolioKG.kidehen.equity_holdings AS h
  FROM PortfolioKG.kidehen.fs_sym_ticker_region AS s
  FROM PortfolioKG.kidehen.fs_ff_sec_map AS fm
  FROM PortfolioKG.kidehen.fs_company AS co
  FROM PortfolioKG.kidehen.fs_ff_basic_af AS f
  FROM PortfolioKG.kidehen.fs_fe_basic_conh_af AS e
{
  GRAPH <http://demo.openlinksw.com/PortfolioKG#>
  {
    <http://demo.openlinksw.com/schemas/PortfolioKG/client_iri>(c.client_id) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/Client> as virtrdf:PortfolioKG-client-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/client_iri>(c.client_id) <http://schema.org/name> c.client_name as virtrdf:PortfolioKG-client-name .
    <http://demo.openlinksw.com/schemas/PortfolioKG/client_iri>(c.client_id) <http://demo.openlinksw.com/schemas/PortfolioKG/segment> c.segment as virtrdf:PortfolioKG-client-segment .
    <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri>(p.portfolio_id) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/Portfolio> as virtrdf:PortfolioKG-pf-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri>(p.portfolio_id) <http://schema.org/name> p.portfolio_name as virtrdf:PortfolioKG-pf-name .
    <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri>(p.portfolio_id) <http://demo.openlinksw.com/schemas/PortfolioKG/baseCurrency> p.base_currency as virtrdf:PortfolioKG-pf-ccy .
    <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri>(p.portfolio_id) <http://demo.openlinksw.com/schemas/PortfolioKG/ofClient> <http://demo.openlinksw.com/schemas/PortfolioKG/client_iri>(p.client_id) as virtrdf:PortfolioKG-pf-client .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/Holding> as virtrdf:PortfolioKG-h-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/inPortfolio> <http://demo.openlinksw.com/schemas/PortfolioKG/portfolio_iri>(h.portfolio_id) as virtrdf:PortfolioKG-h-pf .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/symbol> h.symbol as virtrdf:PortfolioKG-h-symbol .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/instrumentType> h.instrument_type as virtrdf:PortfolioKG-h-type2 .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/shares> h.number_of_shares as virtrdf:PortfolioKG-h-shares .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/avgCost> h.avg_cost as virtrdf:PortfolioKG-h-cost .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/acquiredDate> h.acquired_date as virtrdf:PortfolioKG-h-acq .
    <http://demo.openlinksw.com/schemas/PortfolioKG/holding_iri>(h.holding_id) <http://demo.openlinksw.com/schemas/PortfolioKG/ofSecurity> <http://demo.openlinksw.com/schemas/PortfolioKG/security_iri>(s.fsym_id) where (^{h.}^.symbol = ^{s.}^.ticker_region) as virtrdf:PortfolioKG-h-sec .
    <http://demo.openlinksw.com/schemas/PortfolioKG/security_iri>(s.fsym_id) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/Security> as virtrdf:PortfolioKG-s-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/security_iri>(s.fsym_id) <http://demo.openlinksw.com/schemas/PortfolioKG/tickerRegion> s.ticker_region as virtrdf:PortfolioKG-s-ticker .
    <http://demo.openlinksw.com/schemas/PortfolioKG/security_iri>(s.fsym_id) <http://demo.openlinksw.com/schemas/PortfolioKG/ofCompany> <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(fm.fsym_company_id) where (^{s.}^.fsym_id = ^{fm.}^.fsym_id) as virtrdf:PortfolioKG-s-co .
    <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(co.fsym_company_id) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/Company> as virtrdf:PortfolioKG-co-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(co.fsym_company_id) <http://schema.org/name> co.company_name as virtrdf:PortfolioKG-co-name .
    <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(co.fsym_company_id) <http://demo.openlinksw.com/schemas/PortfolioKG/sector> co.sector as virtrdf:PortfolioKG-co-sector .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/AnnualFinancials> as virtrdf:PortfolioKG-f-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/forCompany> <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(f.fsym_id) as virtrdf:PortfolioKG-f-co .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/fiscalYear> f.fiscal_year as virtrdf:PortfolioKG-f-year .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/fiscalDate> f.fiscal_date as virtrdf:PortfolioKG-f-date .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/sales> f.ff_sales as virtrdf:PortfolioKG-f-sales .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/netIncome> f.ff_net_income as virtrdf:PortfolioKG-f-ni .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/epsBasic> f.ff_eps_basic where (^{f.}^.ff_eps_basic is not null) as virtrdf:PortfolioKG-f-eps .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/debtShortTerm> f.ff_debt_st as virtrdf:PortfolioKG-f-dst .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/debtLongTerm> f.ff_debt_lt as virtrdf:PortfolioKG-f-dlt .
    <http://demo.openlinksw.com/schemas/PortfolioKG/financials_iri>(f.fsym_id, f.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/commonEquity> f.ff_com_eq as virtrdf:PortfolioKG-f-eq .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://demo.openlinksw.com/schemas/PortfolioKG/EarningsEstimate> as virtrdf:PortfolioKG-e-type .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/forCompany> <http://demo.openlinksw.com/schemas/PortfolioKG/company_iri>(e.fsym_id) as virtrdf:PortfolioKG-e-co .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/fiscalYear> e.fiscal_year as virtrdf:PortfolioKG-e-year .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/estimateItem> e.fe_item as virtrdf:PortfolioKG-e-item .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/periodEnd> e.fe_fp_end as virtrdf:PortfolioKG-e-end .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/epsMean> e.fe_mean where (^{e.}^.fe_mean is not null) as virtrdf:PortfolioKG-e-mean .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/epsHigh> e.fe_high where (^{e.}^.fe_high is not null) as virtrdf:PortfolioKG-e-high .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/epsLow> e.fe_low where (^{e.}^.fe_low is not null) as virtrdf:PortfolioKG-e-low .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/analystCount> e.fe_num_est as virtrdf:PortfolioKG-e-count .
    <http://demo.openlinksw.com/schemas/PortfolioKG/estimate_iri>(e.fsym_id, e.fiscal_year) <http://demo.openlinksw.com/schemas/PortfolioKG/consensusEnded> e.cons_end_date where (^{e.}^.cons_end_date is not null) as virtrdf:PortfolioKG-e-ended .
  }
};

-- ── Part 4: access. The view inherits SQL table privileges. SPARQL serves the anonymous endpoint; demo and vdb serve SPASQL and the Query Builder: on another server, drop those two lines. ──
GRANT SELECT ON PortfolioKG.kidehen.client TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.client TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.client TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.client TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.portfolio TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.portfolio TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.portfolio TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.portfolio TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.equity_holdings TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.equity_holdings TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.equity_holdings TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.equity_holdings TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_company TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_company TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_company TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_company TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_sym_ticker_region TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_sym_ticker_region TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_sym_ticker_region TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_sym_ticker_region TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_sec_map TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_sec_map TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_sec_map TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_sec_map TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_sec_map TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_sec_map TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_sec_map TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_sec_map TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_basic_af TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_basic_af TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_basic_af TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_ff_basic_af TO "vdb";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_basic_conh_af TO "SPARQL";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_basic_conh_af TO PUBLIC;
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_basic_conh_af TO "demo";
GRANT SELECT ON PortfolioKG.kidehen.fs_fe_basic_conh_af TO "vdb";

-- ── Part 5: URL rewrite rules, so every entity IRI resolves (303 to a description page; RDF requests routed to DESCRIBE). Generated by the OpenLink RDFVIEW_GENERATE_DATA_RULES function with iri_path_segment=PortfolioKG; the describe rules were edited to pass uri= and graph=, and the generic stat and objects rules were left out. ──
DB.DBA.URLREWRITE_CREATE_REGEX_RULE (
'portfoliokg_rule2',
1,
'(/[^#]*)',
vector('path'),
1,
'/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^%U%%23this%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/PortfolioKG%%23%%3E&format=%U',
vector('path', '*accept*'),
null,
'(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)|(text/turtle)',
2,
null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE (
'portfoliokg_rule1',
1,
'([^#]*)',
vector('path'),
1,
'/describe/?uri=http%%3A%%2F%%2F^{URIQADefaultHost}^%U%%23this&graph=http%%3A%%2F%%2F^{URIQADefaultHost}^%%2FPortfolioKG%%23',
vector('path'),
null,
null,
2,
303
);
DB.DBA.URLREWRITE_CREATE_RULELIST ( 'portfoliokg_rule_list1', 1, vector ( 'portfoliokg_rule1', 'portfoliokg_rule2'));
DB.DBA.VHOST_REMOVE (lpath=>'/PortfolioKG');
DB.DBA.VHOST_DEFINE (lpath=>'/PortfolioKG', ppath=>'/', vsp_user=>'dba', is_dav=>0,
is_brws=>0, opts=>vector ('url_rewrite', 'portfoliokg_rule_list1')
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE (
'portfoliokg_owl_rule2',
1,
'(/[^#]*)',
vector('path'),
1,
'/sparql?query=DESCRIBE+%%3Chttp%%3A//^{URIQADefaultHost}^%U%%3E+FROM+%%3Chttp%%3A//^{URIQADefaultHost}^/schemas/PortfolioKG%%23%%3E&format=%U',
vector('path', '*accept*'),
null,
'(text/rdf.n3)|(application/rdf.xml)|(text/n3)|(application/json)|(text/turtle)',
2,
null
);
DB.DBA.URLREWRITE_CREATE_REGEX_RULE (
'portfoliokg_owl_rule1',
1,
'([^#]*)',
vector('path'),
1,
'/describe/?uri=http%%3A%%2F%%2F^{URIQADefaultHost}^%U&graph=http%%3A%%2F%%2F^{URIQADefaultHost}^%%2Fschemas%%2FPortfolioKG%%2F',
vector('path'),
null,
null,
2,
303
);
DB.DBA.URLREWRITE_CREATE_RULELIST ( 'portfoliokg_owl_rule_list1', 1, vector ( 'portfoliokg_owl_rule1', 'portfoliokg_owl_rule2'));
DB.DBA.VHOST_REMOVE (lpath=>'/schemas/PortfolioKG');
DB.DBA.VHOST_DEFINE (lpath=>'/schemas/PortfolioKG', ppath=>'/', vsp_user=>'dba', is_dav=>0,
is_brws=>0, opts=>vector ('url_rewrite', 'portfoliokg_owl_rule_list1')
);

-- ── Part 6: the memory graph, standing instructions and a run record in Turtle, loaded as its own named graph. ──
DB.DBA.TTLP('@prefix pfm:    <http://demo.openlinksw.com/PortfolioKGMemory#> .
@prefix schema: <http://schema.org/> .
@prefix xsd:    <http://www.w3.org/2001/XMLSchema#> .
@prefix rdfs:   <http://www.w3.org/2000/01/rdf-schema#> .
pfm:analysisHowTo a schema:HowTo ;
    schema:name "How to answer a question about the client portfolios"@en ;
    schema:description "Standing instructions for an agent that answers portfolio questions over the PortfolioKG view. They carry the join chain and data-quality filters that the Databricks post gives its Genie space."@en ;
    schema:step pfm:step1, pfm:step2, pfm:step3, pfm:step4, pfm:step5, pfm:step6 .
pfm:step1 a schema:HowToStep ; schema:position 1 ; schema:name "Query in place, persist nothing"@en ;
    schema:text "Answer from the RDF View over the source tables. Create no view, table or copy for an answer; record only what was asked and what came back."@en .
pfm:step2 a schema:HowToStep ; schema:position 2 ; schema:name "Follow the join chain as links"@en ;
    schema:text "A holding points to a security (pf:ofSecurity), a security to its company (pf:ofCompany), and fundamentals and estimates point to the same company (pf:forCompany). In SQL the same chain is equity_holdings, fs_sym_ticker_region, fs_ff_sec_map, fs_ff_basic_af and, for estimates, fs_fe_sec_map and fs_fe_basic_conh_af."@en .
pfm:step3 a schema:HowToStep ; schema:position 3 ; schema:name "Apply the data-quality filters"@en ;
    schema:text "Equities only. Fundamentals: period closing 2023-01-01 or later, basic EPS present and below 50 in absolute value, sales above zero, latest row per company. Estimates: item EPS, no consensus end date, period ending today or later, absolute mean below 50, earliest period."@en .
pfm:step4 a schema:HowToStep ; schema:position 4 ; schema:name "Compute the measures"@en ;
    schema:text "Current earnings are shares times basic EPS; projected earnings are shares times mean EPS estimate; growth is (estimate minus EPS) divided by EPS; margin is net income divided by sales; debt to equity is short plus long-term debt divided by common equity. Keep holdings without an estimate, with the projection left empty."@en .
pfm:step5 a schema:HowToStep ; schema:position 5 ; schema:name "Classify risk and action"@en ;
    schema:text "Risk: unprofitable or debt to equity above 2.5 is high; growth below minus 15 percent or margin below 3 percent is medium; margin above 15 percent with debt to equity below 1.5 is low and strong; otherwise low. Action: a loss or growth below minus 15 percent is sell; above 20 percent strong buy; above 5 percent buy; above minus 10 percent hold and stable; otherwise hold and monitor."@en .
pfm:step6 a schema:HowToStep ; schema:position 6 ; schema:name "Write the run back to memory"@en ;
    schema:text "After an answer, add a schema:Action to this graph naming the question, the date and the counts returned, so the next session can read what was already decided."@en .
pfm:run1 a schema:Action ;
    schema:name "Holdings review, 2026-10-28"@en ;
    schema:startTime "2026-10-28T12:00:00Z"^^xsd:dateTime ;
    schema:instrument pfm:analysisHowTo ;
    schema:description "Reviewed 28 holdings: 3 left out by the filters (a bond, a symbol absent from symbology and a company whose reported EPS fails the plausibility test), 25 analysed, 4 of them high risk, 2 flagged sell for a loss and 2 for declining earnings."@en .', 'http://demo.openlinksw.com/PortfolioKGMemory#', 'http://demo.openlinksw.com/PortfolioKG/memory#');