function make_sni_groups(sni_string){
  var results = [];
  for ( let i = 2 ; i <= sni_string.length; i ++ ) {
    results.push(sni_string.substring(0,i));
  }
  return results;
}

prod_code_combos = [ ... new Set(
  (db_filtered
  .map( (d) => (make_sni_groups(d.prod_code))))
  .reduce((a,b) => [].concat(a, b))
)
]

prod_code_relations = prod_code_combos.map( (d) => ({
  parent : d.length === 2 ? "SNI" : d.substr(0, d.length -1 ),
  name : d,
  tooltip: ""
}))

prod_code_innovations = db_filtered.filter(d => d.prod_code != "").map( (d) => ({
    parent : d.prod_code,
    name : d.inn_id,
    tooltip: `Innovation: ${d.inn_id}\nName: ${d.name_sv}\nYear: ${d.year}\nFirm: ${d.firm}\n${d.desc_sv}`

}))

prod_code_tree = [].concat(
  [{'parent': null, name: "SNI", tooltip: ""}],
  prod_code_relations,
  prod_code_innovations
)

hi = d3.stratify().id((d) => d.name).parentId((d) => d.parent)(prod_code_tree);

hi.sum((d) => 1 ? d.tooltip.substring(0,12) === "Innovation: " : 0)
  .sort((a, b) => a.value + b.value);

function make_tree_tooltip(item){
  if (item.data.tooltip != ""){
    return item.data.tooltip
  }
  return `${get_sni_name(item.data.name)}\nInnovations: ${item.value}`
}

console.log('Loading data')

database = FileAttachment("https://zenodo.org/api/records/15285784/files/SWINNO.UDIT.sqlite3/content").sqlite()
sni_options = await FileAttachment("sni2002.csv").csv({typed: false})
function k(d) {
  return Number(d3.utcFormat('%Y')(d)) +1
}
fl = d3.format('.1f')

d3 = require("d3")
ts = d3.format(",");

db = database.sql`SELECT i.id as inn_id, year, patented, coalesce(prod_code, '') as prod_code, coalesce(name_sv, "[Unnamed]") as name_sv, coalesce(name, "[Unnamed]" ) as firm, desc_sv from innovation as i left outer join innovation_entity as ie on i.id == ie.innovation_id and ie.type == 1 left outer join entity as e on e.id == ie.entity_id where year between 1970 and 2021 order by year, prod_code`

used_prod = [ ... new Set(db.map((d) => d.prod_code.substring(0, 2))) ]

used_firms = [ ... new Set(db.map((d) => d.firm)) ]
  .filter((d) => (d != '[Unnamed]'))

used_names = [ ... new Set(db.map((d) => d.name_sv)) ]
  .filter((d) => (d != '[Unnamed]'))

target_codes = codes.map( (d) => d.code)

years = Object.fromEntries(['min', 'max'].map((k, i) => [k, d3.extent(ranges)[i]]))

prod_filter = codes.length === 0 ?  (d) => (true) : (d) => target_codes.includes(d['prod_code'].substring(0,2))

year_filter = (d) => (d.year >= years.min & d.year <= years.max)

firm_filter = target_firm.length === 0 ?  (d) => (true) : (d) => (d.firm == target_firm | d.firm.toLowerCase().includes(target_firm))

name_filter = target_inn.length === 0 ?  (d) => (true) : (d) => (
  d.name_sv == target_inn |
                d.name_sv.toLowerCase().includes(target_inn) |
                d.desc_sv.toLowerCase().includes(target_inn) |
                new String(d.inn_id).includes(target_inn)
          )

tmpdb = db.filter(prod_filter)
     .filter(year_filter)
     .filter(firm_filter)
     .filter(name_filter)
db_filtered = tmpdb.length == 0 ? db : tmpdb

tl = Array.from(d3.group(db_filtered, d  => d.year))

innovations = tl.map((d) => ({
  year : d[0],
  value : d[1].length,
  label: "Innovations"

}))

ylim = Math.ceil(innovations.map(a => a.value).reduce((a,b) => Math.max(a,b))/10)*10

patented = tl.map((d) => ({
  year : d[0],
  value: d[1].map( (d) => d['patented']).reduce((a,b) => a + b),
  label: "Patented"
})).filter(d => d.year <= 2015)

t2 = [].concat(innovations, patented)

