--
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE appros;

--
-- Create table `eex_symbols_reference`
--
CREATE TABLE eex_symbols_reference (
  id int(11) NOT NULL AUTO_INCREMENT,
  symbol_native varchar(255) NOT NULL COMMENT 'Valeur native du symbol telle que définie dans le code',
  symbol_api_format varchar(255) NOT NULL COMMENT 'Valeur formatée envoyée dans l''API',
  symbol_stored_format varchar(255) NOT NULL COMMENT 'Valeur stockée en base de données',
  symbol_type enum ('FUTURES', 'SPOT', 'POWER_SPOT') NOT NULL,
  market_category enum ('POWER', 'GAS', 'EUA', 'CO2') NOT NULL,
  country_code varchar(10) DEFAULT NULL,
  country_name varchar(50) DEFAULT NULL,
  product_type enum ('BASE', 'PEAK', 'DAY_AHEAD', 'WEEKEND', 'NEXT_DAY') DEFAULT NULL,
  period_type enum ('YEAR', 'QUARTER', 'MONTH', 'SEASON', 'DAILY') DEFAULT NULL,
  exchange_name varchar(50) DEFAULT NULL,
  description_fr text DEFAULT NULL,
  description_en text DEFAULT NULL,
  is_active tinyint(1) DEFAULT 1,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 38,
AVG_ROW_LENGTH = 442,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
ROW_FORMAT = DYNAMIC;

--
-- Create index `idx_api_format` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_api_format (symbol_api_format);

--
-- Create index `idx_country_code` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_country_code (country_code);

--
-- Create index `idx_market_category` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_market_category (market_category);

--
-- Create index `idx_product_period` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_product_period (product_type, period_type);

--
-- Create index `idx_stored_format` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_stored_format (symbol_stored_format);

--
-- Create index `idx_symbol_type` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD INDEX idx_symbol_type (symbol_type);

--
-- Create index `unique_native_symbol` on table `eex_symbols_reference`
--
ALTER TABLE eex_symbols_reference
ADD UNIQUE INDEX unique_native_symbol (symbol_native);