217 lines
10 KiB
PL/PgSQL
217 lines
10 KiB
PL/PgSQL
BEGIN;
|
||
|
||
CREATE TABLE alembic_version (
|
||
version_num VARCHAR(32) NOT NULL,
|
||
CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
|
||
);
|
||
|
||
-- Running upgrade -> 35742cfbb850
|
||
|
||
DROP INDEX idx_audit_logs_action;
|
||
|
||
DROP INDEX idx_audit_logs_correlation_id;
|
||
|
||
DROP INDEX idx_audit_logs_created_at;
|
||
|
||
DROP INDEX idx_audit_logs_resource;
|
||
|
||
DROP INDEX idx_audit_logs_tenant_id;
|
||
|
||
DROP INDEX idx_audit_logs_user_id;
|
||
|
||
DROP TABLE audit_logs;
|
||
|
||
DROP INDEX idx_tickets_assigned_to;
|
||
|
||
DROP INDEX idx_tickets_category;
|
||
|
||
DROP INDEX idx_tickets_created_at;
|
||
|
||
DROP INDEX idx_tickets_created_by;
|
||
|
||
DROP INDEX idx_tickets_number;
|
||
|
||
DROP INDEX idx_tickets_priority;
|
||
|
||
DROP INDEX idx_tickets_sla_resolution;
|
||
|
||
DROP INDEX idx_tickets_sla_response;
|
||
|
||
DROP INDEX idx_tickets_status;
|
||
|
||
DROP INDEX idx_tickets_tenant_id;
|
||
|
||
DROP TABLE tickets;
|
||
|
||
DROP INDEX idx_refresh_tokens_expires;
|
||
|
||
DROP INDEX idx_refresh_tokens_hash;
|
||
|
||
DROP INDEX idx_refresh_tokens_user_id;
|
||
|
||
DROP TABLE refresh_tokens;
|
||
|
||
DROP INDEX idx_notification_logs_created_at;
|
||
|
||
DROP INDEX idx_notification_logs_recipient;
|
||
|
||
DROP INDEX idx_notification_logs_status;
|
||
|
||
DROP INDEX idx_notification_logs_tenant_id;
|
||
|
||
DROP INDEX idx_notification_logs_ticket_id;
|
||
|
||
DROP TABLE notification_logs;
|
||
|
||
DROP TABLE affected_systems;
|
||
|
||
DROP INDEX idx_ticket_comments_author_id;
|
||
|
||
DROP INDEX idx_ticket_comments_created_at;
|
||
|
||
DROP INDEX idx_ticket_comments_ticket_id;
|
||
|
||
DROP TABLE ticket_comments;
|
||
|
||
DROP INDEX idx_clients_name;
|
||
|
||
DROP INDEX idx_clients_properties;
|
||
|
||
DROP INDEX idx_clients_tax_id;
|
||
|
||
DROP INDEX idx_clients_tenant_id;
|
||
|
||
DROP TABLE clients;
|
||
|
||
DROP TABLE categories;
|
||
|
||
DROP INDEX idx_users_active;
|
||
|
||
DROP INDEX idx_users_email;
|
||
|
||
DROP INDEX idx_users_role;
|
||
|
||
DROP INDEX idx_users_tenant_email;
|
||
|
||
DROP INDEX idx_users_tenant_id;
|
||
|
||
DROP TABLE users;
|
||
|
||
DROP TABLE systems;
|
||
|
||
DROP TABLE ticket_categories;
|
||
|
||
DROP INDEX idx_ticket_status_history_changed_by;
|
||
|
||
DROP INDEX idx_ticket_status_history_created_at;
|
||
|
||
DROP INDEX idx_ticket_status_history_ticket_id;
|
||
|
||
DROP TABLE ticket_status_history;
|
||
|
||
DROP INDEX idx_ticket_attachments_comment_id;
|
||
|
||
DROP INDEX idx_ticket_attachments_ticket_id;
|
||
|
||
DROP INDEX idx_ticket_attachments_uploaded_by;
|
||
|
||
DROP TABLE ticket_attachments;
|
||
|
||
DROP INDEX idx_email_templates_tenant_id;
|
||
|
||
DROP INDEX idx_email_templates_type;
|
||
|
||
DROP TABLE email_templates;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN timezone SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN locale SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN max_users SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN max_storage_mb SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN allowed_file_types TYPE VARCHAR[];
|
||
|
||
ALTER TABLE tenants ALTER COLUMN allowed_file_types SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN status SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN created_at SET NOT NULL;
|
||
|
||
ALTER TABLE tenants ALTER COLUMN updated_at SET NOT NULL;
|
||
|
||
DROP INDEX idx_tenants_domain;
|
||
|
||
DROP INDEX idx_tenants_slug;
|
||
|
||
DROP INDEX idx_tenants_status;
|
||
|
||
COMMENT ON TABLE tenants IS NULL;
|
||
|
||
INSERT INTO alembic_version (version_num) VALUES ('35742cfbb850') RETURNING alembic_version.version_num;
|
||
|
||
-- Running upgrade 35742cfbb850 -> 48c43e9204c3
|
||
|
||
UPDATE alembic_version SET version_num='48c43e9204c3' WHERE alembic_version.version_num = '35742cfbb850';
|
||
|
||
-- Running upgrade 48c43e9204c3 -> 13362e8c493a
|
||
|
||
CREATE TABLE client_profiles (
|
||
id UUID NOT NULL,
|
||
tenant_id UUID NOT NULL,
|
||
business_name VARCHAR(255),
|
||
commercial_name VARCHAR(255),
|
||
client_code VARCHAR(50),
|
||
client_type VARCHAR(50),
|
||
rfc VARCHAR(13),
|
||
tax_id VARCHAR(50),
|
||
country VARCHAR(100),
|
||
state VARCHAR(100),
|
||
city VARCHAR(100),
|
||
address TEXT,
|
||
external_number VARCHAR(20),
|
||
internal_number VARCHAR(20),
|
||
postal_code VARCHAR(10),
|
||
neighborhood VARCHAR(100),
|
||
main_phone VARCHAR(20),
|
||
secondary_phone VARCHAR(20),
|
||
direct_phone VARCHAR(20),
|
||
phone_extension VARCHAR(10),
|
||
fax VARCHAR(20),
|
||
business_hours VARCHAR(255),
|
||
website VARCHAR(255),
|
||
main_email VARCHAR(320),
|
||
billing_email VARCHAR(320),
|
||
advertising_medium VARCHAR(255),
|
||
nationality VARCHAR(100),
|
||
logo_url VARCHAR(500),
|
||
company_representative VARCHAR(255),
|
||
legal_representative VARCHAR(255),
|
||
credit_limit NUMERIC(15, 2),
|
||
payment_terms VARCHAR(100),
|
||
preferred_currency VARCHAR(3) NOT NULL,
|
||
send_to_billing BOOLEAN NOT NULL,
|
||
is_active_client BOOLEAN NOT NULL,
|
||
is_prospect BOOLEAN NOT NULL,
|
||
notes TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(tenant_id) REFERENCES tenants (id) ON DELETE CASCADE,
|
||
UNIQUE (tenant_id)
|
||
);
|
||
|
||
CREATE INDEX idx_client_profiles_tenant_id ON client_profiles (tenant_id);
|
||
|
||
CREATE INDEX idx_client_profiles_rfc ON client_profiles (rfc);
|
||
|
||
CREATE INDEX idx_client_profiles_business_name ON client_profiles (business_name);
|
||
|
||
CREATE INDEX idx_client_profiles_client_code ON client_profiles (client_code);
|
||
|
||
UPDATE alembic_version SET version_num='13362e8c493a' WHERE alembic_version.version_num = '48c43e9204c3';
|
||
|
||
-- Running upgrade 13362e8c493a -> a1b2c3d4e5f6
|
||
|