timeline_raw = [].concat(
  [{
    year: new Date(years.min, 0, 1),
  label: "Innovations",
  value: undefined,
}],
  t2.map( (r) => ({
  year: r.year,
  label: r.label,
  value: r.value,
})),
[{
  year: new Date(years.max, 0, 1),
  label: "Innovations",
  value: undefined,
}]
)


function fill_in_zeros(data, labels){
  let result = [];
  for (let year = years.min; year <= years.max; year++){
    let year_object = data.find((t) => t.year == year);

    labels.forEach((label) => {
    let value = data.find((t) => t.label == label && t.year == year, false)
    let date = new Date(year, 0, 1);
      if (label == 'Patented' && year > 2015){
        // Patents past 2015 are not in the data.
        result.push({
          year: date,
          label: label,
          value: NaN
          });
      } else if (value != undefined){
        result.push({
          year: date,
          label: label,
          value: value.value
          });
      } else {
        result.push({
          year: date,
          label: label,
          value: 0
      })
      }
      })

    }
  return result;
  }

timeline_data = fill_in_zeros(timeline_raw, ['Innovations', 'Patented'])

title = d3.selectAll('.trendCard')
  .selectAll('.card-title')
  .text(`Trends of ${ts(tmpdb.length)} SWINNO innovations `)

footer_right = d3.selectAll('.nav-footer-left')
    .append('p')
    .html('Access the data on <a href="https://zenodo.org/records/10602308">Zenodo</a>')

footer_left = d3.selectAll('.nav-footer-right')
    .append('p')
    .html('Explore the <a href="https://lite.datasette.io/?metadata=https://raw.githubusercontent.com/swinnoproject/datasette-lite/refs/heads/main/metadata.yml&url=https://zenodo.org/api/records/15285784/files/SWINNO.UDIT.sqlite3/content">database interface</a> directly in your browser')

filtered_prod = db_filtered.map((d) => ({
  year: d.year,
  sni: d.prod_code.substring(0,2)
})
)

prod2s = Array.from(
  d3.group(filtered_prod, d => d.sni)
  )

prod3s = prod2s.sort((a,b) => d3.descending( a[1].length, b[1].length))

top10 = prod3s.length <= 10 ? prod3s.slice(0,10).map(d => d[0]) : [].concat(prod3s.slice(0,9).map(d => d[0]), ['Other'])


mutated_prodCode = Array.from(d3.group(filtered_prod.map( (d) => ({
 year: d.year,
 sni : top10.includes(d.sni) ? d.sni : "Other"
})),
  d => d.year, d => d.sni
)).map( (d) => ({
 year: d[0],
 snis : Array.from(d[1])}
 )
 ).map(d => ( {
  year : d.year,
  snis: d.snis.map( (d) => ({
    sni : d[0],
    count : d[1].length
  }))
 }))


function fill_in_zero_timestamps(data, labels){
  let result = [];
  for (let year = years.min; year <= years.max; year++){
    let year_object = data.find((t) => t.year == year)

    let sni_counts = year_object == undefined ? [] :  year_object.snis

    labels.forEach((label) => {
      let value = sni_counts.find((t) => t.sni == label, false)
        if (value != undefined){
          result.push({
            year: new Date(year, 0, 1),
            label: label,
            count: value.count
            });
        } else {
          result.push({
            year: new Date(year, 0, 1),
            label: label,
            count: 0
        })
        }
        })

    }

  return result;
  }

prodtime_data_prep = fill_in_zero_timestamps(mutated_prodCode, top10)

prodtime_data_prep

function get_sni_name(code){
  if ((code == "Other") || (code == "SNI") || (code.length > 5)){
    return code
  }

  var result = sni_options.find((t) => t.code == code)

  if (result == undefined){
    return code
   }

  return `${code}: ${result.desc}`
}

prodtime_data = prodtime_data_prep.map( (r) => ({
  year: r.year,
  label: get_sni_name(r.label),
  value: r.count,
}))

