-- tenant_schema.sql
CREATE TABLE IF NOT EXISTS afwer_users (
  usrtbl_id CHAR(32) NOT NULL PRIMARY KEY,
  usrtbl_username VARCHAR(100) UNIQUE,
  usrtbl_email VARCHAR(255) UNIQUE,
  usrtbl_password_hash VARCHAR(255),
  usrtbl_full_name VARCHAR(255),
  usrtbl_role ENUM('editor','author','manager') DEFAULT 'editor',
  usrtbl_is_active TINYINT(1) DEFAULT 1,
  usrtbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_publications (
  pubtbl_id CHAR(32) NOT NULL PRIMARY KEY,
  pubtbl_title VARCHAR(255),
  pubtbl_type VARCHAR(50),
  pubtbl_issn VARCHAR(20),
  pubtbl_description TEXT,
  pubtbl_created_by CHAR(32),
  pubtbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_volumes (
  voltbl_id CHAR(32) NOT NULL PRIMARY KEY,
  voltbl_publication_id CHAR(32),
  voltbl_volume_number VARCHAR(50),
  voltbl_year INT,
  voltbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_issues (
  isstbl_id CHAR(32) NOT NULL PRIMARY KEY,
  isstbl_volume_id CHAR(32),
  isstbl_issue_number VARCHAR(50),
  isstbl_published_date DATE,
  isstbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_articles (
  arttbl_id CHAR(32) NOT NULL PRIMARY KEY,
  arttbl_issue_id CHAR(32),
  arttbl_title VARCHAR(500),
  arttbl_abstract TEXT,
  arttbl_language CHAR(2) DEFAULT 'en',
  arttbl_pages VARCHAR(50),
  arttbl_doi_suffix VARCHAR(150),
  arttbl_created_by CHAR(32),
  arttbl_created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  arttbl_status VARCHAR(50) DEFAULT 'draft'
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_authors (
  authtbl_id CHAR(32) NOT NULL PRIMARY KEY,
  authtbl_given_name VARCHAR(255),
  authtbl_family_name VARCHAR(255),
  authtbl_affiliation VARCHAR(255),
  authtbl_email VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_article_authors (
  arttbla_article_id CHAR(32),
  arttbla_author_id CHAR(32),
  arttbla_author_order INT,
  PRIMARY KEY (arttbla_article_id, arttbla_author_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS afwer_article_metadata (
  artmeta_id CHAR(32) NOT NULL PRIMARY KEY,
  artmeta_article_id CHAR(32),
  artmeta_key VARCHAR(100),
  artmeta_value TEXT,
  artmeta_created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;