-- Criar tabela companies
CREATE TABLE core_business.companies (
    id bigint NOT NULL,
    company_code character varying(20) DEFAULT ('COMP'::text || lpad((nextval('core_business.companies_id_seq'::regclass))::text, 6, '0'::text)) NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    email character varying(255) NOT NULL,
    phone character varying(20),
    website character varying(255),
    address jsonb DEFAULT '{}'::jsonb,
    logo_url character varying(1000),
    active_plan core_business.subscription_plan DEFAULT 'FREE'::core_business.subscription_plan NOT NULL,
    max_users integer DEFAULT 1 NOT NULL,
    max_social_accounts integer DEFAULT 1 NOT NULL,
    max_posts_per_month integer DEFAULT 10 NOT NULL,
    max_storage_gb numeric(10,2) DEFAULT 1.0 NOT NULL,
    features jsonb DEFAULT '{"analytics": false, "automation": false, "collaboration": false, "advancedReports": false}'::jsonb NOT NULL,
    status core_business.company_status DEFAULT 'TRIAL'::core_business.company_status NOT NULL,
    registration_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    expiration_date timestamp without time zone,
    billing_info jsonb DEFAULT '{}'::jsonb,
    settings jsonb DEFAULT '{"timezone": "America/Sao_Paulo", "language": "pt-BR", "notifications": {"email": true, "push": true}}'::jsonb NOT NULL,
    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
);

-- Criar sequência para companies
CREATE SEQUENCE core_business.companies_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.companies_id_seq OWNED BY core_business.companies.id;
ALTER TABLE ONLY core_business.companies ALTER COLUMN id SET DEFAULT nextval('core_business.companies_id_seq'::regclass);

-- Adicionar constraints
ALTER TABLE ONLY core_business.companies ADD CONSTRAINT companies_pkey PRIMARY KEY (id);
ALTER TABLE ONLY core_business.companies ADD CONSTRAINT companies_company_code_key UNIQUE (company_code);
ALTER TABLE ONLY core_business.companies ADD CONSTRAINT companies_email_key UNIQUE (email);

-- Criar índices
CREATE INDEX idx_companies_active_plan ON core_business.companies USING btree (active_plan);
CREATE INDEX idx_companies_created_at ON core_business.companies USING btree (created_at);
CREATE INDEX idx_companies_deleted ON core_business.companies USING btree (deleted) WHERE (deleted = false);
CREATE INDEX idx_companies_expiration_date ON core_business.companies USING btree (expiration_date) WHERE (expiration_date IS NOT NULL);
CREATE INDEX idx_companies_name ON core_business.companies USING btree (name);
CREATE INDEX idx_companies_registration_date ON core_business.companies USING btree (registration_date);
CREATE INDEX idx_companies_status ON core_business.companies USING btree (status);

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

-- ==========================================
-- Criar tabela users
CREATE TABLE core_business.users (
    id bigint NOT NULL,
    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
);

-- Criar sequência para users
CREATE SEQUENCE core_business.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.users_id_seq OWNED BY core_business.users.id;
ALTER TABLE ONLY core_business.users ALTER COLUMN id SET DEFAULT nextval('core_business.users_id_seq'::regclass);

-- 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;

-- ==========================================
-- Criar tabela social_accounts
CREATE TABLE core_business.social_accounts (
    id bigint NOT NULL,
    account_code character varying(20) DEFAULT ('ACC'::text || lpad((nextval('core_business.social_accounts_id_seq'::regclass))::text, 6, '0'::text)) NOT NULL,
    company_id bigint NOT NULL,
    social_network_id bigint NOT NULL,
    user_id bigint NOT NULL,
    username character varying(100) NOT NULL,
    display_name character varying(255),
    bio text,
    profile_picture_url character varying(1000),
    followers_count integer DEFAULT 0 NOT NULL,
    following_count integer DEFAULT 0 NOT NULL,
    posts_count integer DEFAULT 0 NOT NULL,
    verified boolean DEFAULT false NOT NULL,
    business_account boolean DEFAULT false NOT NULL,
    account_type character varying(50) DEFAULT 'PERSONAL'::character varying NOT NULL,
    external_id character varying(255) NOT NULL,
    access_token text,
    refresh_token text,
    token_expires_at timestamp without time zone,
    connection_status core_business.connection_status DEFAULT 'CONNECTED'::core_business.connection_status NOT NULL,
    last_sync_date timestamp without time zone,
    sync_frequency_hours integer DEFAULT 24 NOT NULL,
    auto_sync_enabled boolean DEFAULT true NOT NULL,
    metrics jsonb DEFAULT '{"followers": 0, "following": 0, "posts": 0, "engagement": 0.0, "avgLikes": 0.0, "avgComments": 0.0, "reachRate": 0.0}'::jsonb NOT NULL,
    additional_info jsonb DEFAULT '{}'::jsonb,
    active boolean DEFAULT true NOT NULL,
    registration_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    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
);

-- Criar sequência para social_accounts
CREATE SEQUENCE core_business.social_accounts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.social_accounts_id_seq OWNED BY core_business.social_accounts.id;
ALTER TABLE ONLY core_business.social_accounts ALTER COLUMN id SET DEFAULT nextval('core_business.social_accounts_id_seq'::regclass);

-- Adicionar constraints
ALTER TABLE ONLY core_business.social_accounts ADD CONSTRAINT social_accounts_pkey PRIMARY KEY (id);
ALTER TABLE ONLY core_business.social_accounts ADD CONSTRAINT social_accounts_account_code_key UNIQUE (account_code);

