-- Reading and writing through a synonym
\pset null '(null)'
\set SHOW_CONTEXT never

CREATE SCHEMA erp;
CREATE SCHEMA vendas;
CREATE TABLE erp.clientes (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nome   text NOT NULL,
    ativo  boolean DEFAULT true,
    limite numeric(10,2) CHECK (limite >= 0)
);
SELECT synonym.create('vendas.cliente', 'erp.clientes');

-- INSERT uses the target's defaults and identity
INSERT INTO vendas.cliente (nome, limite) VALUES ('Ana', 100) RETURNING id, nome, ativo, limite;
INSERT INTO vendas.cliente (nome) VALUES ('Bia'), ('Caio');

-- UPDATE / DELETE
UPDATE vendas.cliente SET ativo = false WHERE nome = 'Bia';
DELETE FROM vendas.cliente WHERE nome = 'Caio';

-- ON CONFLICT
INSERT INTO vendas.cliente (id, nome) OVERRIDING SYSTEM VALUE VALUES (1, 'Ana Maria')
ON CONFLICT (id) DO UPDATE SET nome = excluded.nome;

-- the target's constraints still apply
INSERT INTO vendas.cliente (nome, limite) VALUES ('Davi', -1);

SELECT * FROM erp.clientes ORDER BY id;

-- joins, CTEs and subqueries
CREATE TABLE erp.pedidos (id int, cliente_id int, total numeric);
INSERT INTO erp.pedidos VALUES (10, 1, 50), (11, 1, 25), (12, 2, 10);
SELECT synonym.create('vendas.pedido', 'erp.pedidos');
SELECT c.nome, sum(p.total) AS total
FROM vendas.cliente c JOIN vendas.pedido p ON p.cliente_id = c.id
GROUP BY c.nome ORDER BY c.nome;

-- the planner sees straight through the view: same plan as querying the table
EXPLAIN (COSTS OFF) SELECT * FROM vendas.cliente WHERE id = 1;

-- views and materialized views as targets
CREATE VIEW erp.ativos AS SELECT id, nome FROM erp.clientes WHERE ativo;
CREATE MATERIALIZED VIEW erp.resumo AS SELECT count(*) AS n FROM erp.clientes;
SELECT synonym.create('vendas.ativo', 'erp.ativos');
SELECT synonym.create('vendas.resumo', 'erp.resumo');
SELECT * FROM vendas.ativo ORDER BY id;
SELECT * FROM vendas.resumo;

-- partitioned table as target
CREATE TABLE erp.vendas (dia date, valor numeric) PARTITION BY RANGE (dia);
CREATE TABLE erp.vendas_2026 PARTITION OF erp.vendas FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
SELECT synonym.create('vendas.venda', 'erp.vendas');
INSERT INTO vendas.venda VALUES ('2026-10-19', 99.90);
SELECT * FROM erp.vendas_2026;

SELECT synonym, target, status FROM synonym.list ORDER BY synonym;

SELECT synonym.drop(s.synonym) FROM synonym.list s ORDER BY s.synonym;
DROP SCHEMA erp CASCADE;
DROP SCHEMA vendas CASCADE;