function ZoomBurst(hierarchy, {
  height = 600,
  width = 600,
} = {}) {


  // Specify the chart’s dimensions.
  const radius = width / 6;

  // // Create the color scale.
  // const color = d3.scaleOrdinal(d3.quantize(d3.interpolateRainbow, data.children.length + 1));
  // const color = d3.scaleOrdinal(d3.quantize(d3.interpolateRainbow, 100 + 1));


  // const color = d3.scaleOrdinal(d3.quantize(d3.interpolateRainbow, hierarchy.children.length + 1));
  const color = d3.scaleOrdinal(d3.schemeCategory10);

  const root = d3.partition()
      .size([2 * Math.PI, hierarchy.height + 1])
    (hierarchy);
  root.each(d => d.current = d);

  // Create the arc generator.
  const arc = d3.arc()
      .startAngle(d => d.x0)
      .endAngle(d => d.x1)
      .padAngle(d => Math.min((d.x1 - d.x0) / 2, 0.005))
      .padRadius(radius * 1.5)
      .innerRadius(d => d.y0 * radius)
      .outerRadius(d => Math.max(d.y0 * radius, d.y1 * radius - 1))

  // Create the SVG container.
  const svg = d3.create("svg")
      .attr("viewBox", [-width / 2, -height / 2, width * 1 , width])
      .style("font", "10px sans-serif");

  // Append the arcs.
  const path = svg.append("g")
    .selectAll("path")
    .data(root.descendants().slice(1))
    .join("path")
      .attr("fill", d => { while (d.depth >= 2) d = d.parent; return color(d.value); })
      .attr("fill-opacity", d => arcVisible(d.current) ? (d.children ? 0.6 : 0.4) : 0)
      .attr("pointer-events", d => arcVisible(d.current) ? "auto" : "none")

      .attr("d", d => arc(d.current));

  // Make them clickable if they have children.
  path.filter(d => d.children)
      .style("cursor", "pointer")
      .on("click", clicked);

  const format = d3.format(",d");
  path.append("title")
      .text(d => make_tree_tooltip(d));


  const label = svg.append("g")
      .attr("pointer-events", "none")
      .attr("text-anchor", "middle")
      .style("user-select", "none")
    .selectAll("text")
    .data(root.descendants().slice(1))
    .join("text")
      .attr("dy", "0.35em")
      .attr("fill-opacity", d => +labelVisible(d.current))
      .attr("transform", d => labelTransform(d.current))
      .text(d => d.data.name);

  const parent = svg.append("circle")
      .datum(root)
      .attr("r", radius)
      .attr("fill", "none")
      .attr("pointer-events", "all")
      .on("click", clicked);

  // Handle zoom on click.
  function clicked(event, p) {
    parent.datum(p.parent || root);

    root.each(d => d.target = {
      x0: Math.max(0, Math.min(1, (d.x0 - p.x0) / (p.x1 - p.x0))) * 2 * Math.PI,
      x1: Math.max(0, Math.min(1, (d.x1 - p.x0) / (p.x1 - p.x0))) * 2 * Math.PI,
      y0: Math.max(0, d.y0 - p.depth),
      y1: Math.max(0, d.y1 - p.depth)
    });


    const t = svg.transition().duration(750);

    // Transition the data on all arcs, even the ones that aren’t visible,
    // so that if this transition is interrupted, entering arcs will start
    // the next transition from the desired position.
    path.transition(t)
        .tween("data", d => {
          const i = d3.interpolate(d.current, d.target);
          return t => d.current = i(t);
        })
      .filter(function(d) {
        return +this.getAttribute("fill-opacity") || arcVisible(d.target);
      })
        .attr("fill-opacity", d => arcVisible(d.target) ? (d.children ? 0.6 : 0.4) : 0)
        .attr("pointer-events", d => arcVisible(d.target) ? "auto" : "none")

        .attrTween("d", d => () => arc(d.current));

    label.filter(function(d) {
        return +this.getAttribute("fill-opacity") || labelVisible(d.target);
      }).transition(t)
        .attr("fill-opacity", d => +labelVisible(d.target))
        .attrTween("transform", d => () => labelTransform(d.current));
  }

  function arcVisible(d) {
    return d.y1 <= 3 && d.y0 >= 1 && d.x1 > d.x0;
  }

  function labelVisible(d) {
    return d.y1 <= 3 && d.y0 >= 1 && (d.y1 - d.y0) * (d.x1 - d.x0) > 0.03;
  }

  function labelTransform(d) {
    const x = (d.x0 + d.x1) / 2 * 180 / Math.PI;
    const y = (d.y0 + d.y1) / 2 * radius;
    return `rotate(${x - 90}) translate(${y},0) rotate(${x < 180 ? 0 : 180})`;
  }

  return svg.node();
}