package net.siegeln.cameleer.saas.provisioning;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

@Service
public class TenantDatabaseService {

    private static final Logger log = LoggerFactory.getLogger(TenantDatabaseService.class);

    private final ProvisioningProperties props;

    public TenantDatabaseService(ProvisioningProperties props) {
        this.props = props;
    }

    public void createTenantDatabase(String slug, String password) {
        validateSlug(slug);

        String url = props.datasourceUrl();
        if (url == null || url.isBlank()) {
            log.warn("No datasource URL configured — skipping tenant DB setup");
            return;
        }

        String user = "tenant_" + slug;
        String schema = "tenant_" + slug;

        try (Connection conn = DriverManager.getConnection(url, props.datasourceUsername(), props.datasourcePassword());
             Statement stmt = conn.createStatement()) {

            boolean userExists;
            try (ResultSet rs = stmt.executeQuery(
                    "SELECT 1 FROM pg_roles WHERE rolname = '" + user + "'")) {
                userExists = rs.next();
            }
            if (!userExists) {
                stmt.execute("CREATE USER \"" + user + "\" WITH PASSWORD '" + escapePassword(password) + "'");
                log.info("Created PostgreSQL user: {}", user);
            } else {
                stmt.execute("ALTER USER \"" + user + "\" WITH PASSWORD '" + escapePassword(password) + "'");
                log.info("Updated password for existing PostgreSQL user: {}", user);
            }

            boolean schemaExists;
            try (ResultSet rs = stmt.executeQuery(
                    "SELECT 1 FROM information_schema.schemata WHERE schema_name = '" + schema + "'")) {
                schemaExists = rs.next();
            }
            if (!schemaExists) {
                stmt.execute("CREATE SCHEMA \"" + schema + "\" AUTHORIZATION \"" + user + "\"");
                log.info("Created PostgreSQL schema: {}", schema);
            } else {
                stmt.execute("ALTER SCHEMA \"" + schema + "\" OWNER TO \"" + user + "\"");
                log.info("Schema {} already exists — ensured ownership", schema);
            }

            stmt.execute("REVOKE ALL ON SCHEMA public FROM \"" + user + "\"");

        } catch (Exception e) {
            throw new RuntimeException("Failed to create tenant database for '" + slug + "': " + e.getMessage(), e);
        }
    }

    public void dropTenantDatabase(String slug) {
        validateSlug(slug);

        String url = props.datasourceUrl();
        if (url == null || url.isBlank()) {
            log.warn("No datasource URL configured — skipping tenant DB cleanup");
            return;
        }

        String user = "tenant_" + slug;
        String schema = "tenant_" + slug;

        try (Connection conn = DriverManager.getConnection(url, props.datasourceUsername(), props.datasourcePassword());
             Statement stmt = conn.createStatement()) {
            stmt.execute("DROP SCHEMA IF EXISTS \"" + schema + "\" CASCADE");
            log.info("Dropped PostgreSQL schema: {}", schema);

            stmt.execute("DROP USER IF EXISTS \"" + user + "\"");
            log.info("Dropped PostgreSQL user: {}", user);
        } catch (Exception e) {
            log.warn("Failed to drop tenant database for '{}': {}", slug, e.getMessage());
        }
    }

    private void validateSlug(String slug) {
        if (slug == null || !slug.matches("^[a-z0-9-]+$")) {
            throw new IllegalArgumentException("Invalid tenant slug: " + slug);
        }
    }

    private String escapePassword(String password) {
        return password.replace("'", "''");
    }
}
