-- Criar apenas a tabela users (a de companies já foi criada)
CREATE TABLE core_business.users (
    id bigint NOT NULL DEFAULT nextval('core_business.users_id_seq'::regclass),
    user_code character varying(20) DEFAULT ('USER'::text || lpad((nextval('core_business.users_id_seq'::regclass))::text, 6, '0'::text)) NOT NULL,
    company_id bigint NOT NULL,
    name character varying(255) NOT NULL,
    email character varying(255) NOT NULL,
    password_hash character varying(255) NOT NULL,
    type core_business.user_type DEFAULT 'USER'::core_business.user_type NOT NULL,
    status core_business.user_status DEFAULT 'PENDING'::core_business.user_status NOT NULL,
    avatar_url character varying(1000),
    phone character varying(20),
    birth_date date,
    preferences jsonb DEFAULT '{"language": "pt-BR", "timezone": "America/Sao_Paulo", "notifications": {"email": true, "push": true, "inApp": true}}'::jsonb NOT NULL,
    last_login timestamp without time zone,
    email_verified boolean DEFAULT false NOT NULL,
    phone_verified boolean DEFAULT false NOT NULL,
    two_factor_enabled boolean DEFAULT false NOT NULL,
    two_factor_secret character varying(255),
    deleted boolean DEFAULT false NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Adicionar constraints
ALTER TABLE ONLY core_business.users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY core_business.users ADD CONSTRAINT users_user_code_key UNIQUE (user_code);
ALTER TABLE ONLY core_business.users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Criar índices
CREATE INDEX idx_users_company_id ON core_business.users USING btree (company_id);
CREATE INDEX idx_users_created_at ON core_business.users USING btree (created_at);
CREATE INDEX idx_users_deleted ON core_business.users USING btree (deleted) WHERE (deleted = false);
CREATE INDEX idx_users_email ON core_business.users USING btree (email);
CREATE INDEX idx_users_email_verified ON core_business.users USING btree (email_verified);
CREATE INDEX idx_users_last_login ON core_business.users USING btree (last_login) WHERE (last_login IS NOT NULL);
CREATE INDEX idx_users_status ON core_business.users USING btree (status);
CREATE INDEX idx_users_type ON core_business.users USING btree (type);

-- Criar trigger
CREATE TRIGGER trigger_users_updated_at BEFORE UPDATE ON core_business.users FOR EACH ROW EXECUTE FUNCTION public.trigger_set_updated_at();

-- Adicionar foreign key
ALTER TABLE ONLY core_business.users ADD CONSTRAINT users_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE CASCADE;

-- ==========================================
-- Corrigir tabela social_accounts para usar referências corretas
ALTER TABLE ONLY core_business.social_accounts ADD CONSTRAINT social_accounts_user_id_fkey FOREIGN KEY (user_id) REFERENCES core_business.users(id) ON DELETE CASCADE;

-- ==========================================
-- Corrigir tabela media para usar referências corretas
ALTER TABLE ONLY core_business.media ADD CONSTRAINT media_user_id_fkey FOREIGN KEY (user_id) REFERENCES core_business.users(id) ON DELETE SET NULL;

-- ==========================================
-- Criar tabela posts
CREATE TABLE core_business.posts (
    id bigint NOT NULL DEFAULT nextval('core_business.posts_id_seq'::regclass),
    post_code character varying(20) DEFAULT ('POST'::text || lpad((nextval('core_business.posts_id_seq'::regclass))::text, 6, '0'::text)) NOT NULL,
    company_id bigint NOT NULL,
    author_id bigint NOT NULL,
    category_id bigint,
    content_text text,
    content_hashtags character varying(100)[],
    content_mentions character varying(100)[],
    content_links character varying(255)[],
    content_length integer DEFAULT 0 NOT NULL,
    post_type core_business.post_type DEFAULT 'TEXT'::core_business.post_type NOT NULL,
    status core_business.post_status DEFAULT 'DRAFT'::core_business.post_status NOT NULL,
    target_social_accounts bigint[] NOT NULL,
    media_ids bigint[],
    scheduled_for timestamp without time zone,
    published_at timestamp without time zone,
    external_ids jsonb DEFAULT '{}'::jsonb,
    view_count integer DEFAULT 0 NOT NULL,
    like_count integer DEFAULT 0 NOT NULL,
    comment_count integer DEFAULT 0 NOT NULL,
    share_count integer DEFAULT 0 NOT NULL,
    engagement_metrics jsonb DEFAULT '{"clicks": 0, "saves": 0, "profileVisits": 0}'::jsonb,
    performance_score numeric(5,2),
    geo_location jsonb,
    is_pinned boolean DEFAULT false NOT NULL,
    tags character varying(100)[],
    approval_status character varying(50) DEFAULT 'PENDING'::character varying,
    approved_by bigint,
    approved_at timestamp without time zone,
    rejected_reason text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Adicionar constraints
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_pkey PRIMARY KEY (id);
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_post_code_key UNIQUE (post_code);

-- Criar índices
CREATE INDEX idx_posts_company_id ON core_business.posts USING btree (company_id);
CREATE INDEX idx_posts_author_id ON core_business.posts USING btree (author_id);
CREATE INDEX idx_posts_category_id ON core_business.posts USING btree (category_id) WHERE (category_id IS NOT NULL);
CREATE INDEX idx_posts_status ON core_business.posts USING btree (status);
CREATE INDEX idx_posts_post_type ON core_business.posts USING btree (post_type);
CREATE INDEX idx_posts_scheduled_for ON core_business.posts USING btree (scheduled_for) WHERE (scheduled_for IS NOT NULL);
CREATE INDEX idx_posts_published_at ON core_business.posts USING btree (published_at) WHERE (published_at IS NOT NULL);
CREATE INDEX idx_posts_created_at ON core_business.posts USING btree (created_at);
CREATE INDEX idx_posts_target_social_accounts_gin ON core_business.posts USING gin (target_social_accounts);
CREATE INDEX idx_posts_media_ids_gin ON core_business.posts USING gin (media_ids);
CREATE INDEX idx_posts_hashtags_gin ON core_business.posts USING gin (content_hashtags);
CREATE INDEX idx_posts_tags_gin ON core_business.posts USING gin (tags);

-- Criar trigger
CREATE TRIGGER trigger_posts_updated_at BEFORE UPDATE ON core_business.posts FOR EACH ROW EXECUTE FUNCTION public.trigger_set_updated_at();

-- Adicionar foreign keys
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE CASCADE;
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES core_business.users(id) ON DELETE RESTRICT;
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_category_id_fkey FOREIGN KEY (category_id) REFERENCES core_business.content_categories(id) ON DELETE SET NULL;
ALTER TABLE ONLY core_business.posts ADD CONSTRAINT posts_approved_by_fkey FOREIGN KEY (approved_by) REFERENCES core_business.users(id) ON DELETE SET NULL;

-- ==========================================
-- Corrigir tabela post_media para usar referências corretas
ALTER TABLE ONLY core_business.post_media ADD CONSTRAINT post_media_post_id_fkey FOREIGN KEY (post_id) REFERENCES core_business.posts(id) ON DELETE CASCADE;
ALTER TABLE ONLY core_business.post_media ADD CONSTRAINT post_media_media_id_fkey FOREIGN KEY (media_id) REFERENCES core_business.media(id) ON DELETE CASCADE;
