SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Crea la BD si hace falta:
-- CREATE DATABASE aureaport CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE aureaport;

CREATE TABLE IF NOT EXISTS tenants (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  plan_code VARCHAR(12) NOT NULL DEFAULT 'starter_50',
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at DATETIME NULL,
  CONSTRAINT chk_tenants_status CHECK (status IN ('inactive','active')),
  CONSTRAINT chk_tenants_plan CHECK (plan_code IN ('pro_100','starter_50'))
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  email VARCHAR(190) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at DATETIME NULL,
  CONSTRAINT chk_users_status CHECK (status IN ('blocked','active')),
  CONSTRAINT uq_users_tenant_email UNIQUE (tenant_id, email),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  CONSTRAINT uq_roles_code UNIQUE (code)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS clients (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  code VARCHAR(40) NULL,
  name VARCHAR(200) NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at DATETIME NULL,
  CONSTRAINT chk_clients_status CHECK (status IN ('inactive','active')),
  CONSTRAINT fk_clients_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  INDEX ix_clients_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS client_users (
  tenant_id BIGINT NOT NULL,
  client_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  PRIMARY KEY (tenant_id, client_id, user_id),
  CONSTRAINT fk_cu_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_cu_client FOREIGN KEY (client_id) REFERENCES clients(id),
  CONSTRAINT fk_cu_user FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX ix_cu_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS assets (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  ticker VARCHAR(40) NOT NULL,
  name VARCHAR(200) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'MXN',
  sector VARCHAR(80) NULL,
  asset_type VARCHAR(40) NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at DATETIME NULL,
  CONSTRAINT chk_assets_status CHECK (status IN ('inactive','active')),
  CONSTRAINT fk_assets_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT uq_assets_tenant_ticker UNIQUE (tenant_id, ticker),
  INDEX ix_assets_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS portfolios (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  client_id BIGINT NOT NULL,
  name VARCHAR(160) NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'active',
  risk_profile VARCHAR(20) NOT NULL DEFAULT 'moderate',
  target_vol DECIMAL(10,6) NULL,
  return_type VARCHAR(10) NOT NULL DEFAULT 'log',
  annual_factor INT NOT NULL DEFAULT 252,
  long_only TINYINT(1) NOT NULL DEFAULT 1,
  min_weight DECIMAL(10,6) NOT NULL DEFAULT 0.0,
  max_weight DECIMAL(10,6) NOT NULL DEFAULT 0.4,
  frontier_points INT NOT NULL DEFAULT 25,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at DATETIME NULL,
  CONSTRAINT chk_pf_status CHECK (status IN ('inactive','active')),
  CONSTRAINT chk_pf_risk CHECK (risk_profile IN ('custom','aggressive','moderate','conservative')),
  CONSTRAINT chk_pf_return CHECK (return_type IN ('simple','log')),
  CONSTRAINT fk_pf_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_pf_client FOREIGN KEY (client_id) REFERENCES clients(id),
  INDEX ix_pf_tenant (tenant_id),
  INDEX ix_pf_client (client_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS portfolio_assets (
  portfolio_id BIGINT NOT NULL,
  asset_id BIGINT NOT NULL,
  PRIMARY KEY (portfolio_id, asset_id),
  CONSTRAINT fk_pfa_pf FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
  CONSTRAINT fk_pfa_asset FOREIGN KEY (asset_id) REFERENCES assets(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS price_sources (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  name VARCHAR(120) NOT NULL,
  source_type VARCHAR(10) NOT NULL DEFAULT 'csv',
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  CONSTRAINT chk_sources_type CHECK (source_type IN ('api','manual','csv')),
  CONSTRAINT fk_sources_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  INDEX ix_sources_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prices (
  tenant_id BIGINT NOT NULL,
  asset_id BIGINT NOT NULL,
  price_date DATE NOT NULL,
  close_price DECIMAL(18,6) NOT NULL,
  source_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (tenant_id, asset_id, price_date),
  CONSTRAINT fk_prices_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_prices_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
  CONSTRAINT fk_prices_source FOREIGN KEY (source_id) REFERENCES price_sources(id),
  INDEX ix_prices_asset_date (asset_id, price_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  job_type VARCHAR(30) NOT NULL DEFAULT 'markowitz_run',
  status VARCHAR(10) NOT NULL DEFAULT 'queued',
  payload_json JSON NOT NULL,
  error_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  CONSTRAINT chk_jobs_status CHECK (status IN ('error','done','running','queued')),
  CONSTRAINT fk_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  INDEX ix_jobs_tenant_status (tenant_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS runs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  portfolio_id BIGINT NOT NULL,
  job_id BIGINT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'queued',
  engine_version VARCHAR(40) NOT NULL DEFAULT '1.0.0',
  params_json JSON NOT NULL,
  diagnostics_json JSON NULL,
  expected_ret DECIMAL(12,8) NULL,
  expected_risk DECIMAL(12,8) NULL,
  sharpe DECIMAL(12,8) NULL,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  CONSTRAINT chk_runs_status CHECK (status IN ('error','done','running','queued')),
  CONSTRAINT fk_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_runs_pf FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
  CONSTRAINT fk_runs_job FOREIGN KEY (job_id) REFERENCES jobs(id),
  INDEX ix_runs_tenant_pf (tenant_id, portfolio_id),
  INDEX ix_runs_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS run_points (
  run_id BIGINT NOT NULL,
  point_order INT NOT NULL,
  risk DECIMAL(12,8) NOT NULL,
  ret DECIMAL(12,8) NOT NULL,
  sharpe DECIMAL(12,8) NULL,
  PRIMARY KEY (run_id, point_order),
  CONSTRAINT fk_rp_run FOREIGN KEY (run_id) REFERENCES runs(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS run_weights (
  run_id BIGINT NOT NULL,
  asset_id BIGINT NOT NULL,
  weight DECIMAL(10,6) NOT NULL,
  PRIMARY KEY (run_id, asset_id),
  CONSTRAINT fk_rw_run FOREIGN KEY (run_id) REFERENCES runs(id),
  CONSTRAINT fk_rw_asset FOREIGN KEY (asset_id) REFERENCES assets(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS uploads (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  source_id BIGINT NULL,
  filename VARCHAR(255) NOT NULL,
  file_hash CHAR(64) NULL,
  kind VARCHAR(20) NOT NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'queued',
  total_rows INT NULL,
  ok_rows INT NULL,
  error_rows INT NULL,
  created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
  finished_at DATETIME NULL,
  CONSTRAINT chk_uploads_kind CHECK (kind IN ('portfolio_config','positions','prices','assets')),
  CONSTRAINT chk_uploads_status CHECK (status IN ('error','done','processing','queued')),
  CONSTRAINT fk_uploads_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  CONSTRAINT fk_uploads_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_uploads_source FOREIGN KEY (source_id) REFERENCES price_sources(id),
  INDEX ix_uploads_tenant_status (tenant_id, status)
) ENGINE=InnoDB;