# Create a PostgreSQL schema file with tables and seed data for 8 B2C PV products (4 PV, 4 PV+Storage)
from pathlib import Path

sql = r"""
-- =============================================================
-- B2C PV Simulator – Minimal PostgreSQL Data Model (v0.1)
-- Targets: Ubuntu + PostgreSQL 14+
-- Includes: schema, tables, constraints, one price list, 8 products, prices
-- =============================================================

-- 0) Extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- for gen_random_uuid()

-- 1) Schema
CREATE SCHEMA IF NOT EXISTS b2c;

-- 2) Lookup: currency
CREATE TABLE IF NOT EXISTS b2c.currency (
  code CHAR(3) PRIMARY KEY,        -- e.g., 'CLP', 'USD'
  name TEXT NOT NULL
);

INSERT INTO b2c.currency (code, name)
VALUES ('CLP', 'Chilean Peso')
ON CONFLICT (code) DO NOTHING;

-- 3) Price list (allows multiple lists over time)
CREATE TABLE IF NOT EXISTS b2c.price_list (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,                 -- e.g., 'B2C CLP 2025'
  currency_code CHAR(3) NOT NULL REFERENCES b2c.currency(code),
  tax_included BOOLEAN NOT NULL DEFAULT FALSE,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to   DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (valid_to IS NULL OR valid_to > valid_from)
);

-- Seed a default price list
INSERT INTO b2c.price_list (name, currency_code, tax_included, valid_from)
VALUES ('B2C CLP 2025', 'CLP', FALSE, CURRENT_DATE)
ON CONFLICT (name) DO NOTHING;

-- 4) Products (PV kits y PV+Storage)
--   category: 'PV' | 'PV+STORAGE'
--   battery_kwh: NULL for PV-only, >0 for PV+Storage
CREATE TABLE IF NOT EXISTS b2c.product (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT NOT NULL UNIQUE,                -- e.g., 'KIT-S', 'KIT-M-BAT'
  name TEXT NOT NULL,                       -- e.g., 'Kit S (3 kWp)'
  category TEXT NOT NULL CHECK (category IN ('PV','PV+STORAGE')),
  kwp NUMERIC(6,2) NOT NULL CHECK (kwp > 0),
  battery_kwh NUMERIC(7,2) CHECK (battery_kwh IS NULL OR battery_kwh > 0),
  description TEXT,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_product_category ON b2c.product(category);
CREATE INDEX IF NOT EXISTS idx_product_kwp ON b2c.product(kwp);

-- 5) Product price (many-to-one by price list, supports history via effective dates)
CREATE TABLE IF NOT EXISTS b2c.product_price (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES b2c.product(id) ON DELETE CASCADE,
  price_list_id UUID NOT NULL REFERENCES b2c.price_list(id) ON DELETE CASCADE,
  capex_net NUMERIC(14,2) NOT NULL CHECK (capex_net >= 0),  -- price without tax (CLP)
  install_days INTEGER CHECK (install_days IS NULL OR install_days >= 0),
  notes TEXT,
  valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to   DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (valid_to IS NULL OR valid_to > valid_from),
  UNIQUE (product_id, price_list_id, valid_from)
);

CREATE INDEX IF NOT EXISTS idx_product_price_current
  ON b2c.product_price(product_id, price_list_id, valid_from, valid_to);

-- 6) VIEW: current prices (where today is within [valid_from, valid_to) or valid_to IS NULL)
CREATE OR REPLACE VIEW b2c.v_current_prices AS
SELECT
  p.id AS product_id,
  p.code,
  p.name,
  p.category,
  p.kwp,
  p.battery_kwh,
  pl.name AS price_list,
  pp.capex_net,
  pl.currency_code,
  pp.install_days,
  pp.valid_from,
  pp.valid_to
FROM b2c.product p
JOIN b2c.product_price pp ON pp.product_id = p.id
JOIN b2c.price_list pl ON pl.id = pp.price_list_id
WHERE (pp.valid_to IS NULL OR CURRENT_DATE < pp.valid_to)
  AND CURRENT_DATE >= pp.valid_from;

-- 7) Seed data: 8 products (4 PV + 4 PV+Storage)
-- Dummy pricing (CLP) and installation days as placeholders.
WITH pl AS (
  SELECT id FROM b2c.price_list WHERE name = 'B2C CLP 2025' LIMIT 1
)
INSERT INTO b2c.product (code, name, category, kwp, battery_kwh, description)
VALUES
  -- PV only
  ('KIT-S',      'Kit S (3 kWp)',        'PV',          3.00, NULL, 'Vivienda pequeña, consumo bajo'),
  ('KIT-M',      'Kit M (5 kWp)',        'PV',          5.00, NULL, 'Vivienda media, consumo medio'),
  ('KIT-L',      'Kit L (7.5 kWp)',      'PV',          7.50, NULL, 'Vivienda med/alta, consumo medio-alto'),
  ('KIT-XL',     'Kit XL (10 kWp)',      'PV',         10.00, NULL, 'Vivienda grande, consumo alto'),
  -- PV + Storage
  ('KIT-S-BAT',  'Kit S + Storage (3 kWp + 5 kWh)',   'PV+STORAGE', 3.00,  5.00, 'Autoconsumo con respaldo básico'),
  ('KIT-M-BAT',  'Kit M + Storage (5 kWp + 10 kWh)',  'PV+STORAGE', 5.00, 10.00, 'Mayor independencia energética'),
  ('KIT-L-BAT',  'Kit L + Storage (7.5 kWp + 15 kWh)','PV+STORAGE', 7.50, 15.00, 'Optimizado para pico vespertino'),
  ('KIT-XL-BAT', 'Kit XL + Storage (10 kWp + 20 kWh)','PV+STORAGE',10.00, 20.00, 'Máxima cobertura y respaldo')
ON CONFLICT (code) DO NOTHING;

-- Attach prices to the inserted products
INSERT INTO b2c.product_price (product_id, price_list_id, capex_net, install_days, notes)
SELECT p.id, pl.id, t.capex_net, t.install_days, t.notes
FROM b2c.product p
CROSS JOIN pl
JOIN (
  VALUES
    -- code       capex_net (CLP)  install_days  notes
    ('KIT-S',      3800000.00,          2,      'Dummy 2025'),
    ('KIT-M',      5200000.00,          3,      'Dummy 2025'),
    ('KIT-L',      6900000.00,          4,      'Dummy 2025'),
    ('KIT-XL',     8800000.00,          5,      'Dummy 2025'),
    ('KIT-S-BAT',  6200000.00,          3,      'Incluye batería 5 kWh'),
    ('KIT-M-BAT',  8800000.00,          4,      'Incluye batería 10 kWh'),
    ('KIT-L-BAT', 11500000.00,          5,      'Incluye batería 15 kWh'),
    ('KIT-XL-BAT',14800000.00,          6,      'Incluye batería 20 kWh')
) AS t(code, capex_net, install_days, notes)
ON CONFLICT DO NOTHING;

-- 8) Quick sanity checks
-- SELECT * FROM b2c.v_current_prices ORDER BY kwp, battery_kwh NULLS FIRST;

"""

path = Path("/mnt/data/schema_b2c_pv.sql")
path.write_text(sql, encoding="utf-8")
str(path)

