"""
Vytvoří tabulku covance_equeries v databázi studie.
Spusť jednorázově pro inicializaci.
"""

import mysql.connector
import db_config

DDL = """
CREATE TABLE IF NOT EXISTS covance_equeries (
    id                    INT AUTO_INCREMENT PRIMARY KEY,
    import_id             INT NOT NULL,
    study                 VARCHAR(20)  NOT NULL,
    site_code             VARCHAR(10),
    investigator_name     VARCHAR(100),
    country               VARCHAR(30),
    visit                 VARCHAR(100),
    visit_collection_date DATETIME,
    accession             VARCHAR(20),
    subject               VARCHAR(20),
    equery_id             INT,
    create_date           DATETIME,
    response_datetime     DATETIME,
    issue_type            VARCHAR(100),
    status                VARCHAR(20),
    time_before_response  VARCHAR(20),
    user_name             VARCHAR(100),
    email                 VARCHAR(100),
    study_role            VARCHAR(50),
    FOREIGN KEY (import_id) REFERENCES iwrs_import(import_id),
    INDEX idx_import   (import_id),
    INDEX idx_site     (site_code),
    INDEX idx_subject  (subject),
    INDEX idx_equery   (equery_id),
    INDEX idx_status   (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""

conn = mysql.connector.connect(
    host=db_config.DB_HOST, port=db_config.DB_PORT,
    user=db_config.DB_USER, password=db_config.DB_PASSWORD,
    database=db_config.DB_NAME,
)
cursor = conn.cursor()
cursor.execute(DDL)
conn.commit()
cursor.close()
conn.close()
print("Tabulka covance_equeries OK.")