-- Criar índices
CREATE INDEX idx_social_accounts_company_id ON core_business.social_accounts USING btree (company_id);
CREATE INDEX idx_social_accounts_social_network_id ON core_business.social_accounts USING btree (social_network_id);
CREATE INDEX idx_social_accounts_user_id ON core_business.social_accounts USING btree (user_id);
CREATE INDEX idx_social_accounts_username ON core_business.social_accounts USING btree (username);
CREATE INDEX idx_social_accounts_connection_status ON core_business.social_accounts USING btree (connection_status);
CREATE INDEX idx_social_accounts_active ON core_business.social_accounts USING btree (active);
CREATE INDEX idx_social_accounts_deleted ON core_business.social_accounts USING btree (deleted) WHERE (deleted = false);

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

-- Adicionar foreign keys
ALTER TABLE ONLY core_business.social_accounts ADD CONSTRAINT social_accounts_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE CASCADE;
ALTER TABLE ONLY core_business.social_accounts ADD CONSTRAINT social_accounts_social_network_id_fkey FOREIGN KEY (social_network_id) REFERENCES core_business.social_networks(id) ON DELETE RESTRICT;
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;

-- ==========================================
-- Criar tabela media
CREATE TABLE core_business.media (
    id bigint NOT NULL,
    media_code character varying(20) DEFAULT ('MED'::text || lpad((nextval('core_business.media_id_seq'::regclass))::text, 6, '0'::text)) NOT NULL,
    company_id bigint NOT NULL,
    user_id bigint,
    original_filename character varying(255) NOT NULL,
    filename character varying(255) NOT NULL,
    file_path character varying(1000) NOT NULL,
    media_type core_business.media_type NOT NULL,
    file_size_bytes bigint NOT NULL,
    mime_type character varying(100) NOT NULL,
    dimensions jsonb DEFAULT '{"width": 0, "height": 0}'::jsonb,
    duration_seconds integer,
    thumbnail_path character varying(1000),
    alt_text character varying(255),
    description text,
    tags character varying(255)[],
    metadata jsonb DEFAULT '{}'::jsonb,
    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
);

-- Criar sequência para media
CREATE SEQUENCE core_business.media_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.media_id_seq OWNED BY core_business.media.id;
ALTER TABLE ONLY core_business.media ALTER COLUMN id SET DEFAULT nextval('core_business.media_id_seq'::regclass);

-- Adicionar constraints
ALTER TABLE ONLY core_business.media ADD CONSTRAINT media_pkey PRIMARY KEY (id);
ALTER TABLE ONLY core_business.media ADD CONSTRAINT media_media_code_key UNIQUE (media_code);

-- Criar índices
CREATE INDEX idx_media_company_id ON core_business.media USING btree (company_id);
CREATE INDEX idx_media_user_id ON core_business.media USING btree (user_id) WHERE (user_id IS NOT NULL);
CREATE INDEX idx_media_media_type ON core_business.media USING btree (media_type);
CREATE INDEX idx_media_created_at ON core_business.media USING btree (created_at);
CREATE INDEX idx_media_deleted ON core_business.media USING btree (deleted) WHERE (deleted = false);
CREATE INDEX idx_media_tags_gin ON core_business.media USING gin (tags);

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

-- Adicionar foreign keys
ALTER TABLE ONLY core_business.media ADD CONSTRAINT media_company_id_fkey FOREIGN KEY (company_id) REFERENCES core_business.companies(id) ON DELETE CASCADE;
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,
    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
);

-- Criar sequência para posts
CREATE SEQUENCE core_business.posts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.posts_id_seq OWNED BY core_business.posts.id;
ALTER TABLE ONLY core_business.posts ALTER COLUMN id SET DEFAULT nextval('core_business.posts_id_seq'::regclass);

-- 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;

-- ==========================================
-- Criar tabela post_media
CREATE TABLE core_business.post_media (
    id bigint NOT NULL,
    post_id bigint NOT NULL,
    media_id bigint NOT NULL,
    display_order integer DEFAULT 1 NOT NULL,
    caption text,
    alt_text character varying(255),
    crop_data jsonb,
    filter_data jsonb,
    actual_format core_business.media_format,
    optimized_for_platform character varying(50),
    processing_status character varying(50) DEFAULT 'PENDING'::character varying NOT NULL,
    processed_at timestamp without time zone,
    error_message text,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Criar sequência para post_media
CREATE SEQUENCE core_business.post_media_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- Associar sequência à coluna id
ALTER SEQUENCE core_business.post_media_id_seq OWNED BY core_business.post_media.id;
ALTER TABLE ONLY core_business.post_media ALTER COLUMN id SET DEFAULT nextval('core_business.post_media_id_seq'::regclass);

-- Adicionar constraints
ALTER TABLE ONLY core_business.post_media ADD CONSTRAINT post_media_pkey PRIMARY KEY (id);

-- Criar índices
CREATE INDEX idx_post_media_post_id ON core_business.post_media USING btree (post_id);
CREATE INDEX idx_post_media_media_id ON core_business.post_media USING btree (media_id);
CREATE INDEX idx_post_media_display_order ON core_business.post_media USING btree (display_order);
CREATE INDEX idx_post_media_processing_status ON core_business.post_media USING btree (processing_status);

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

-- Adicionar foreign keys
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;
