sql_read_datatypes_field_lengths_sizes

import re
import csv
import math

# ========== SQL REGEXES ==========
table_regex = re.compile(
    r"CREATE\s+TABLE\s+`?(\w+)`?\s*\((.*?)\)\s*;",
    re.IGNORECASE | re.DOTALL
)

column_regex = re.compile(
    r"^\s*`(\w+)`\s+([A-Za-z]+)(?:\((.*?)\))?",
    re.IGNORECASE | re.MULTILINE
)

ignore_regex = re.compile(
    r"^\s*(PRIMARY KEY|UNIQUE KEY|KEY|INDEX|CONSTRAINT|FOREIGN KEY)",
    re.IGNORECASE
)

def parse_sql_tables(sql_text):
    """
    Extract CREATE TABLE blocks from SQL file.
    Returns a dict: {table_name: table_body}
    """
    table_regex = re.compile(
        r"CREATE\s+TABLE\s+`?(\w+)`?\s*\((.*?)\);",
        re.IGNORECASE | re.DOTALL
    )
    return {name: body.strip() for name, body in table_regex.findall(sql_text)}


# ========== MYSQL STORAGE FUNCTIONS ==========

def calc_mysql_storage(col_type, size_raw):
    col_type = col_type.upper()

    # Numeric Types
    numeric_sizes = {
        "TINYINT": 1,
        "SMALLINT": 2,
        "MEDIUMINT": 3,
        "INT": 4,
        "INTEGER": 4,
        "BIGINT": 8,
        "FLOAT": 4,
        "DOUBLE": 8,
        "REAL": 8
    }

    if col_type in numeric_sizes:
        return numeric_sizes[col_type]

    # DECIMAL(M,D)
    if col_type == "DECIMAL" and size_raw:
        try:
            m = int(size_raw.split(",")[0])
            return math.ceil(m / 9) * 4
        except:
            return 0

    # CHAR(n)
    if col_type == "CHAR" and size_raw:
        return int(size_raw) * 4  # utf8mb4 worst-case

    # VARCHAR(n)
    if col_type == "VARCHAR" and size_raw:
        n = int(size_raw)
        return n * 4 + 2  # data + length bytes

    # TEXT TYPES
    text_sizes = {
        "TINYTEXT": 255 + 1,
        "TEXT": 65535 + 2,
        "MEDIUMTEXT": 16777215 + 3,
        "LONGTEXT": 4294967295 + 4
    }
    if col_type in text_sizes:
        return text_sizes[col_type]

    # DATE/TIME TYPES
    datetime_sizes = {
        "DATE": 3,
        "TIME": 3,
        "YEAR": 1,
        "DATETIME": 5,
        "TIMESTAMP": 4
    }
    if col_type in datetime_sizes:
        return datetime_sizes[col_type]

    # ENUM('A','B','C')
    if col_type == "ENUM":
        try:
            options = size_raw.split(",")
            count = len(options)
            return 1 if count < 256 else 2
        except:
            return 1

    # SET('A','B')
    if col_type == "SET":
        try:
            options = size_raw.split(",")
            count = len(options)
            if count <= 8: return 1
            if count <= 16: return 2
            if count <= 24: return 3
            return 8
        except:
            return 1

    # Anything else → treat as unknown size
    return 0


# ========== COLUMN EXTRACTION ==========

def extract_columns(body):
    columns = []

    for line in body.split("\n"):
        if ignore_regex.search(line):
            continue

        m = column_regex.search(line)
        if not m:
            continue

        col_name = m.group(1)
        col_type = m.group(2).upper()
        size_raw = m.group(3)

        storage_bytes = calc_mysql_storage(col_type, size_raw)

        columns.append((col_name, col_type, size_raw, storage_bytes))

    return columns


# ========== MAIN PROCESSOR ==========

def process_sql_file(path, csv_output="mysql_storage_report.csv"):
    with open(path, "r", encoding="utf-8") as f:
        sql_text = f.read()

    tables = parse_sql_tables(sql_text)

    all_fields = []

    for table_name, body in tables.items():
        columns = extract_columns(body)
        for name, dtype, size_raw, bytes_used in columns:
            all_fields.append([
                table_name,
                name,
                dtype,
                size_raw,
                bytes_used
            ])

    # Sort globally by bytes descending
    all_fields_sorted = sorted(all_fields, key=lambda x: x[4], reverse=True)

    # Write CSV
    with open(csv_output, "w", newline="", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerow(
            ["Table Name", "Column Name", "Data Type", "Length/Params", "MySQL Storage Bytes"]
        )
        writer.writerows(all_fields_sorted)

    print(f"\nCSV CREATED: {csv_output}")


# RUN ON erp.sql
process_sql_file("erp.sql")



# ✅ MYSQL REAL STORAGE SIZE RULES USED
# Numeric Types
# Type  Storage
# TINYINT   1 byte
# SMALLINT  2 bytes
# MEDIUMINT 3 bytes
# INT / INTEGER 4 bytes
# BIGINT    8 bytes
# FLOAT 4 bytes
# DOUBLE    8 bytes
# DECIMAL(M,D)  Varies = ceil(M/9)*4 bytes
# Character Types
# Type  Formula
# CHAR(n)   n bytes
# VARCHAR(n)    n × utf8mb4 (max 4 bytes) + 1–2 bytes length indicator
# ENUM('...')   1 or 2 bytes
# SET   1, 2, 3, or 8 bytes (depends on count)

# For simplicity, we assume utf8mb4 for VARCHAR → 4 bytes per character.

# Blob/Text Types
# Type  Storage
# TINYTEXT  255 bytes + 1 byte
# TEXT  65,535 bytes + 2 bytes
# MEDIUMTEXT    16MB + 3 bytes
# LONGTEXT  4GB + 4 bytes
# Date/Time
# Type  Storage
# DATE  3 bytes
# TIME  3 bytes
# YEAR  1 byte
# DATETIME  5 bytes
# TIMESTAMP 4 bytes