
CREATE TABLE IF NOT EXISTS denominations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('billete','moneda') NOT NULL,
  value INT NOT NULL,
  family VARCHAR(50) DEFAULT '',
  year_from INT NULL,
  year_to   INT NULL,
  variant   VARCHAR(80) NOT NULL DEFAULT '',
  obverse_desc VARCHAR(150) DEFAULT '',
  reverse_desc VARCHAR(150) DEFAULT '',
  ref_url VARCHAR(255) NOT NULL,
  UNIQUE KEY ux_type_value_variant (type, value, variant)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ts DATETIME NOT NULL,
  label VARCHAR(64) NOT NULL,
  confidence DECIMAL(6,5) NOT NULL DEFAULT 0,
  type ENUM('billete','moneda') NULL,
  value INT NULL,
  side VARCHAR(16) NULL,
  url VARCHAR(255) NULL,
  user_agent VARCHAR(255) NULL,
  ip VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO denominations (type,value,family,year_from,year_to,variant,obverse_desc,reverse_desc,ref_url) VALUES
('billete',10,'1970',1970,1970,'reposicion_R_pequena','10 Pesos Oro 1970 (R pequeña)','', 'https://guia.numismaticol.com/b650-10-pesos-oro-1970-octubre-12-reposicion-r-pequena-abajo-en-el-centromuy-escaso/')
ON DUPLICATE KEY UPDATE ref_url=VALUES(ref_url);
INSERT INTO denominations (type,value,family,year_from,year_to,variant,obverse_desc,reverse_desc,ref_url) VALUES
('billete',2,'1923',1923,1923,'','2 Pesos Oro 1923','', 'https://guia.numismaticol.com/b758-2-pesos-oro-1923-julio-20/')
ON DUPLICATE KEY UPDATE ref_url=VALUES(ref_url);
INSERT INTO denominations (type,value,family,year_from,year_to,variant,obverse_desc,reverse_desc,ref_url) VALUES
('billete',2000,'2004',2004,2004,'','2000 Pesos 2004','', 'https://guia.numismaticol.com/b335-2000-pesos-2004-febrero-18-19/')
ON DUPLICATE KEY UPDATE ref_url=VALUES(ref_url);
