import argparse
import os
import sys
from sqlalchemy import text

# Ensure workspace root is on sys.path
WORKSPACE_ROOT = os.path.dirname(os.path.dirname(__file__))
if WORKSPACE_ROOT not in sys.path:
	sys.path.append(WORKSPACE_ROOT)

from app.core.database import engine

DDL_ALTER_ACCOUNTING_CODES = [
	# Add Phase 1 metadata columns
	"ALTER TABLE accounting_codes ADD COLUMN IF NOT EXISTS is_dimensional_template BOOLEAN NOT NULL DEFAULT FALSE",
	"ALTER TABLE accounting_codes ADD COLUMN IF NOT EXISTS hierarchy_level INTEGER NULL",
	"ALTER TABLE accounting_codes ADD COLUMN IF NOT EXISTS sort_order INTEGER NULL",
	# Prefer JSONB for Postgres; if not supported, this will error. Adjust manually if using non-Postgres DB.
	"ALTER TABLE accounting_codes ADD COLUMN IF NOT EXISTS dimension_pattern JSONB NULL",
	# Indexes for fast filtering (safe if already exists)
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_accounting_codes_is_dimensional_template') THEN CREATE INDEX idx_accounting_codes_is_dimensional_template ON accounting_codes (is_dimensional_template); END IF; END $$;",
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_accounting_codes_parent_id') THEN CREATE INDEX idx_accounting_codes_parent_id ON accounting_codes (parent_id); END IF; END $$;"
]

DDL_CREATE_CODE_INSTANCES = """
CREATE TABLE IF NOT EXISTS accounting_code_instances (
	id VARCHAR(36) PRIMARY KEY,
	base_code_id VARCHAR(36) NOT NULL REFERENCES accounting_codes(id) ON DELETE CASCADE,
	created_from_template_id VARCHAR(36) NULL REFERENCES accounting_codes(id) ON DELETE SET NULL,
	instance_code VARCHAR(50) UNIQUE NOT NULL,
	name VARCHAR(100) NOT NULL,
	account_type VARCHAR(20) NOT NULL,
	category VARCHAR(50) NOT NULL,
	reporting_tag VARCHAR(50) NULL,
	branch_id VARCHAR(36) NULL REFERENCES branches(id) ON DELETE SET NULL,
	dimension_values JSONB NULL,
	created_at TIMESTAMP NULL,
	updated_at TIMESTAMP NULL
);
"""

DDL_INDEX_CODE_INSTANCES = [
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_code_instances_base_code_id') THEN CREATE INDEX idx_code_instances_base_code_id ON accounting_code_instances (base_code_id); END IF; END $$;",
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_code_instances_template_id') THEN CREATE INDEX idx_code_instances_template_id ON accounting_code_instances (created_from_template_id); END IF; END $$;",
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_code_instances_branch_id') THEN CREATE INDEX idx_code_instances_branch_id ON accounting_code_instances (branch_id); END IF; END $$;",
	"DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_code_instances_instance_code') THEN CREATE INDEX idx_code_instances_instance_code ON accounting_code_instances (instance_code); END IF; END $$;"
]

QUERY_LIST_ACCOUNTING_CODES_COLS = text("""
SELECT column_name FROM information_schema.columns
WHERE table_name='accounting_codes' ORDER BY column_name
""")

QUERY_LIST_CODE_INSTANCES_COLS = text("""
SELECT column_name FROM information_schema.columns
WHERE table_name='accounting_code_instances' ORDER BY column_name
""")


def run_migration(list_only: bool = False):
	with engine.connect() as conn:
		if list_only:
			cols_codes = [r[0] for r in conn.execute(QUERY_LIST_ACCOUNTING_CODES_COLS)]
			cols_instances = [r[0] for r in conn.execute(QUERY_LIST_CODE_INSTANCES_COLS)]
			print({
				'accounting_codes': cols_codes,
				'accounting_code_instances': cols_instances
			})
			return

		# Apply accounting_codes alterations
		for stmt in DDL_ALTER_ACCOUNTING_CODES:
			conn.execute(text(stmt))

		# Create instances table
		conn.execute(text(DDL_CREATE_CODE_INSTANCES))

		# Indexes for instances
		for stmt in DDL_INDEX_CODE_INSTANCES:
			conn.execute(text(stmt))

		conn.commit()

		# Print results
		cols_codes = [r[0] for r in conn.execute(QUERY_LIST_ACCOUNTING_CODES_COLS)]
		cols_instances = [r[0] for r in conn.execute(QUERY_LIST_CODE_INSTANCES_COLS)]
		print('MIGRATION COMPLETE')
		print({
			'accounting_codes': cols_codes,
			'accounting_code_instances': cols_instances
		})


if __name__ == '__main__':
	parser = argparse.ArgumentParser(description='Phase 1: Dimensional Codes Migration')
	parser.add_argument('--list-only', action='store_true', help='List columns and exit')
	args = parser.parse_args()

	run_migration(list_only=args.list_only)
